none
Mapping a EDI 852 to a flat pipe delimited file RRS feed

  • Question

  • Hello I am trying to map an inbound 852 to a pipe delimted file that with all of the SDQ's populated up to the SDQ22 would look like this with one labeled GOOD producing 10 DTL records one for each possible SDQ pair but with with only one LIN segment. I created 10 DTL records in my Schema DTL1, DTL2 etc, with a Logical Existence and a Value Mapping Flattening for each SDQ pair with my Looping Functoid tied to the LINloop1 and my first Occurence of my DTL record. I produced the first one below which is perfect, My probelm is when I have more than one LIN and SDQ segment I get randon emtpy DTL records and random mapped output as you see below labeled as BAD. How do I manage this if I do straight mapping without the use of an XSLT or is there an easier way to do this? Any help much appreciated.

    Mike

    GOOD

    HDR|20100719|20100725~

    DTL|001|VP|NVX9200-C1819IVZN-00|UP|652810814218|||QS|92|005|1|EA~

    DTL|001|VP|NVX9200-C1819IVZN-00|UP|652810814218|||QS|92|007|-1|EA~

    DTL|001|VP|NVX9200-C1819IVZN-00|UP|652810814218|||QS|92|011|1|EA~

    DTL|001|VP|NVX9200-C1819IVZN-00|UP|652810814218|||QS|92|015|-1|EA~

    DTL|001|VP|NVX9200-C1819IVZN-00|UP|652810814218|||QS|92|016|-1|EA~

    DTL|001|VP|NVX9200-C1819IVZN-00|UP|652810814218|||QS|92|021|2|EA~

    DTL|001|VP|NVX9200-C1819IVZN-00|UP|652810814218|||QS|92|024|2|EA~

    DTL|001|VP|NVX9200-C1819IVZN-00|UP|652810814218|||QS|92|025|1|EA~

    DTL|001|VP|NVX9200-C1819IVZN-00|UP|652810814218|||QS|92|026|2|EA~

    DTL|001|VP|NVX9200-C1819IVZN-00|UP|652810814218|||QS|92|027|1|EA~

    BAD

    HDR|20100719|20100725~

    DTL|001|VP|NVX9200-C1819IVZN-00|UP|652810814218|||QS|92|005|1|EA~

    DTL|002|VP|NVX7100-C1819IVZN-00|UP|652810814225|||QS|92|010|1|EA~

    DTL|001|002|VP|VP|NVX9200-C1819IVZN-00|NVX7100-C1819IVZN-00|UP|UP|652810814218|652810814225|||QS|QS|92|92|007|025|-1|1|EA|EA~

    DTL|001|002|VP|VP|NVX9200-C1819IVZN-00|NVX7100-C1819IVZN-00|UP|UP|652810814218|652810814225|||QS|QS|92|92|011|028|1|1|EA|EA~

    DTL|001|VP|VP|NVX9200-C1819IVZN-00|UP|652810814218|||QS|92|015|-1|EA~

    DTL|001|VP|NVX9200-C1819IVZN-00|UP|652810814218|||QS|92|016|-1|EA~

    DTL||||||||||||~

    DTL|001|VP|NVX9200-C1819IVZN-00|UP|652810814218|||QS|92|021|2|EA~

    DTL||||||||||||~

    DTL|001|VP|NVX9200-C1819IVZN-00|UP|652810814218|||QS|92|024|2|EA~

    DTL||||||||||||~

    DTL|001|VP|NVX9200-C1819IVZN-00|UP|652810814218|||QS|92|025|1|EA~

    DTL||||||||||||~

    DTL|001|VP|NVX9200-C1819IVZN-00|UP|652810814218|||QS|92|026|2|EA~

    DTL||||||||||||~

    DTL|001|VP|NVX9200-C1819IVZN-00|UP|652810814218|||QS|92|027|1|EA~

    DTL||||||||||||~

    • Moved by Ben Cline1Moderator Tuesday, September 7, 2010 3:31 AM Related to EDI (From:BizTalk Server General)
    Tuesday, September 7, 2010 12:29 AM

