alaasallam
IS-IT--Management
I have a field called "OrgPath" that has Path for different orgs with "\" as separator as follows:
\Org1\Org2\Org3\Org4
Some records may have 4 items (orgs) and some may have more up to 9 orgs with the "\" as separator.
\Org1\Org2\Org3\Org4\Org5\Org6\Org7\Org8\Org9
I want to split each org in a field. I created 9 formula fields (Org1 to Org9) to have each populated with the appropriate org and used the following formula in each field:
For “Org1” formula field:
Split({OrgPath},"\")[1]....This should be populated with "Org1"
For “Org2” formula field:
Split({OrgPath},"\")[2]....This should be populated with "Org2"
And so on.
the formula works fine as long as the reference to the item does not exceed the number of items in the array. However, if the subscript that references an item that is higher than the number of items in the array, I get an error msg; i.e. if I enter a subscript that references the 6th or 8th item, for example, in a five item array, I get the following error:
"You have entered a subscript that specifies an array item that does not exist"
So, if a record is showing \Org1\Org2, then formula fields “Split({OrgPath},"\")[1]” and “Split({OrgPath},"\")[2]” will be fine but “Split({OrgPath},"\")[3]” will error out.
Is there a way to fix this so that, all formula fields that are referencing items that are higher than the upper bound of the array in one record, would just show blank; if, for example, I have 2 items in the array, then the rest of the formula fields from 3 to 9 will be blank.
thanks
Al
\Org1\Org2\Org3\Org4
Some records may have 4 items (orgs) and some may have more up to 9 orgs with the "\" as separator.
\Org1\Org2\Org3\Org4\Org5\Org6\Org7\Org8\Org9
I want to split each org in a field. I created 9 formula fields (Org1 to Org9) to have each populated with the appropriate org and used the following formula in each field:
For “Org1” formula field:
Split({OrgPath},"\")[1]....This should be populated with "Org1"
For “Org2” formula field:
Split({OrgPath},"\")[2]....This should be populated with "Org2"
And so on.
the formula works fine as long as the reference to the item does not exceed the number of items in the array. However, if the subscript that references an item that is higher than the number of items in the array, I get an error msg; i.e. if I enter a subscript that references the 6th or 8th item, for example, in a five item array, I get the following error:
"You have entered a subscript that specifies an array item that does not exist"
So, if a record is showing \Org1\Org2, then formula fields “Split({OrgPath},"\")[1]” and “Split({OrgPath},"\")[2]” will be fine but “Split({OrgPath},"\")[3]” will error out.
Is there a way to fix this so that, all formula fields that are referencing items that are higher than the upper bound of the array in one record, would just show blank; if, for example, I have 2 items in the array, then the rest of the formula fields from 3 to 9 will be blank.
thanks
Al