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.

3 Likes
5

Create a new layout with the context of the child table. Then it shows all child records. You can sort on the client, then hide all client names after the first one, using a GetNthRecord construct.

1 Like
6

Maybe have a look at our fx-reports (GitHub, Docs).

It uses the MBS plugin an LibXL to generate any kind of Excel document. In your case you would need a simple template and you would then provide your data as a json array of arrays. You could also just replace the column header on an existing xlsx.

Maybe a litte bit overkill for the job, but check the examples on GitHub if you like.

Nils

1 Like