AnsweredAssumed Answered

How can I create a field in one table containing the comma-delimited concatenation of related fields...

Question asked by douglerner_1 on Feb 4, 2013
Latest reply on Feb 20, 2013 by douglerner_1

Title

How can I create a field in one table containing the comma-delimited concatenation of related fields in another table? (FM Pro 10 Advanced)

Post

     The title of the post may not be that clear, so let me give a concrete example if what I'm trying to do.

     I have two tables: Customers and Contacts.

     Customers are usually companies and Contacts are people with names and email addresses. There is a one-to-many relationship from Customers to Contacts via the Customer ID key. And I have a portal on my Customers layout showing all the related Contacts records for the company.

     But I want to be able to export the Customer records into a csv file for import into QuickBooks Online. In QBO one field can contain a comma-delimited list of email addresses to CC to.

     So (please tell me if there is a better way to do this but...) what I want to do is create an extra field in the Customers table called "Contact emails" which contains a comma-delimited list of the email addresses of all the related Contact records.

     I suspect I need to create a new "calculation field" but I'm not quite sure what to do next - what kind of calculation I need.

     Am I going about this the right way? Or maybe there's a way of just creating a new portal with the related email addresses and include that in the export? A portal, if I understand correctly, isn't really part of the table - it's just a view into related records in another table.

     Thanks,

     doug

Outcomes