Data Item Expression

[Nickbruno]

If someone could help me get a better understanding of what is going on below I'd appreciate it. As much explanation as you care to give is appreciated as I am a noob. But if nothing else what does the 2 numbers after the substring ie 11,8 mean.

if (substring([Standard Bills].[Standard Bills].[Transaction Project ID],11,8) in ('AA.05.05', 'AA.05.09', 'AA.05.23', 'AA.06', 'AB.05.06', 'AB.05.13', 'AB.05.15','AC.05.05', 'AC.05.09', 'AC.05.23', 'AC.06', 'AD.05.06', 'AD.05.13', 'AD.05.15'))

then (substring([Standard Bills].[Standard Bills].[Transaction Project ID],1,18))

else if (substring([Standard Bills].[Standard Bills].[Transaction Project ID],11,11) in ('AA.05.07.02', 'AC.05.07.02','AA.05.11.03', 'AA.05.07.03', 'AC.05.07.03','AB.05.12.01','AD.05.12.01'))

then (substring([Standard Bills].[Standard Bills].[Transaction Project ID],1,24))

else if (substring([Standard Bills].[Standard Bills].[Transaction Project ID],11,8) in ('AB.05.11', 'AJ.05.11'))

then (substring([Standard Bills].[Standard Bills].[Transaction Project ID],1,27))

else (substring([Standard Bills].[Standard Bills].[Transaction Project ID],1,21))

[MFGF]

If someone could help me get a better understanding of what is going on below I'd appreciate it. As much explanation as you care to give is appreciated as I am a noob. But if nothing else what does the 2 numbers after the substring ie 11,8 mean.

if (substring([Standard Bills].[Standard Bills].[Transaction Project ID],11,8) in ('AA.05.05', 'AA.05.09', 'AA.05.23', 'AA.06', 'AB.05.06', 'AB.05.13', 'AB.05.15','AC.05.05', 'AC.05.09', 'AC.05.23', 'AC.06', 'AD.05.06', 'AD.05.13', 'AD.05.15'))

then (substring([Standard Bills].[Standard Bills].[Transaction Project ID],1,18))

else if (substring([Standard Bills].[Standard Bills].[Transaction Project ID],11,11) in ('AA.05.07.02', 'AC.05.07.02','AA.05.11.03', 'AA.05.07.03', 'AC.05.07.03','AB.05.12.01','AD.05.12.01'))

then (substring([Standard Bills].[Standard Bills].[Transaction Project ID],1,24))

else if (substring([Standard Bills].[Standard Bills].[Transaction Project ID],11,8) in ('AB.05.11', 'AJ.05.11'))

then (substring([Standard Bills].[Standard Bills].[Transaction Project ID],1,27))

else (substring([Standard Bills].[Standard Bills].[Transaction Project ID],1,21))

Hi,

The substring() function is used to extract parts of character strings. The two numbers refer to the starting position to begin extracting from, and the number of characters to extract.

For example, let's say you have a data item called Statement containing the value "MFGF is a muppet"

if you created an expression substring([Statement],1,4) you would get "MFGF" and substring([Statement],11,6) would return "muppet"

So, your expression above extracts an 8 character subset of the Transaction Project ID item, starting with the character at position 11, and compares the extracted result with the values you see listed in the parentheses on line 1 of the expression. If it finds a match, it returns the first 18 characters of the Transaction Project ID item as the result. If not, it then goes on to extract an 11 character subset of Transaction Project ID, starting with the character at position 11, and compares the extracted result with the values you see listed in parentheses on line 3 of the expression. If it finds a match, it returns the first 24 characters of the Transaction Project ID item as the result. If not, it then goes on to extract an 8 character subset of Transaction Project ID, starting with the character at position 11, and compares the extracted result with the values you see listed in parentheses on line 5 of the expression. If it finds a match, it returns the first 27 characters of the Transaction Project ID item as the result. If not, it returns the first 21 characters of the Transaction Project ID item as the result.

Cheers!

MF.

[Nickbruno]

Great Thank you! That was a wonderful explanation!