Mapping problem, need ideas please.

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 functiods 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 perform a look up on this when I came across to see if I’ve already created a record. Note that records are created chronologically in the source document, so the above records can be jumbled up and not sequentially 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 again guys,

Eric