Answers

  • Unless you know ecxaclty how many records you want to output every time, I would not use the min max. That is only for when you want to output a specific number of records. If repeating occurs many times and dont know how many is the max, you prob do not want to set that. You will want to grab all the records until you are done and then go to the next row. It all depends on the guide you follow. If you are getting random DTL segments, then there is something that is off in the map or loop, given that the schemas are right. Each record should match and the ramdomness may be part of somehting else. Its almost as either a loop is set wrong or the schema is missing something. I strongly encourage that you either start with only one link and go from there. Delelete all links and functoids if possible. If yuo are sure the schema matches the imput file, then work one link at a time. I found it is very tempting to do the whole map and link everything before testing, and I learned that is not always the best becuase I am not sure where the error occurs. I hope this helps.


    Please Indicate "Mark as Answer" if this Post has Answered the Question
    Wednesday, September 8, 2010 1:15 PM
  • Mike,

    One of the problems trying to get an answer to this question on the forum is that looping in BizTalk mapping is, if not complex, unpredictable.  One seemingly innocuous link not apparently involved in the looping can change the loop behavior.  In our book, four of the nineteen chapters deal exclusively with looping.  The ideas presented so far are all valid ideas, but whether or not they work in your situation depends on what else is happening in your map.

    My suggestion is that you duplicate your map and remove all the links in the duplicate.  Then begin remapping by doing the looping nodes first.  Choose a mandatory data element in the source that can drive the loop, link it, test the map, and review the results.  If the loop is correct, then add more data links and retest.  If the loop is not correct, try to fix it.  We call this iterative testing.  You may find that you add link after link without a problem.  Then suddenly you add a link and boom the loop doesn't work any more.  Test frequently, if at all possible after almost every link from a new node.

    Also be aware that the format of the source schema is as important as the format of the output schema.  The BT engine infers some mapping code from the structure of the source schema.

    If all else fails, zip up your project and email it to me at jim@secondstaredi.com and I'll take a look at it.

     


    Jim -- Pro Mapping in BizTalk 2009, Apress Books, March 23, 2009
    Monday, September 13, 2010 12:38 PM

