This design pattern borrows heavily from Michael Mousdale's blog post regarding Return Helpers,
I would advise you read that post before continuing here as really this is just a couple of extra notes on that post.
Basically the idea is that where you have Table A and you want multiple fields from Table B, For example, you could go from GL to Cost Centers using the Cost Center code to get the Name, the Owner etc... ). Your solution might be to put in multiple lookups using the same key and return each field singularly, however, doing so is inefficient as you are asking the system to look for rows in Table B multiple times. To avoid this, we can concatenate the fields into a single return field and then divide it up as needed in Table A. Thats more or less where Michael's blog post ends.
There are a few notes that I would like to add to that idea:
Choose a separator carefully. It is important that you check your data does not contain your separator, this will disalign the result of the split function. You can use a different split separator each time you concatenate and split, but I would advise to be as consistent as possible to make it less confusing.
Use the optional parameter when splitting. The Split function will by default consider the multiple adjacent separators as a single separator, so if I were to split the following two rows with Split(Data, 2, "|")
- "A|B|C"
- "D||F"
I Would get values B for the first line and F for the second. This makes sense if you are trying to manipulate blocks of text, but not when you are tring to extract data from the position within the column separators.
To avoid this behaviour we add ,false to the "ignoreAdjacentDelimiters" split command
Split(Data,2,"|",false) will return "B" and "".
Splitting Numeric Values. Finally, when using Value() to interpret the results, you can get around useage of . or , as your decimal separator using the optional field that describes the pattern, so if your number is 2,33 following the European standard, you can interpret this correctly using =Value(data,"0,0"). See the Value function for further details. Be careful of decimal place accuracy. As the helper value is a String, you will only get exactly what is in the string, extra decimal places will have been lost. If you want to force more, you can use NumberFormat when you create the helper NumberFormat(column_name,"0.000000000000")
#DesignPattern
#TBMStudio