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.

 

[kenhamady]

You can't do the split within Crystal because whatever calculation you do in a CR formula will be ineligible for use in the CR linking window.

This would have to be done in SQL.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[lbass]

Another approach might be to create separate formulas using the split function, and then use them as linking fields to individual subreports per formula. Depending upon the scope, this could result in slow performance though.

-LB

 

[elsenorjose]

Thank you both. I've asked that this be done on the database side but the same developer who created the new design says he can't do it. So, I've been forced to find a solution in Crystal.

LB, what do you recommend? Performance isn't my main concern. TBH, if it's slow, I can use that to my advantage to push for a database solution.

Thank you.

 

[lbass]

You could try my earlier suggestion. Or if there are not that many description fields, you could leave out the table and hard code a formula that switches the ID and description.

-LB

 

[elsenorjose]

Sorry LB. I'm not very familiar with how the split function works. In looking at your solution in the original post, I notice you created an array with the colors;

whileprintingrecords;
stringvar array x := split({table.string},",");
stringvar array desc := ["blue","green","pink","red","white"];
redim preserve x[ubound(desc)];
numbervar i;
stringvar y := "";
numbervar j;

for i := 1 to ubound(x) do(
for j := 1 to ubound(desc) do(
if val(right(x[i],1)) = j then
y := y + desc[j]+", "));
if len(y) > 2 then
left(y,len(y)-2)

I can't really hard code the material descriptions because more materials are added over time.

 

[CoSpringsGuy]

is there a limited number of MaterialIDs?

could you split that filed out in an SQL Commad table and link to the description table? The SQL Code would look like this in my database but im using intersystems..

SELECT $piece (Action_Parameters.Action_Parameters_ROWID,'|',1) as ID1,$piece (Action_Parameters.Action_Parameters_ROWID,'|',2) as ID2,$piece (Action_Parameters.Action_Parameters_ROWID,'|',3) as ID3,$piece (Action_Parameters.Action_Parameters_ROWID,'|',4) as ID4,$piece (Action_Parameters.Action_Parameters_ROWID,'|',5) as ID5,$piece (Action_Parameters.Action_Parameters_ROWID,'|',6) as ID6,$piece (Action_Parameters.Action_Parameters_ROWID,'|',7) as ID7,$piece (Action_Parameters.Action_Parameters_ROWID,'|',8) as ID8,$piece (Action_Parameters.Action_Parameters_ROWID,'|',9) as ID9
FROM SQLUser.Action_Parameters Action_Parameters



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

 

[CoSpringsGuy]

When I said limited I meant in the LotNumber Table..

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

 

[elsenorjose]

Hi CoSpringsGuy,

That's the rub...while there isn't an unlimited number of MaterialIDs in the Lot table, there's no set number of Materials for a particular Lot. A Lot could have 0 or more Materials.

 

[CoSpringsGuy]

ok .. I thought if there was a limited number you could split it into fields which could be linked to the description table... even if there will never be more than say... 25

but it sounds like thats not the case... Carry on

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

 

[kenhamady]

Here is an idea that might work (if your database doesn't choke).

1) Add the LotNumber table to the report and don't link it. This generates a cross-join and gives you every combination of LotNumber with every record. This won't work if the LotNumber table is very large and can't be filtered down. Crystal will warn you about unlinked tables, but it still might work.

2) Write one formula for each potential material in the string, using Split

If Count (Split({Field} , '|')) >= 3
then Split ({Field},"|") [3]
else ""

3) Add a filter that says:

{table.LotNumber} = {@Material1ID} or
{table.LotNumber} = {@Material2ID} or
{table.LotNumber} = {@Material3ID} or
{table.LotNumber} = {@Material4ID}


Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[elsenorjose]

Thanks Ken,

Just to clarify; I would take

If Count (Split({Field} , '|')) >= 3
then Split ({Field},"|") [3]
else ""

and modify it by changing the '3' to '1', '2', '4', etc for each @MaterialID formula?

Thanks.

 

[kenhamady]

Thats right. Never tried this method but the theory is sound. I'm just not sure your database will like doing the Cartesian product.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[elsenorjose]

Thanks Ken,

Your theory was sound but the database did choke. The Materials table actually has more values than are used in the Lot table since Materials can also be related to individual drug products or even components within a drug product so while there are very few Lot records with related Materials (around 15000) and there are only a couple thousand total Materials records in the Materials table, the Cartesian product resulted in a few million records. The report ran FOREVER.

I'm reading up on the syntax to split a field using SQL but SQL Server apparently doesn't have a simple SPLIT funciton.

Oh well, back to the drawing board.

 

[CoSpringsGuy]

Just want to throw this out more or less for a learning point for myself. So please!! Ken, LBass comment on it and tell me the flaws. Performance or whatever it is. I have tested it several times and it seems to work well

I have a database field similar to the one described. {Action_Parameters.Action_Parameters_ROWID}
It happens to also be delimited by the pipe. Each of the possible values can be looked up in another table for a description. {Event_Tactic_Desc.Event_Tactic}

So I created a command SQL table below and it gives me the desired results. I only have a possible 4 "ID" fields but as you see... I wrote it for up to 20 and it works fine.


