Now let’s relax with something easy. Supposing we receive orders as a CSV file (or from the database) with a very simple structure.

O,09.07.2009,12345,Derrick,Munich,Germany
I,4711,3
I,4712,1
I,4713,5
I,4714,2
O,09.07.2009,12346,Kottan,Vienna,Austria
I,4711,1
I,4713,3
I,4715,6
O,09.07.2009,12347,Kojak,New York,USA
I,4717,11
O,09.07.2009,12348,Maigret,Paris,France
I,4712,4

We can see from the tree structure what the fields mean.

images/download/attachments/21302642/5_3_Strukturen_EN-version-3-modificationdate-1535011152000-api-v2.png

We use value NL (newline) for the source structure attribute Delimiter Column/Row and the nodes split the individual rows into fields where there is a comma. OrderHeader grabs the rows that start with O, Item picks up all those beginning with I. So far, so easy. As you can already tell from the trees, we actually want to route the data as it stands, which involves a simple 1:1 mapping.

Although no changes to the structure or content of the data are intended, we do want orders from different countries to be forwarded to different members of staff. The following rules should apply: Orders from German-speaking countries - i.e. Germany, Austria and Switzerland - are supposed to go to Mr Meier, orders from the rest of Europe to Ms Müller, and everything else to Ms Schmitz. The customer’s country is in the order header. Helpfully, the country is always given in English (or the abbreviation USA) so we do not need to go to the effort of incorporating translations. If we load the data for a test, we get the following result.

images/download/attachments/21302642/Tipps_und_Tricks_zu_DataWizard_umgesetzt_html_1f097128-version-2-modificationdate-1535011342000-api-v2.png

How convenient! Our structure creates four individual records straight away. If you do not know why, please read the chapter on memory, phase 2, CSV data as soon as possible. Why is this convenient? Because Lobster_data offers the wonderful option to have each record follow all the defined Response Routes. And for each response route, you can set a variable to determine whether the route is followed or not. So, what do we need?

Three variables: var__G (German-speaking), var__E (Europe) and var__W (world).
Three Response Routes that are followed depending on the variables, and send each order to the appropriate member of staff (e.g. by email).
A way of setting one of the variables to true depending on the customer’s country.

We can create the variables and the Response Routes without any problems. Just remember that you need boolean variables because their values have to be true or false. So let’s turn to point 3. How do we decide which variable gets set to true? Or rather, where? Ideally, it should be directly in the field that already holds the country: Country below OrderHeader. Let’s start with the German-speaking countries. That’s nice and manageable.

Nice Try

First of all, we are clever enough to set all of the variables to false so that there is no old data to mess things up for us.

1) result = save variable a(b) type-safe
a constant: var__G
b constant: false
 
2) result = save variable a(b) type-safe
a constant: var__E
b constant: false
 
3) result = save variable a(b) type-safe
a constant: var__W
b constant: false

Great. Now we want to know whether the country has the value Germany, Austria or Switzerland. Perhaps like this:

4) result = a equals b
a linked field:
b constant: Germany
 
5) result = a equals b
a linked field:
b constant: Austria
 
6) result = a equals b
a linked field:
b constant: Switzerland

Each function returns true or false. So we need an OR operation.

7) result = logical-or(a, b,[c,d,e])
a result: 4
b result: 5
c result: 6
d constant:
e constant:
 
8) result = save variable a(b) type-safe
a constant: var__G
b result: 7

Fair enough. At least the logical-or function allows you to compare up to five values at the same time. Let’s look at the test result, once for the order from Germany and once for the order from the USA.

We see two things: Firstly, the function chain has clearly worked with these two datasets (and also with the others, trust us!), and secondly, the field unfortunately no longer contains the country, but the variable content. This is because when one or more functions (and no fixed values) are assigned to a field, it will always end up containing the result of the last executed function. So we absolutely must make sure that that field shows the actual country name again at the end.

9) result = copy(field/value/variable)
a linked field:

Now it is correct again. Unfortunately, we have now used five functions (out of nine) just to check whether the order is from one of the three German-speaking countries. Now imagine what a monster the function chain would become if you wanted to check it against all European countries. And don’t forget that you can only have a maximum of five values in a single logical-or(a, b,[c,d,e]) function. If you want to compare more than that, you 'get' to cascade everything. Have fun!

Not Much Better

So let’s look for an alternative to this construction of equals and logical-or functions. The various functions of the replace value 'family' are very nice and well suited to many situations. We already used one of these in the previous example with the EDIFACT NAD segments, to allow us to determine the name of a variable. This time, we will use the same function to determine the value.

4) result = replace value(a, list b, list c, default d)
a linked field:
b constant: Germany,Austria,Switzerland
c constant: true,true,true
d constant: false
 
5) result = save variable a(b) type-safe
a constant: var__G
b result: 4

Then comes the copy function, of course, and we still need to reset the variables at the beginning. At least it is two functions instead of five. Definitely a step forward. But in this case, where we only want to know whether a list contains a particular value, there is an even better function.

4) result = check(a, list b)
a linked field:
b constant: Germany,Austria,Switzerland

You can imagine what this does: if it finds the allocated field value in the parameter b list, it returns true, otherwise, it returns false. The rest remains the same. Ok then, so we have reduced the number of functions and, in principle, we would only need two functions each for all of Europe and the rest of the world. That is pretty good if we only have a handful of values that each need to be checked. But my god, how long would that make the list for Europe alone? Not to mention the rest of the world! No, there must be something more manageable! There is.

Now We Are Getting Somewhere

The replace value 'family' of functions, which we mentioned earlier, also includes the following one.

4) result = replace value(a, name of file b, default value c [, wildcard d])
a linked field:
b constant: ./conf/TipsAndTricks/countries.csv
c constant: W
d constant:

This function uses a file named countries.csv, which looks like follows.

Germany=G
Austria=G
Switzerland=G
France=E
Belgium=E
Netherlands=E

The file must be encoded in the Latin1 character set (= ISO-8859_1); there is another function that allows you to specify the character set and can be used for more complex files. Why not have a look at the function called replace value(a, name of file b, default value c[, wildcard d]) in the function documentation.

Here’s the interesting thing about the file: You only need to specify the countries with the identifiers G and E, because the default value in parameter c ensures that all the others automatically produce a W. If a new category is introduced, e.g. A for everything in America, then all you have to do is change or add a few entries in the file, and, of course, create the new variable var__A and populate it in a few additional functions, and everything is taken care of again. A file like this is much easier to edit than ultra-long function parameters, and it can also be reused (for other profiles). If we take away the copy function, let’s see what comes out now:

You already know how to convert the letter into a variable. The replace value function is an excellent one to use with the two lists. Just add in a true and we are done. Just for fun, we could also determine the variable as follows.

5) result = concat( a, b )
a constant: var__
b result: 4

Simply add the letter to the prefix var__, and you have the variable names you need. Not much of an improvement with three variables, but if you have a larger number, it does make things quite a bit clearer.