Mapping became a whole lot more of a pleasure than a pain in BizTalk 2010. Finally it became a lot easier to manage complex maps and the immediate temptation to just write up some custom XSLT has been put to the backburner (for me at least) for all but those specific scenarios where the mapping IDE will not play ball.
An interesting scenario I was faced with was mapping a flat message structure into a message which had a repeating record with elements in it.
An example output of the source structure looks like the below (note that I have skipped the records suffixed with 7-18 but assume they’re there). The schema for this message is a delimited flat file schema, which will always contain 40 elements (in my scenario, setting these up as a repeating record in the flat file schema was not possible).
An example destination message looks like the below (there could actually be upto 20 records in this message, but I’ve kept it short in this screenshot). One of the rules I was given was that if any of the HazCLx elements contained an empty string in the source message, then a record shouldn’t be created with that HazCLx element and it’s corresponding HazUNx element.
Ok, so first thing we need to do is drag a table looping functoid onto the mapping area.
If you inspect the Functoid Description, you’ll see that the first parameter is a scoping parameter so what you need to do is connect it to the root of the record which contains these elements, which could be your root node or as it is in my case, the CheckIn record.
The second parameter is meant to be the size of the table you are trying to create. Now just by looking at the schema you would guess it would be 2, but there is a bit more to this. If you move to the Table Looping Grid tab (I believe in BizTalk 2009 and earlier you will have to access this from the properties explorer area while focused on the functoid), you’ll notice that there is an option that allows you to treat the first column in the table as a logical gate for the row. If you select this option, that means that the first column will contain a boolean value that will be used to evaluate whether the current row in the table is valid or not. In our case this is important because we only want to output a record if the HazCLx element if the source message contains a non-empty string. So let’s tick this option, and let’s set the second parameter of the looping functoid to 3, since there are going to be 3 columns – the boolean gate column, the HazCL column, and the HazUN column. Your functoid parameters should look like the below.
Ok, now for the next step you will need to supply all the values that will be needed to create this table. In order to provide the value for the gate column, you can use logical not functoids, the first parameter of the functoid being the HazCLx element, and the second parameter being blank. You’ll need 20 of these in this case since there are always 20 HazCLx elements in the source message. The output of the logical not functoids should go to the table looping functoid. You should also make links from the HazCLx and HazUNx elements in the source message to the table looping functoid. Note that it does not matter what order you supply the parameters to the table looping functoid (except for the very first two parameters which you should have according to the screenshot just above this paragraph).
Your map should now look somewhat like the below.
As you can see, things are getting quite messy, what with there being the 2 initializing parameters + 60 value parameters being fed to the table looping functoid. The next step has the potential to be a nightmare, but I have a trick to make it a lot easier for you. You need to open the Table Looping Grid tab now and assign the values of the table looping functoid to the relevant rows and columns. You do this by clicking on the drop down arrow in each cell and choosing which parameter’s value to use for that cell. You’ll need to do this for each of the 20 rows. While there isn’t much of the problem for column 2 (HazCLx) and column 3 (HazCLUnx), how on earth are you meant to choose which parameter to use for the gated column when all the parameters supplied by the 20 Not Equal functoids have the same name – “Not Equal”. See the below screenshot to see what I mean.
So here’s a nifty trick for you BizTalkers. If you click on the actual link (the lines between elements and functoids/other elements etc…) on a map, you’ll see that there is a parameter in the properties explorer called Label. Setting a value for the label effectively gives the link a friendly name. In this situation I am going to name all of the links from my Not Equal functoids to my Table Looping functoid Nx where x is the current iteration of the table row. So the label for the link coming out of the Not Equal functoid driven off HazCL1 is N1, and the one for HazCL2 is N2 etc…..
If you now go back to the Table Looping Grid tab in the table looping functoid you’ll have a nice surprise waiting for you. Rather than the generic name “Not Equal” in the drop down selections, you now see N1, N2, N3 etc…. and thus can choose appropriately. This tab should look like the below once you are done setting it up (should keep going on until the 20th row).
Ok, so we now have a table structure which is populated with all the values from the source message. Now all we need is to get each row in the table to make up a record in the destination message. The very first thing you want to do is to define the output scope of the table, so the output of the Table Looping functoid should be connected to the repeating record hazardous in the destination schema.
The next thing we want to do is to drag two table extractor functoids onto the mapping area. The first parameter for these functoids will be the table they need to perform the extraction from, so drag a link from the table looping functoid to each of these table extractor functoids. The second parameter for the table extractor functoids is the column number in the table that they should be extracting the values from. So this parameter should have a value of 2 for the first table extractor functoid and a value of 3 for the second table extractor functoid (remember column 1 is the gate and thus it’s value doesn’t need to be extracted). Lastly set the ouput of the first table extractor functoid to the hazardousClass element in the destination schema and the output of the second table extractor functoid to the hazardousUN element in the destination schema. You’re map should now look like the below. Please let me know if you need any help with this.