In order to get Excel records 1 per row, am I correct in assuming you have to export from a layout that is the lowest in a line of 1 to many relationships: Eg. for A < B < C < D
I'd need to export from D?
You have to export from a table where each exported field has only one value.
For example, table A has three records, each of which have two related records in table B. If you export from the context of table A, and you only export fields from table A, you will get three rows. If you export fields from table B, from either context, you'll get six rows (and IIRC only the table B fields will be populated in each row).
To get only three rows, you'll need to create a calculation/summary field in table A that consolidates the data from the related records in table B into one entry. Then you can export that field along with your other data, and you should only get 3 rows.
I hope this makes sense (and I hope it is correct!)
If you export from D, you will only get data where there is a related value, if that is an issue. Of couse, then you have the same problem, just from the context of D, if you need to show information from any other related table.
You could bring the data up to one level and make it flat, and export the calculation fields.
Thank you both. It was what I thought, but best to check with people who really understand FM !
Just thought of 1 more point on this topic. Alongside my 1 to many A < B < C < D tables I also have an A < C2 table. The C2 table contains daily fields as does table C. However, while C will get filled in daily C2 may not. I'd really like to have some of C2 exported to Excel alongside C. The obvious answer is to move the C2 fields to C. However, this would mean every C record might have quite a few used fields, presumably wasteful.
I assume from the points above, bar saving files programmatically (not yet within my compentence), I'll have to bite the bullet and move the fields?
Retrieving data ...