One to Many Excel Export

Questions
1

Say I have two tables, A and B, related in a one-to-many relationship.

A holds demographic info (Names) for each person
B holds transactions for each person.

I want an export that looks like this:

First Name Last Name Date Amount
Alice Able 1/1/2025 $100
2/1/2025 $50
3/1/2025 $75
Bob Baker 1/1/2025 $100
Carl Clarkson 6/1/2025 $99
8/1/2025 $9

I can almost get this if I do a standard XLSX export from FileMaker, by including fields from both tables in the export, and exporting from a found set in Table A:

First Name Last Name B::Date B::Amount
Alice Able 1/1/2025 $100
2/1/2025 $50
3/1/2025 $75
Bob Baker 1/1/2025 $100
Carl Clarkson 6/1/2025 $99
8/1/2025 $9

So close! FileMaker untangles the 1 to Many relationship and includes more than one related record, just like I want.

However, the problem is that all fields from the "B" table get the "B::" prefix (and in reality, the B table name is much longer than just "B", so it's really annoying).

I tried adding calculated fields in the A table which mirror the B table fields, but this doesn't work - you get the first related record only.

I tried adding a List() calculated field in the A table. This almost works, but you end up with multiple values in one Excel cell separated by a carriage return like this:

First Name Last Name DateList AmountList
Alice Able 1/1/2025
2/1/2025
3/1/2025		 $100
$50
$75
Bob Baker 1/1/2025 $100
Carl Clarkson 6/1/2025
8/1/2025		 $99
$9

Again, not what we need.

Any ideas?

2

Post-export processing to strip "B::"

Pre-export processing to generate headers and content in a virtual table.

XML / XSLT output for complete control!

3 Likes
3

Good ideas!

I'm curious if I can do this using the XLSX format without any other tools.

If instead I did the export from Table B, I would get something like this:

A::First Name A::Last Name Date Amount
Alice Able 1/1/2025 $100
Alice Able 2/1/2025 $50
Alice Able 3/1/2025 $75
Bob Baker 1/1/2025 $100
Carl Clarkson 6/1/2025 $99
Carl Clarkson 8/1/2025 $9

I could define calculated fields in Table B which pull the data from Table A, which would fix up my field names:

First Name Last Name Date Amount
Alice Able 1/1/2025 $100
Alice Able 2/1/2025 $50
Alice Able 3/1/2025 $75
Bob Baker 1/1/2025 $100
Carl Clarkson 6/1/2025 $99
Carl Clarkson 8/1/2025 $9

but then I still have the name repeating on multiple lines, which I don't want.

4

Iā€™d do this in a virtual table based on data stored in json format. You can get the data into any shape that you like and then do an XSLX export.

2 Likes