6 Replies Latest reply on Aug 12, 2013 12:45 PM by FileMakerNovice

    List() relationship

    FileMakerNovice

      Title

      List() relationship

      Post

           Hello,

           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-----CallType
                          |
                       TypesToInclude

           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)

           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:
           http://forums.filemaker.com/posts/084bbd2594?commentId=252627

        • 1. Re: List() relationship
          philmodjunk

               I see no reason why global storage should be specified for cInclude. Simply the default Unstored storage option should suffice and global calcuation fields update in really funky ways so I would not use a global calculation field for this purpose.

               In fact, I'd just use a global text field formatted with check boxes for this purpose. The value list used the check box can be drawn from the types to include table, but the values you select, would be entered in that global text field and it would serve as the match field in the relationship.

          • 2. Re: List() relationship
            FileMakerNovice

                 Hm... I would have never thought that it would work that way.

                 So you're saying to remove the TypesToInclude table, change cInclude to a new field: TypesToInclude, make it global and create that in the relationship as EmpoyeeID::TypesToInclude=CallType::CallTypes?

                 I created the fields and relationship.  I changed my global check mark field in the layout to be the TypesToInclude value.  It is global.  It looks right but a field in EmployeeIDs called CallCount (which is Sum(Calls)) does not return a value for any Employees.  I also tried to test by creating a portal but still no avail.  

            • 3. Re: List() relationship
              philmodjunk

                   You would still hvae the table as a source of values for the relationship.

                   Note that List and a check box formatted field both produce a return separated list of values. Any one value in the list that matches to the value in the related table's match field is all that is needed to link the two records.

              • 4. Re: List() relationship
                FileMakerNovice

                     Apologies, I am just starting to try to tackle this again today.

                     So... I have a Global field now called CallsToInclude.  This is in my EmployeeID table.  I have a layout that I am trying to utilize a checkmark set.(I am beginning to hate checkmark sets.)  The checkmark set is using the field CallsToInclude and has a value set that populates from the CallType table.

                     The values display properly but the data does not appear to transfer to the universal field.  I have the layout in report view to provide a list after the checkmark set.  In theory, if I check and uncheck items, the report should shorten or lengthen.  Additionally, it should display the CallsToInclude value on each record but this field is never populated.

                     EDIT:  Okay.  The checkmark set is working correctly.  When it'd populated in the header but not in the list.  This may be expected.  Let's say I have a list of 100 people, but only 10 people are in the given checked items.  What is the best way to only display the employees that are present in the report?

                • 5. Re: List() relationship
                  philmodjunk
                       

                            The values display properly but the data does not appear to transfer to the universal field.

                       What do you mean by "universal field"?

                       

                             I have the layout in report view to provide a list after the checkmark set.

                       How? is this a portal? If it is not a portal, changing values in the global field will not automatically update the found set of records listed on the layout.You'd need to use a script to update the found set each time you change what values are selected in the checkbox formatted global field.

                  • 6. Re: List() relationship
                    FileMakerNovice

                         Apologies for the confusion and my terrible use of terms on my part.

                         Universal meant global.

                         So...
                         My layout is in report view.  At the top is the check mark set.(Field is CallsToInclude.)  I added a field that is edit box to show that the checkmark set is adding to the field as expected.  In the report list, I am displaying name, CallsToInclude and a new field called SumCalls.(SumCalls = Sum(CallType::Calls)  SumCalls does not return any value.  All I am getting is a list of employees.

                         Database relationship is:
                    Employees           CallType
                    EmployeeID =      EmployeeID
                    STARTDATE >=   Date
                    ENDDATE    <=    Date
                    cINCLUDE     =     CallType

                         EDIT:  Changed STARTDATE and ENDDATE arguments.  Woops.  STARTDATE<=Date; ENDDATE>=Date.  It now works if I remove cINCLUDE x CallType.  But once added, the results are not populated.

                         EDIT2:  cInclude is now a list or a table CallsToInclude.  It worked one time and then fails to update from the initial update.  =-(
                         EDIT2.5:  Added refresh window to my script and cleared cached data.  Seems to work now.  *Fingers crossed.