AnsweredAssumed Answered

List() relationship

Question asked by FileMakerNovice on Aug 7, 2013
Latest reply on Aug 12, 2013 by FileMakerNovice


List() relationship



     I am currently looking to relate two tables through a List.  Let me explain:

     For this part of my database, I have three Tables:


     Employees:  A list of employees: names, employeeID, email, STARTDATE, ENDDATE etc (John Doe; 12345;; 7/1; 7/30)
     CallType:  A list of types of calls taken throughout the day being routed different ways:  EmployeeID, callType, Date. (12345; Route1; 1/1/1)
     TypesToInclude:  A list of routing methods I wish to include. (Route1, Route2, Route3, etc)

     Employees           CallType
     EmployeeID =      EmployeeID
     STARTDATE >=   Date
     ENDDATE    <=    Date
     cINCLUDE     x     CallType

     Employees        TypesToInclude
     cINCLUDE   =    CallType

     cINCLUDE is a global variable of type calculation =  List(TypesToInclude::CallType) &¶ & "none".  It is calculated as Text.

     I have a Layout that I enter STARTDATE and ENDDATE.  I then select which CallTypes I'd like to include, this is via a check box and a script populates my TypesToInclude table.  This all works without issue.  The problem seems to stem from the list relationship.  

     My ultimate goal is to enter start/end dates and the calltypes I want and then from the resulting data extract additional data from the CallType table.  This table has calls from each calltype, durations of calls, etc.

     I initially attempted to do this with summary fields and performing a find but could not figure out how to create a ExecuteSQL to properly count employees.  The new method I described above seems more robust to me but if there is a way to do this better through ExecuteSQL, I'd go that way.  Previous forum for that: