I am having some trouble exporting the exact data I need from two tables (a one to many relationship). The main table has a record for each set number, and the related table has a record for each individual item number with a field that says what set number it is in and is related back to the main table. So it looks like this:

Main table:

SetID# (unique)

SetTitle

Related Table:

Item# (unique)

Set# (many, related to SetID#)

What I want to do is export (to an excel file, ultimately) The SetID#, SetTitle, and ALL the Item#s related to each set, with each Item# being in a separate field. But I can only get it to export the first related Item#, not all of them.

Is there some exporting strategy I am overlooking? Or maybe a way to set up a calculation that will take all the related Item#s and put them in a repeating field? (I already have a database structure that will use a calculation to take a repeating field and break it into individal fields, so I can use that tactic if I can get the data into a repeating field, but the manual says it will only lookup the first matching record.)

It is really frustrating because I have a portal in my main layout that lists all the item#s in that set, so I know there just has to be a way to do it. Maybe I could use a script?

Any help you can offer would be greatly appreciated. Thanks :)

No, don't use repeating fields. Yes, use a script. With the Go to Related Record (GTRR) script step. It'd look something like:

If[ not IsEmpty( Items::Item# ) ]

Go to Related Record [ current record ; Items layout ]

Export Records [ restore ]

Go to Layout [ original layout ]

End If

The IF conditional checks whether there are any related Items records, and only does the GTRR and Export if there are.