Join type formula

[ThomasBrown]

Hello Folks,

I am working in the Event Industry and am having a problem with one of the reports.

This is what is happening:

Start Date End Date Event Name Venue
1 June 2004 5 June 2004 Great Event Hall 1
1 June 2004 5 June 2004 Great Event Hall 2
1 June 2004 5 June 2004 Great Event Hall 3
3 June 2004 4 June 2004 Next Event Hall 1

What I want it to look like is:

1 June 2004 5 June 2004 Great Event Hall 1, Hall 2, Hall 3
3 June 2004 4 June 2004 Next Event Hall 1

I have tried using the join () but it says "A String Array is required Here" I will not set this as a Parameter field, and in the database it entered i seperate lines.

Many Thanks
Thomas

 

[lbass]

Group on an event ID, preferably, and then create three formulas:

//{@reset} to be placed in the group header:
whileprintingrecords;
stringvar venues := "";

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar venues := venues + {table.venue} + ", ";

//{@display} to be placed in the group footer:
whileprintingrecords;
stringvar venues;
left(venues, len(venues)-2)

Drag the other fields into the group footer and then suppress the group header and the details section.

-LB

 

[ThomasBrown]

Hi lbass,

Thanks a lot this works perfectly, just one more thing, if a venue is used twice in the same event, is there a way of only showing that venue once? ie Suppressing duplicates

Thomas

 

[Madawc]

Right-click on the detail section and choose Format Section. Then choose the formula icon (x+2 and a pencil) for suppression.
Test Venu= Previous(Venu).

Madawc Williams (East Anglia, UK)

 

[ThomasBrown]

Madawc,

Nope that doesn't seem to be working, i tried:

Venues= Previous(Venues)

I also tried using:

If Venues= Previous(Venues) then True
as well as
table.venue=Previous {table.venue}

The last one worked best but suppress too much of the venues. ie. If a venue was duplicated it left if off totally.

Thomas

 

[Madawc]

If you could get if table.venue,.Previous {table.venue}into lbass's accumulation forumula, that should work.

Madawc Williams (East Anglia, UK)

 

[lbass]

Change {@accum} to:

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar venues;

if instr(venues,{table.venue}) = 0 then
venues := venues + {table.venue} + ", ";

-LB

 

[ThomasBrown]

lbass

once again .. thank you, thats exactly what I'm looking for.