How to add an IF-condition to your mapping
If you have some skills in any programming language, you will certainly know the IF-THEN-ELSE statement: if a table field or a variable has a specific value X, then do this, if not do something else. It is possible to reproduce this behaviour in a mapping, there are several ways to do so and in this article we want to explain them.
For the first case, let’s assume that you want to export a posted sales invoice. You have a sales invoice header and several invoice lines. The invoice lines contain some items and some comments above and between the lines. Without going deep into your mapping structure, I’m sure that you have a loop line for your sales invoice header and some data mapping lines below the line, followed by a loop line for your sales lines and their mapping lines:
Sales Invoice Header
– EDI Document
– Invoice header data mapping line 1
– Invoice header data mapping line 2
– Invoice header data mapping line …
– Sales Invoice Line
— Invoice line data mapping line 1
— and so on …
The hyphens shall indicate the level of indention from the mapping.
The question is: how do you seperate the item lines from the lines with the comments? It is dangerous to assume that the order of the item and comment lines is always the same, so you need a solution that is independent of the order.
The solution is to add two new loop lines indented below the sales invoice line. In the scenario pictured above all comment lines have the type “empty”, all other lines have a certain type, that makes the lines distinguishable. The first new loop line is a loop over the sales invoice line table, with the same data item link and filters as your first loop, but you add a new filter: Type=CONST(‘ ‘). You can read this as “If the sales invoice line type is empty” – your first part of the IF statement!
Now for the “THEN”-part: all mapping lines that are indented below this new line form the “THEN”-part and you can put the comments to the table “EDI Additional Information” for example, with all necessary data item links and filters.
And what about all the other lines with your items (and G/L accounts and resources)? Well, independent of your general exporting rules that apply for your mapping, these lines have a type that is different from empty. That means you can add a second new line to your mapping, below the IF-line, with the same data item link and filters as your first loop, but now you add the filter on the type like this: Type=FILTER(<>0). You can read it as “if the sales invoice line type is anything ELSE than empty” – your ELSE part of the statement. In this case you probably want to create some EDI Document Lines that contain the informations of the items (and resources and G/L accounts), so you indent the EDI Document Line mapping line below this else line and add the data mapping lines appropriatly.
If you want to have a closer look at this example, you can use the template for exporting posted sales invoices from the assistant: create a new blank NAV mapping with and project code and code, then run the assistant from the ribbon “Action” via “Run Create and Update Wizard” and select the first option:
Instead of inserting the second Sales Invoice Line you can also a conditional type line. Let’s take a step back to the loop line “Sales Invoice Line”: it loops through every invoice line, i.e. every invoice line is passed to the mapping one after another. If it’s a comment line, the information will be written to the table EDI Additional Information – which means that there’s a record in the filter.
With the conditional type line, you can take advantage of the fact that if you have a non-comment line, the filter mentioned above doesn’t find a record. The conditional line can check this case, with the function “ISEMPTY”. The name is literally, if there is no record in the filter of the Sales Invoice Comment line, all mapping lines indented below the conditional line will be executed. Here, the EDI Document Lines will be written. The complete properties of the mapping line look something like this:
If you have imported the template into a new mapping you can change the mapping line named “ELSE” to this conditional line.
Another way to create a conditional is using a value translation. For example you need to export the unit of measure codes for your items to an EDIFACT type message. It is very likely that the codes in your NAV system don’t match the ones that are expected in EDIFACT, so you need to translate them: if the code is “PALLET”, write “PAL” to the buffer table. If it is “PIECE”, then write “PCE”. If “HOUR”, write “HUR”. And so on.
You CAN use the filtering described above and add a sales (shipment probably in this case) line below the original one for every unit of measure code and write the corresponding code to the EDI Document Line, but it is much easier to use a value translation. Each “source” unit of measure code is assigned a “destination” unit of measure code, this destination code is written to the destination field in the mapping line. For setting up a value translation, please refer to the knowledge base article [insert link here]