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; JohnDoe@a.com; 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)
EmployeeID = EmployeeID
STARTDATE >= Date
ENDDATE <= Date
cINCLUDE x CallType
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: