AnsweredAssumed Answered

Getting related values true join table

Question asked by bvanbranden on Jun 10, 2018
Latest reply on Jun 12, 2018 by philmodjunk

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:

 

Books:

  • Books::PrimaryKey
  • 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:

  • 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 :

  • Books::Authors
  • Books::Co-Authors
  • Books::Editors
  • Books::Translators
  • Books::Photographers
  • Books::Artists
  • 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,

etc ...

 

Thanks,
Bruno.

Outcomes