Can a value list in the current table merge the value lists in a number of related records in a related table with a many to one relationship?
Apologies if there is an obvious answer to this - I'm quite new to FileMaker.
I have a database that records architectural projects that my company has completed over many years. The projects are often phased so the database has a record per master project in a "master" table plus a number of related records in a related table for each phase of the project. The records are related in a many to one relationship between the "master" table and the "phases" table.
In the "phases" table there is a checkbox field with a value list giving various categories of project type, for example Arts Buildings, Education Buildings, Office Buildings, Housing, etc. Each phase can have more than one category so hence the use of a checkbox field.
The master and phase tables are related through a separate ID field.
What I would like to do is set up a similar field in the "master table that for any master project combines the data from the related "phase" value lists into a single value list showing the categories for the whole project. I have tried to set it up as a lookup and to use the List calculation but in both cases they only bring over the data from the first related record and ignore any others.
Any suggestions on how to get this to work would be greatly appreciated?