SELECT $piece (Action_Parameters.Action_Parameters_ROWID,'|',1) as ID1,
$piece (Action_Parameters.Action_Parameters_ROWID,'|',2) as ID2,
$piece (Action_Parameters.Action_Parameters_ROWID,'|',3) as ID3,
$piece (Action_Parameters.Action_Parameters_ROWID,'|',4) as ID4,
$piece (Action_Parameters.Action_Parameters_ROWID,'|',5) as ID5,
$piece (Action_Parameters.Action_Parameters_ROWID,'|',6) as ID6,
$piece (Action_Parameters.Action_Parameters_ROWID,'|',7) as ID7,
$piece (Action_Parameters.Action_Parameters_ROWID,'|',8) as ID8,
$piece (Action_Parameters.Action_Parameters_ROWID,'|',9) as ID9,
$piece (Action_Parameters.Action_Parameters_ROWID,'|',10) as ID10,
$piece (Action_Parameters.Action_Parameters_ROWID,'|',11) as ID11,
$piece (Action_Parameters.Action_Parameters_ROWID,'|',12) as ID12,
$piece (Action_Parameters.Action_Parameters_ROWID,'|',13) as ID13,
$piece (Action_Parameters.Action_Parameters_ROWID,'|',14) as ID14,
$piece (Action_Parameters.Action_Parameters_ROWID,'|',15) as ID15,
$piece (Action_Parameters.Action_Parameters_ROWID,'|',16) as ID16,
$piece (Action_Parameters.Action_Parameters_ROWID,'|',17) as ID17,
$piece (Action_Parameters.Action_Parameters_ROWID,'|',18) as ID18,
$piece (Action_Parameters.Action_Parameters_ROWID,'|',19) as ID19,
$piece (Action_Parameters.Action_Parameters_ROWID,'|',20) as ID20,
Event_Tactic_Desc.Description,
Event_Tactic_Desc.Event_Tactic

FROM SQLUser.Action_Parameters Action_Parameters,SQLUser.Event_Tactic_Desc Event_Tactic_Desc

WHERE $piece (Action_Parameters.Action_Parameters_ROWID,'|',1) = Event_Tactic_Desc.Event_Tactic
OR $piece (Action_Parameters.Action_Parameters_ROWID,'|',2) = Event_Tactic_Desc.Event_Tactic
OR $piece (Action_Parameters.Action_Parameters_ROWID,'|',3) = Event_Tactic_Desc.Event_Tactic
OR $piece (Action_Parameters.Action_Parameters_ROWID,'|',4) = Event_Tactic_Desc.Event_Tactic
OR $piece (Action_Parameters.Action_Parameters_ROWID,'|',5) = Event_Tactic_Desc.Event_Tactic
OR $piece (Action_Parameters.Action_Parameters_ROWID,'|',6) = Event_Tactic_Desc.Event_Tactic
OR $piece (Action_Parameters.Action_Parameters_ROWID,'|',7) = Event_Tactic_Desc.Event_Tactic
OR $piece (Action_Parameters.Action_Parameters_ROWID,'|',8) = Event_Tactic_Desc.Event_Tactic
OR $piece (Action_Parameters.Action_Parameters_ROWID,'|',9) = Event_Tactic_Desc.Event_Tactic
OR $piece (Action_Parameters.Action_Parameters_ROWID,'|',10) = Event_Tactic_Desc.Event_Tactic
OR $piece (Action_Parameters.Action_Parameters_ROWID,'|',11) = Event_Tactic_Desc.Event_Tactic
OR $piece (Action_Parameters.Action_Parameters_ROWID,'|',12) = Event_Tactic_Desc.Event_Tactic
OR $piece (Action_Parameters.Action_Parameters_ROWID,'|',13) = Event_Tactic_Desc.Event_Tactic
OR $piece (Action_Parameters.Action_Parameters_ROWID,'|',14) = Event_Tactic_Desc.Event_Tactic
OR $piece (Action_Parameters.Action_Parameters_ROWID,'|',15) = Event_Tactic_Desc.Event_Tactic
OR $piece (Action_Parameters.Action_Parameters_ROWID,'|',16) = Event_Tactic_Desc.Event_Tactic
OR $piece (Action_Parameters.Action_Parameters_ROWID,'|',17) = Event_Tactic_Desc.Event_Tactic
OR $piece (Action_Parameters.Action_Parameters_ROWID,'|',18) = Event_Tactic_Desc.Event_Tactic
OR $piece (Action_Parameters.Action_Parameters_ROWID,'|',19) = Event_Tactic_Desc.Event_Tactic
OR $piece (Action_Parameters.Action_Parameters_ROWID,'|',20) = Event_Tactic_Desc.Event_Tactic

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

 

[elsenorjose]

Thanks again everyone for all your input. My client went back to the developers and asked them to figure out an alternative in the database. We'll be meeting in the morning to discuss options.

 

[kenhamady]

The ideal method here is to have a mapping table that sits between these two tables. One field that links back to the Lot and a separate record for each material ID in that lot. The material links to the materials table Like this:

Lot Material
A 1234
A 2345
A 3456
B 3456
B 3457

This allows unlimited materials and makes the linking simple. One option (if this can't be done directly) is to write an SP that runs on a schedule and creates/updates this table from the existing combined string.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[elsenorjose]

My thoughts exactly. Interestingly enough, the original developers of this application presented this to the new developers and the idea was rejected.

Thanks again everyone.

 

[lbass]

What would be the approximate maximum number of IDs in the string?

-LB

 

[elsenorjose]

It's hard to tell LB. I'm sure there is a practical limit to the number of materials a Lot can contain, but it's not something that's known in advance, hence the need for a solution that accommodates a '0 or more' scenario.