Split a field value to use as a join field

[elsenorjose]

Hello all,

I've been searching the forums for an answer to my problem and the closest I've found is thread149-1217977

The problem is, it doesn't appear to have ever been resolved. I have a similar problem in that a database redesign resulted in what used to be multiple rows in a table being concatenated into one row with certain IDs being stored in a pipe delimited field; bad design, I know, but it's what I have to work with. What I need to do is split out those values and then join them to another table. For illustration purposes, I have a table, Lot, with the following fields (there are more but these are the important ones:

LotNumber
MaterialIDs

There is only one LotNumber and in the MaterialIDs field I have:

Material1ID|Material2ID|Material3ID...etc, literally, concatenated with the | symbol

I need to join the Material IDs to another table called Materials, that holds the description of each material each with a unique MaterialID:

MaterialID
Description

So Material1ID = Plastic Bottle, Material2ID = Child Proof Cap, and Material3ID = Foil Lining.

Since I can't map the concatenated MaterialIDs field in the Lot table to the normalized MaterialID in the Materials table, I need to split the MaterialIDs field and then join it to the MaterialID field in the Materials table and display the Description values.

Is this possible? I'm using Crystal Reports XI Developer and MS SQL Server 2005.

Thank you.

 

[lbass]

Are we talking 10 or 100's? Ballpark. I don't mean distinct types--I mean the total number of IDs per string.

-LB

 

[elsenorjose]

I'll hazard a guess and say, a dozen or so perhaps? I've done a quick search through the table and the record with the most material IDs so far only has 4.

 

[lbass]

Okay, let me think about this a bit more. You could still do separate formulas for each and link each to it's own subreport, but maybe there's a better way.

CoSpringsGuy, I may be wrong, but it appears you are in essence linking multiple field components to the same table, so it seems like only the first value that meets the criterion would be returned--since each element is from the same row. I'm not familiar with the $piece function you are using though.

-LB

 

[CoSpringsGuy]

LBass,

I did some research and found that the $Piece function seems to be generic to Intersystems Cache. I could not find another flavor of SQL that had an easy way to accomplish this. Looking close at my code though I see that you are right. As I said in the beginning I jumped on this thread because it interested me and I thought I could learn something. SQL is not yet my strong point. Thanks for the feedback!

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[lbass]

Okay, try this. First open a new report that uses only the Materials table. Insert a text box in the detail section and manually type in the following:

if split({Lot.MaterialIDs},"|") =

...and then insert the field {Materials.MaterialID} followed by the word 'then' and then insert the field {Materials.Description}. This assumes that these fields are strings. Then run the report and export the results to text, choosing "application". Then copy the results and paste it the following formula where I have indicated <results>:

stringvar x := "";
numbervar i;
numbervar j := ubound(split({Lot.MaterialIDs},"|"));
for i := 1 to j do(
x := x + (
<results>
) + ','
);
left(x,len(x)-1)

If you want a vertical list instead of a comma delimited string, replace ',' with chr(13) and then format the result to "can grow."

This will be fairly slow because each row is being checked against the Materials table right in the formula.

-LB

 

[elsenorjose]

Hi LB,

Would I need to do this every time a new material is added?

 

[lbass]

Yes, if the Materials table has new materials added, you'd have to update the embedded if/then formula. I thought maybe you had a standard list of materials. Oh well.

-LB

 

[elsenorjose]

I could probably safely assume a limit of materials since things in biomed move so slowly but in a year or two when a new drug is developed, you can bet there will be a new material or two added to the list.

In the meantime, I've been working with the developers to write a stored procedure for a subreport. I'll pass the list of IDs to the sub for the proc and retrieve only the material names. Wish me luck.