Splitting comma delimited strings 1

[NattyCat]

Hi,

Am using Crystal Version 9 against SQL Server 2000.

I have one field, which contains the following:

"15006543,1234432,777765200001" (these represent account numbers in a banking system)

I need to split this string and return rows. I need to do this so that I can use a subreport to return the address details for the matching accounts which reside in a different table.

I tried to use the"split" function. This gave me the error "The Result of the Formula Cannot be an Array".

Has anyone got any idea how I can return individual values? Bearing in mind that they are NOT all of equal length, and the number of accounts returned can vary.

Thanks

Nat

 

[lbass]

You needed to add the subscript to your split formula. Try:

if ubound(split({table.string},",")) >= 3 then
split({table.string},",")[3]

Create individual formulas, replacing the "3" with numbers from 1 to the maximum number of values.

-LB

 

[synapsevampire]

If those are different accounts within each field, then you need to fire the database architect.

You will need numerous subreports, with each "field" linking to a subreport.

Better yet, parse the data out on the database side using a View or Stored Procedure.

-k

 

[NattyCat]

OK, I'll take a step back and show you how I got to this point. Then perhaps you might know some funky way to solve the problem.

I am reporting from an audit trail table, and the field is called "input". I am trying to create a report which shows customer registrations that have been transferred from one account to another. This information is held in the INPUT field as a long string as follows:

<TRANSFERNOMINATIONS CONTEXTBUSINESSUNITID="2" CONTEXTUSERID="1" CONTEXTLOGINID="8589" CONTEXTUSERNAME="Admin" SERVERCONTEXT="{4365DAC1-E037-4487-82ED-BD4C0A9641AD}"><TRANSFER><TARGETACCOUNT><NUMBER>44000002017</NUMBER><BRANCH><CLEARINGCODE/></BRANCH></TARGETACCOUNT><SOURCEACCOUNT><BRANCH><CLEARINGCODE></CLEARINGCODE></BRANCH><NUMBER>15006760</NUMBER></SOURCEACCOUNT><SOURCEACCOUNT><BRANCH><CLEARINGCODE></CLEARINGCODE></BRANCH><NUMBER>81000000013</NUMBER></SOURCEACCOUNT></TRANSFER></TRANSFERNOMINATIONS>


You can see a little way down the page that there is a TARGET ACCCOUNT (the old account being transferred to) and a SOURCE ACCOUNT (the account being transferred from)

There could be LOTS of source accounts per target account.

I have started by creating the following formula:

{@evaluate source}
Local StringVar AccountNumberList;
Local NumberVar StartChar;
Local NumberVar LastStart;

Local NumberVar AccountStart;
Local NumberVar AccountEnd;
Local StringVar Account;

Local NumberVar NumberStart;
Local NumberVar NumberLen;
Local StringVar Number;

StartChar := 1;
AccountNumberList := "";
AccountStart := 1;

while AccountStart > 0 do
(
AccountStart := InStr(StartChar, {ABAuditTrail_AuditTrailItemTbl.Input}, "<SOURCEACCOUNT>");
AccountEnd := InStr(StartChar, {ABAuditTrail_AuditTrailItemTbl.Input}, "</SOURCEACCOUNT>") + Len("</SOURCEACCOUNT>");

If AccountStart > 0 then
(
Account := Mid({ABAuditTrail_AuditTrailItemTbl.Input}, AccountStart, AccountEnd - AccountStart);

NumberStart := InStr(Account, "<NUMBER>") + Length("<NUMBER>");
NumberLen := InStr(Account, "</NUMBER>") - NumberStart;
Number := Mid(Account, NumberStart, NumberLen);

AccountNumberList := AccountNumberList + Number + ",";
StartChar := AccountEnd;
);
);

//Trim off last comma
If Len(AccountNumberList) > 0 then
Left(AccountNumberList, Len(AccountNumberList) - 1);



This returns the following:

detail line 1 = 10000068
detail line 2 = 15006760,81000000013



What I now need to do, is link in another table which holds the address information for the two accounts shown on detail line 2. The only way I can think of doing this is to split the field at every comma to make several records, then link in a subreport to return the relevant address.

The problem I have is that I have no idea HOW MANY source accounts there will be for each target account, it could be 1 it could be 10....

Any ideas how to do this a different way?

 

[synapsevampire]

As I said, I would do this on the database side, not in Crystal.

The only way I see it working in Crystal is to drop in 10 subreports and link each one by the 10 different variables, suppressing those which do NOT have data.

What should be done is to create a seperate View or SP in SQL Server to generate the data in a single column, allowing for reusability and simplified maintenance.

-k

 

[ouzojd]

Thanks NattyCat, I know its an old post but I'm new to Crystal Syntax and you have saved me with this. Thankyou, I hope you have a nice day.