AnsweredAssumed Answered

Lookup of related values trough join table

Question asked by bvanbranden on Jun 11, 2018
Latest reply on Jun 11, 2018 by bvanbranden

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