The ItemReject function
- May. 12, 2016
In Turbo Integrator processes, you often need a way to skip certain records/text files lines/cells/subset elements/… from processing. We know that we can use the ItemSkip function to that end. Whatever the data source you can run over some items in the data source and neglect them.
Next to that function, we also have the ItemReject function. It is very similar to ItemSkip, but allows to specify a custom message. The message will be stored in the message log and will be presented to you when your nice TI process suddenly breaks down. Even in the case of a successful process run, the message log will show when you used ItemReject and certain records satisfied the criteria for ItemReject.
Below I show you an example of how you could output a custom message:
# These variables could be stored in the Prolog tab # or they are the contents of Variables in the Metadata / Data tab vDim = 'Accounts_PL'; vElement = 'Net Earnings'; # Skipping invalid elements IF(DTYPE(vDim, vAccount) @<> 'N'; ITEMREJECT('The element ''' | vElement | ''' is not a valid element in the dimension ''' | vDimension | '''.'); ENDIF;
As you can observe, I chop up the sentence in smaller bits, so that I can use the contents of the variables in the custom message. Note the syntax: effectively 3 single quotes in front of, and 3 after, the variable. This is to ensure that around the variables contents, I also have a pair of single quotes. To me, this serves two purposes:
- Highlighting what are the contents of variables or formula results, rather than fixed text (like the rest of the message).
- Indicating in an easy way that a variable is empty at run-time. Check out the difference in text when the element happens to be empty for some data source items:
The element '' is not a valid element in the dimension 'Accounts_PL'.against:
The element is not a valid element in the dimension 'Accounts_PL'.
You can see 2 spaces in the second case, but to me, that is not very clear. 2 single quotes without contents are cleaner. Or, if you prefer, you could use double quotes.
# Skipping invalid elements IF(DTYPE(vDim, vAccount) @<> 'N'; ITEMREJECT('The element '" | vElement | '" is not a valid element in the dimension '" | vDimension | '".'); ENDIF;yielding, for an empty element value:
The element "" is not a valid element in the dimension "Accounts_PL".
In the case of single quotes, we need to double up them. Hence, 3 single quotes right after each other, means: 1 single quote to close the string, 1 single to double up the next single quote, and then the single quote to surround the element or dimension name. Make it a habit to type 3 single quotes and you are fine :-)
Ascii code characters
Char(39) and Char(34) are the function counterparts of ' and ", respectively. Hence, the above can also be coded as:
# Skipping invalid elements IF(DTYPE(vDim, vAccount) @<> 'N'; ITEMREJECT('The element ' | Char(34) | vElement | Char(34) | ' is not a valid element in the dimension ' | Char(34) | vDimension | Char(34) | '.'); ENDIF;
To be honest, I find this notation harder to write since 3 single quotes is so easy to type, and in addition, you must remember 34 and 39 (and maybe a bunch of others as well).
Anyway, these are your main options when writing to the TM1 Message Log. When writing to a custom text file using AsciiOutput and the equivalent TextOutput, you would do a similar kind of coding. For text files, be sure to set the DataSourceAsciiQuoteCharacter in the correct way.
Separate lines of output
Char(10) can be used to output messages spanning several lines:
# Skipping invalid elements vMessage = Char(10) | 'My custom message:' | Char(10) | 'Username: ''' | Username | '''' | Char(10) | 'Area: ''' | Area | '''' | Char(10) | 'Login: ''' | Login | '''' | Char(10) | 'Group: ''' | Group | ''''; ItemReject( vMessage );
To wrap up, ItemReject is one of your main options when writing to the TM1 Message Log, as well as LogOutput. When writing to a custom text file using AsciiOutput and the equivalent TextOutput, you would do a similar kind of coding. For text files, be sure to set the DataSourceAsciiQuoteCharacter in the correct way.