Formula to display table value

[Pete271]

Hi

I'm on Crystal Reports 8.5 with an Access database.

I have a table that displays fundraiser's names when linked to a project. I currently have a formula to format the way a name displays in the report itself

if "Smith, John" in {TABLE.Fundraiser} then "Smith" else
if "Jones, Mark" in {TABLE.Fundraiser} then "Jones"

and so on for each member of staff. This works fine for when there is only one fundraiser attached to a project, but when there are more it only picks up the first fundraiser listed.

Is there a way that if the value in the table is

"Smith, John; Jones, Mark"

I can have a formula that would display

"Smith Jones"

I'm trying to avoid putting every possible combination of names in the formula as there are myriad options.

Thanks
Peter

 

[Madawc]

Use SPLIT, first by semi-colon to split the names and then by comma to get the surnames.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[IanWaterman]

Provided the max names is 2 and the split is always a ; and , then you can use

At Name

local stringvar array x;
local stringvar y;

x:=split({TABLE.Fundraiser}, ";");

If ubound(x) <> 0 then y:= split(x[1], ",")[1] &", " & split(x[2], ",")[1]
else
y:= split({TABLE.Fundraiser}, ",")[1];


Ian

 

[Pete271]

Many thanks for both replies.

Ian - I put your formula in my report and while I was in the formula editor it did not get and error message but when I ran the report itself I got the following message

"A subscript must be between 1 and the size of the array"

Any ideas?

Peter

 

[IanWaterman]

Sounds like you have some field values which do not have ; or ,

You need to identify records and come up with some alternative logic when it finds them.

Ian

 

[gebigler]

I have CR 8.5 with ODBC to SQL Table. I'd like to display the table name on my reports, but so far I can't find a way to get the table name. Do you know how to do this?

thanks!

 

[lbass]

You should have started a new thread. If you have a record selection formula you could use a formula like this:

extractstring(RecordSelection,"{",".")

If you don't have any selection criteria, add one like this:

{table.field} = {table.field}

This would only work for one table though.

-LB