How can I create a field in one table containing the comma-delimited concatenation of related fields in another table? (FM Pro 10 Advanced)
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.