All replies

  • You need to add a condition to the loop based on existance. If that loop is not present then it will not map. You can use a looping functoid in conjunction to an = fucntoid or somehting similar and you will get your results. Add the condition to the top of the loop you want to set the condition to and not the individual segment. This will take care of that entire loop.
    Please Indicate "Mark as Answer" if this Post has Answered the Question
    Tuesday, September 7, 2010 11:50 AM
  • So in other words I can put a Logical Existence functiod and attach it to my Looping Functoid which I have pointed at my LINLoop or is there another functoid I should be using for this purpose?  Thanks so much for your help.

    Mike

    Tuesday, September 7, 2010 3:08 PM
  • Correct. However, I typically like to use the existance of qualifiers, if they are available. I believe the qualifier is better becasue you know what is the ecxact value. But if you can not, then use logical existance. Let me know if you have any problems./
    Please Indicate "Mark as Answer" if this Post has Answered the Question
    Tuesday, September 7, 2010 8:06 PM
  • Mike,

    Not quite.  First, don't use the Logical Exist functoid unless you are sure that the empty node will NOT be present.   This functoid returns true/false based on the presence/absence of the node, not the data.  You should instead use a node from the LIN segment that is mandatory for each instance of the loop, and pass that through an Is String functoid.

    Thus

    <LineLoop /> will return false while <LineLoop><LIN><LIN02>data</LIN02></LIN></LineLoop> will return true.

    You do not attach the functoid to the looping functoid, rather you attach it to the same output node to which the looping functoid is attached.

     


    Jim -- Pro Mapping in BizTalk 2009, Apress Books, March 23, 2009
    Tuesday, September 7, 2010 8:11 PM
  • Thanks. Actually in this case I don't need to check the value of an element just the presence. The SDQ segment or node can have a minimum of 4 elements and a maximum of 22 so depending on what was sold on any given line item my Logical existence functoid should be OK. So I set one up for each possible pair and only want to create a DTL record populated from the LIN, ZA and SDQ segments based on the presence of those pairs. The DTL record can contain only 12 elements so for one line item there can be as many as 10 DTL records based on how many SDQ pairs are present. Somehow the looping seems off though as my map is creating random DTL records. In a case like this what should my MIN/MAX be set to for the Parent and Child Elements?

    Mike

    Tuesday, September 7, 2010 11:12 PM
  • Unless you know ecxaclty how many records you want to output every time, I would not use the min max. That is only for when you want to output a specific number of records. If repeating occurs many times and dont know how many is the max, you prob do not want to set that. You will want to grab all the records until you are done and then go to the next row. It all depends on the guide you follow. If you are getting random DTL segments, then there is something that is off in the map or loop, given that the schemas are right. Each record should match and the ramdomness may be part of somehting else. Its almost as either a loop is set wrong or the schema is missing something. I strongly encourage that you either start with only one link and go from there. Delelete all links and functoids if possible. If yuo are sure the schema matches the imput file, then work one link at a time. I found it is very tempting to do the whole map and link everything before testing, and I learned that is not always the best becuase I am not sure where the error occurs. I hope this helps.


    Please Indicate "Mark as Answer" if this Post has Answered the Question
    Wednesday, September 8, 2010 1:15 PM
  • Gotcha-thanks for all the comments. I think my issue is that my SCHEMA had all the DTL records at the PARENT level so the looping assumed I wanted a Record written out each time a loop was passed. I am going to try and redefine my schema with the subsequent records as Children and see what happens.

    Thanks to all this is a great learning experience for me.

     

    Mike

    Thursday, September 9, 2010 1:40 PM
  • Anytime. I also recomend checking this site out. It has a bunch of examples you can use. Good luck.

    http://www.biztalkgurus.com/


    Please Indicate "Mark as Answer" if this Post has Answered the Question
    Thursday, September 9, 2010 5:07 PM
  • Hi Guys:

    One more question for you. I am happy to say that I now get all of my data on my output file but I am left with one challenge. My map is creating empty DTL records even though I have a Logical existence set to check each SDQ pair so in the example below the first line had 10 pairs and the second 3 so what it looks like is happening is that BizTalk is writing out the number of DTL records from my schema whether there is output or not. Is there a simple way to suppress any DTL record that has no data populated in any of the target nodes?

     

    HDR|20100719|20100725~

    DTL|001|VP|NVX9200-C1819IVZN-00|UP|652810814218|||QS|92|005|1|EA~

    DTL|001|VP|NVX9200-C1819IVZN-00|UP|652810814218|||QS|92|007|-1|EA~

    DTL|001|VP|NVX9200-C1819IVZN-00|UP|652810814218|||QS|92|011|1|EA~

    DTL|001|VP|NVX9200-C1819IVZN-00|UP|652810814218|||QS|92|015|-1|EA~

    DTL|001|VP|NVX9200-C1819IVZN-00|UP|652810814218|||QS|92|016|-1|EA~

    DTL|001|VP|NVX9200-C1819IVZN-00|UP|652810814218|||QS|92|021|2|EA~

    DTL|001|VP|NVX9200-C1819IVZN-00|UP|652810814218|||QS|92|024|2|EA~

    DTL|001|VP|NVX9200-C1819IVZN-00|UP|652810814218|||QS|92|025|1|EA~

    DTL|001|VP|NVX9200-C1819IVZN-00|UP|652810814218|||QS|92|026|2|EA~

    DTL|001|VP|NVX9200-C1819IVZN-00|UP|652810814218|||QS|92|027|1|EA~

    DTL|002|VP|NVX7100-C1819IVZN-00|UP|652810814225|||QS|92|010|1|EA~

    DTL|002|VP|NVX7100-C1819IVZN-00|UP|652810814225|||QS|92|025|1|EA~

    DTL|002|VP|NVX7100-C1819IVZN-00|UP|652810814225|||QS|92|028|1|EA~

    DTL||||||||||||~

    DTL||||||||||||~

    DTL||||||||||||~

    DTL||||||||||||~

    DTL||||||||||||~

    DTL||||||||||||~

    DTL||||||||||||~

    Saturday, September 11, 2010 3:47 PM
  • Pair those segments with an = functoid and a loop. That will tell biztalk to map it if present. 
    Please Indicate "Mark as Answer" if this Post has Answered the Question
    Saturday, September 11, 2010 6:00 PM
  • OK can you show me an example of what this would look like in a map? It tells me that the functiod is used for comparing one value to another. What I am trying to do is say only write the record if paired SDQ couples are present. I have my SDQ looped to my Parent DTL record which has 10 children DTL records below it.

     

    Use the Equal functoid to return the value “true” if the first input parameter is equal to the second input parameter. This functoid requires two input parameters.

    Sunday, September 12, 2010 12:24 AM
  • I missunderstood what you were trying to do. Try this link and it will help. There are several ways to do what you want to do. It is a very comon thing. You will use a combination of a looping fuctoid and exists. You can also use a value mapping with a condition and a loop. Try this book and it may help.

    http://www.amazon.com/Mapping-BizTalk-Server-Experts-Voice/dp/1430218576


    Please Indicate "Mark as Answer" if this Post has Answered the Question
    Sunday, September 12, 2010 2:11 PM
  • Mike,

    One of the problems trying to get an answer to this question on the forum is that looping in BizTalk mapping is, if not complex, unpredictable.  One seemingly innocuous link not apparently involved in the looping can change the loop behavior.  In our book, four of the nineteen chapters deal exclusively with looping.  The ideas presented so far are all valid ideas, but whether or not they work in your situation depends on what else is happening in your map.

    My suggestion is that you duplicate your map and remove all the links in the duplicate.  Then begin remapping by doing the looping nodes first.  Choose a mandatory data element in the source that can drive the loop, link it, test the map, and review the results.  If the loop is correct, then add more data links and retest.  If the loop is not correct, try to fix it.  We call this iterative testing.  You may find that you add link after link without a problem.  Then suddenly you add a link and boom the loop doesn't work any more.  Test frequently, if at all possible after almost every link from a new node.

    Also be aware that the format of the source schema is as important as the format of the output schema.  The BT engine infers some mapping code from the structure of the source schema.

    If all else fails, zip up your project and email it to me at jim@secondstaredi.com and I'll take a look at it.

     


    Jim -- Pro Mapping in BizTalk 2009, Apress Books, March 23, 2009
    Monday, September 13, 2010 12:38 PM
  • I missunderstood what you were trying to do. Try this link and it will help. There are several ways to do what you want to do. It is a very comon thing. You will use a combination of a looping fuctoid and exists. You can also use a value mapping with a condition and a loop. Try this book and it may help.

    http://www.amazon.com/Mapping-BizTalk-Server-Experts-Voice/dp/1430218576


    Please Indicate "Mark as Answer" if this Post has Answered the Question


    Mike;

    Please note the link. I inenvertendly copied the wrong link earlier. I Googled the book trying to find a link for your to purchase the book and somehow I copied and pasted the wrong link. Please use this link. I recomend you purchase the book. It was a good buy and it answers a lot of the questions that you have. It was very reasonably priced and I use it all the time for my mapping questions.


    Please Indicate "Mark as Answer" if this Post has Answered the Question
    Monday, September 13, 2010 2:40 PM
  • Jim:

    Great tips and I was on the same wavelength. I have a feeling the pattern I am seeing has something to do with my target schema and the way I defined it to have 10 DTL records with a Tag Identifier of DTL and a child delimter with a Pipe. My logical existence functoids are working perfectly as well as my loop as I get all my output when I see 5 or 6 SDQ's repeating and maxed out to the SDQ21 so and I am thinking I may create another map with just one DTL and see if my good loop handles it. I ordered the BizTalk 2009 book last week and can't wait to look  the logic in there. I have a major project this year to convert all of our outsourced maps to BizTalk 2009 so this will be invaluable.

    Mike

     

     

    Tuesday, September 14, 2010 1:36 AM
  • Jim:

    I have one more question related to the same process flow above. We receive an 852 interchange that contains 2 ST's inside one for QA and one for QS. BizTalk is creating 2 output files but I would prefer to have them combined into one. (text files output) How do I accomplish this? I bought the book but I see no references to this and I was just wondering if there is a Pipeline property or Party setting perhaps? Any help much appreciated.

     

     

    Thursday, October 7, 2010 1:52 PM