Splitting a Field for Subreport Link

[kskid]

Crystal 8.5 Developer
Excel Spreadsheet - Main Report
Oracle 8i - Subreport
ODBC connectivity

I was given an Excel Spreadsheet which contain names and associated case numbers. At first, it seemed pretty straight forward by creating a subreport against the Oracle DB with the case number as the subreport link.

Here's the problem. Some of the names have multiple case numbers in a given cell. How can I split the field to get the individual case numbers that I can use link to the subreport?

Sample Data

Excel Spread Sheet

Name Case
Jane Doe 04CM0002
John Doe 03TM0001 04TM2345 04TM4567


Subreport Oracle DB

Case = 03TM0001
blah... blah.... blah

Case = 04CM0002
blah... blah.... blah

Case = 04TM2345
blah... blah.... blah

Case = 04TM4567
blah... blah.... blah

Any help would be greatly appreciated. If not, I'll just have the user create separate rows.

 

[Madawc]

If the codes are fixed-length, as per your example, you could separate the case-codes into separate formula fields using Left({Case}, 8), Mid({Case}, 9, 8)], Mid({Case}, 18, 8) etc.

If they aren't, ], Split({Case}, " ") should put them into an array and you can use them from there.

Do a subreport linking for the first case code, get that right. Then export the subreport and copy it back in for the extra usages.
Put each subreport into a separate section, suppressing the section if the extra case code does not exist for that customer.

Subreports may not be the best solution. They make a lot of work for computers, but also save time for humans. You decide which is more important for your particular circumstances.


Madawc Williams (East Anglia, UK)

 

[kskid]

Thanks, Madawc. I'll give it a try and report back.

-LW (Wichita, KS)