1 Reply Latest reply on Sep 8, 2011 1:05 PM by philmodjunk

    Lookup question

      Title

      Lookup question

      Post

      I admit my ignorance as I am new to Filemaker, so bear with me.  I am creating a Filemaker database from a Salesforce database that my client wants to stop using.  I have made lot of progress but now have some questions.

      I have table ("Case") that is related to another table ("Individuals") via another table ("Character Role").

      This was setup because one person may be related to several cases and be in different roles (e.g., defense counsel in one case and plaintiff in another).  I am trying to create a calculated field in the main "case" table that looks up the name of a person that has a certain role in the case.  As an example, if "Jane Doe" is the individual and "Mother" is her role, I want to have another field that lists her name.

      The client wants a "case name" field that is a combination of several fields. I know how to do that with non-related fields, but this is stumping me.  Any help would be appreciated!

        • 1. Re: Lookup question
          philmodjunk

          Case----<CharacterRole>----Individuals

          Case::CaseID = CharacterRole::CaseID
          Individuals::InvidudalID = CharacterRole::CaseID

          A relationship can be added to link a case record to a specific role record by caseID and Role, but the resulting calculation will be unstored. Sorts and finds on such a field will be much slower than on a stored calculation. You may want to use a script to find and capture this data to put in a simple, indexed text field for you case name in order to get better performance.

          That said, here's what you requested:

          Define a calculation field in Case, constMother, and define it to return the text "Mother".

          Make an extra occurrence of characterRole, MotherRole, and link it in a relationship like this:

          Case::CaseID = MotherRole::CaseID AND
          Case::constMother = MotherRole::Role

          Make an extra occurrence of Individuals, MthIndividuals and link it in as:

          MotherRole::IndividualID = MthIndividuals::IndividualID.

          Now your calculation, defined in Case, can refer to a name field in MthIndividuals to return the name of the Mother in this case.