Hi FileMaker guru's, I would love to get your help.
I have a book database with a few hundred thousand books in it. As every book can have multiple authors, editors, photographers and whatever else, I've created a joined table to prevent a many to many relationship.
But now I need to export those books in a flat file for use in a webshop.
So this is my situation currently:
- a lot of other stuff
Book Contributors: (join table)
- Book Contributors::PrimaryKey
- Book Contributors::ID_Books (foreign key related to Books)
- Book Contributors::ID_Contributors (foreign key related to Contributors)
- Book Contributors::Role (can have values like Author, Co-Author, Editor, Translator, Photographer, Artist, etc)
- Contributors::Primary Key
- Contributors::First Name
- Contributors::Last Name
- Contributors::Full Name
So, for exporting reasons to one flat file, I need to have some fields for every contributor function. So I need a field :
- etc ...
So my idea was to create a script (or else a calculated field) for each of those values like this:
If ( Book Contributors::Role = "Translator" ; Contributors::Full Name )
BUT: this only checks the first related record, not the many possible others.
On the other hand, listing all related records with:
List (Contributors::Full Name)
gives me a full list of all contributors.
So how can I combine this, and get a list of Authors in the Author field, a list of Editors in the Editor field, etc ?
Realy no idea how to do this and very new to Filemaker, so some actual code would be great :-)
I should end up with
Books::Authors = list of all authors
Books::Translators = list of all translators,