Filemaker - Getting field values from related table

Question

I have the following setup in Filemaker Pro 10.

Table1 with: id_table1, related_names

Table2 with: id_table2, name, include

and a joint-table with: id_table1, id_table2

Now I want either make related_names a calculated field or write a script that sets related_names to a comma separated list of all names which are connected through the joint-table and have Table2.include = True.

---

So for example a data set could look like:

Table1

id_table1, related_names 1, "foo,bar" 2, "foo" 3, ""

joint-table

id_table1, id_table2

1,1

1,2

1,3

2,1

Table2

id_table2, name, include

1, foo, True

2, bar, True

3, baz, False

After searching the internet for a few hours the closest I came was a calculated field with list(join-table::id_table2) which gives me a list with a all the id_table2's. But now I would need to find the appropriate records in table2 and check the include field.

I hope the problem is clear. any help is highly appreciated.

Answer 1

One of solutions would be to create a calculated field in Table2 that only evaluates to Name if Include is True:

Case( Include, Name )

and then get its contents with List() and replace carriage returns with commas:

Substitute( List( Table2::Name to Include ), "¶", ", " )