Concatenating related records into a field

[kiwiora]

This is a field question, but relates to portals as well.. so bare with me!!

The part of the database I am trying to work on has 4 tables.

Company

Contact

Event

EventContact

I'll try to explain what I'm tryign to obtain.

I need to generate a report of all contacts, and if they have either been invited, or have attened an event. I can't produce the report from the Events/Contact table (which would be easy) because it will only display only those contacts which have been invited/attended an event.

On the contact record I have a portal which displays the events the contact has been invitd to and their current status (i.e attended, declined, yet to RSVP).

What I think I need to do, is create the report from the contact table so as to contain ALL names and then in a field concatenate the related records in the events/contact table into one field listing all the events they've been invited to (and the status).

The guy wants it looking like an excel spreadsheet so in essence how it appears is:

Company; contact; event1, event2 event 3

(aka the event details are all on the one row). I've tried doing it from the contacts table as is but naturally only one event will display.

Hope that makes sense.

Cheers

[Fenton]

First create a value list, based on a relationship from Contacts to EventContact (start at Contact), named "ContactEvents".

Then an unstored calculation field, in Contacts:

Substitute ( ValueListItems (Get(FileName), "ContactEvents");

[kiwiora]

Ok I must be doing something wrong because I am only getting one item (i.e in a portal where the dude has 4 records of events, only the first one appears).

Any ideas?

Thanks in advance.