10 Replies Latest reply on Feb 20, 2013 4:37 AM by douglerner_1

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

    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