Storing field data from multiple records (in a global text field)

[sbphoto01]

Hi all, I'm unsure this is the right place to ask this question but here goes...

 

In my solution I have an Events table which is link via a join table to a contacts table as it's a man to many relationship, so I have the Contact Fk and the Events FK in the join table to make the relatonship.

 

Previously I had restricted the contact info to just two contacts per event, so those contact fields (name, Mobile No) were in the events table, this made it easy to concatenate the mobile numbers to place in a 'Send sms' URL string... which worked perfectly...

 

Now I have the contact data in a seperate related table, with individual records for each contact, and I am stumped as to how I can concatenate the mobile numbers to create my send sms url string again.

 

I know how to create a foundset, of the related contacts, but not sure how to collate data (in a global text field for example) from individual fields in multiple records.

 

Can anyone help??

 

All suggestions much apreciated.

 

 

[Wim Decorte]

A number of different ways to do this:

1) use ExecuteSQL to collect the related data and then parse that result.

 

2) Use the List() function to simulate the same thing

 

3) use a portal and do some "portal walking" in a loop to collect the data

[comment]

how I can concatenate the mobile numbers to create my send sms url string again.

 

Any time you are in a layout of Events, the expression =

List ( Contacts::MobileNo )

will return a carriage return-separated list of the related contacts' numbers. You can then use the Substitute() function to replace the delimiter. There is no need to store the result in a field - call it as and when needed (I assume you have a script for this?).

 

 

 

 

 

2) Use the List() function to simulate the same thing

 

LOL, I thought it was the other way around...

[sbphoto01]

Thanks Guys, that was a great help! just what I needed!

[sbphoto01]

Hi guys, I've used the list function to gather mobile numbers from a related contact table foundset, but when I ask that it places them in a text field in another table, it only pastes the first record. Any ideas why this is?