Biztalk Mapping Problem, Ideas please.

Question

Hey folks,

I'm working on a moderately complex flat file schema and I've had a curve ball thrown at me. Here's what the input looks like:

BCT121,C,7.35,6.5,0.39,7216,"SMITH, TRACEY",13456321,CLASS DRESSMAKING SHOPPE,CLASS DRESSMAKING SHOPPE,VAN,BC,20090109,20090112,20090127,V,0.46, BCT121,C,14.7,13.01,0.78,7216,"SMITH, TRACEY",13456321,CLASS DRESSMAKING SHOPPE,CLASS DRESSMAKING SHOPPE,VAN,BC,20090110,20090112,20090127,V,0.91, BCT121,C,60.27,53.34,3.2,5812,"SMITH, TRACEY",13456321,ME-N-ED'S PIZZA PARLORS,ME-N-ED'S PIZZA PARLORS,VAN,BC,20090110,20090112,20090127,V,3.73, BCT121,C,201.56,179.96,0,5200,"SMITH, TRACEY",13456321,THE HOME DEPOT #7145,THE HOME DEPOT #7145,VAN,BC,20090110,20090112,20090127,V,21.6,REF.#^1^

Ok, so the output that the client wants is the following:

1 header line called PRQT for each person/card number and a line for each of the transactions above.. so for Tracy, we would have 1 PRQT line and 4 PRLN lines, which are the transaction details.. so it would look like something like:

PRQT, SMIT6321, 13456321, details, details, etc.

PRLN, SMIT6321,7.35, 6.5, etc. etc.

PRLN, SMIT6321, 14.7, 13.01, etc, etc.

PRLN, etc. etc.

PRLN, etc. etc.

ok, so my main problem is how to insert only one prqt record for the header? I'm looking at logical functoids to see if there is one that can look up to see if an existing value has been created for the output schema. My primary key is the SMIT6321 (last name + last four digits of the card number). So basically if I was coding this in C# I would create an array of previously inserted PRQT keys and during my loop, I would perform a look up on this when I came across to see if I've already created a record. If I had already created, I would skip so I didn't create a duplicate PRQT record. Note that records are created chronologically in the source document, so the above records will likely be jumbled up and not sequential like the sample above.

Can anyone point me in the right direction? What functoids should I use? Can Biztalk Mapper handle this sort of thing? I'm sure it can... I just need to figure it out.

thanks,

Eric

Answer 1

Eric,

I would look at doing the map in custom XSLT. The mapper (while powerful in its own right) is not going to give you 100% of what you want without going through hoops. By using custom XSLT you'll be able to perform full sorts on fields and properly navigate the document to get the desired output. For dealing with problems like this in the future there's a new book called BizTalk 2009 Pro Mapping by APress that goes into problems like yours.

-Bryan