1 2 Previous Next 17 Replies Latest reply on Jan 17, 2013 4:58 PM by Bubba

    Related Records Problem: Any Suggestions?

    Bubba

      Title

      Related Records Problem: Any Suggestions?

      Post

            

           A scheduling application in FMP 12 Adv has three main tables, Contacts, Events, and Rosters. The data entry layout for Events allows entry of event date, type, time, location and so forth for each event. A portal on this layout allows entry of multiple contacts, assignments and so forth for each event using drop down menus from Contacts. Each portal record generates (or deletes) a related record in the Roster table that contains all info for a specific event for one contact. Thus one event may generate dozens or hundreds of records in the Roster table.

            

           A fourth related table, Absences, contains one record (with contact name and absence date) for every date that a contact will be unavailable. If a contact will be unavailable for 7 days vacation, this table will contain seven records, one for each day of the vacation. Other contacts in this table may or may not be absent on any or all of the dates of the contact with seven days vacation.

            

           I want to identify/flag absences in the Events data entry portal so that the scheduler can avoid them and have provided a field in this portal from the related Roster table record. For a contact to show up as absent in the Roster record, at least one record must be found in the Absences table that matches the contact name and the event date from the Roster record.

            

           The Roster record contains a portal showing the related records from the Absences table (if any) and correctly lists the names of all contacts unavailable on the specific event date, including the contact that is the specific subject of this Roster record.  I need to get this specific name from these related records and flag it in the Roster record and the Events data entry portal.

            

           It seemed to me that I needed a script activated from the Events data entry portal to go through the Absences table related records to find a match between both contact and event date, but this has been unsuccessful so far through many permutations. Perhaps I am missing a key table relationship.

            

           Before spending more time on scripts I wanted to check into the forum to see whether folks have been down this trail and can suggest more straightforward or better ways to solve this problem.

            

           Thanks for all suggestions.  smiley

            

           Bubba

            

        • 1. Re: Related Records Problem: Any Suggestions?
          philmodjunk

               Thank you for the detailed description. But it needs just a few more details. While my field names will differ from yours, are these the relationships that you have?

               Absences>-----Contacts-----<Roster>------Events

               Contacts::__pkContactID = Absences::_fkContactID
               Contacts::__pkContactID = Roster::_fkContactID
               Events::__pkEventID = Roster::_fkEventID

               If my notation is not familiar, see the first post of: Common Forum Relationship and Field Notations Explained

               Are events all one day events with a date field to record the event date?

               Or are some multiple day events? If so, how do you record those multiple dates for one event?

               I'm a bit puzzled by what you want to see in the portal to Roster. You want to add a contact to the Roster even if a record in Absences shows that they will be absent for at least one day of that event? Would it not make more sense to omit contacts from the value list of available contacts if they are going to be absent?

          • 2. Re: Related Records Problem: Any Suggestions?
            Bubba

                  

                  

                 PhilModJunk:

                  

                 Thank you for responding. The relationships you requested are below.

                  

                 On your questions:

                  

                 All events are "one day" events and there is a date field with drop down calendar to record the event date, however there can be multiple events at different times and different places on each day.  So there is a field to record the time as well as the date and location. But there is one unique event record per event.

                  

                 Yes, omitting unavailable contacts from the value list makes much more sense and is exactly the kind of insight I hoped to get from my post. This was an add-on and not in the original plan. The drop down contacts menu uses a value list with ContactID and a second (display) field that ligates the name and function of the contact. Can you help me with this approach?

                  

                  

                 1  Contacts(People)-----<Affiliations(Rosters)>-----Organizations(Events)

                  

                 Contacts::__pKNameID = Affiliations::__pKContactID

                 Organizations::__pKServiceID = Affiliations::__pKOrganizationID

                  

                 2  Contacts(People)-----Organizations2(Events)

                  

                 Contacts::__pKNameID = Organizations2::__pKServiceID

                  

                 3  Affiliations(Rosters)>-----Organizations(Events)>-----<Unavailable_dates(Absences)

                  

                 Affiliations::__pKOrganizationID = Organizations::__pKServiceID

                 Organizations::_fkService_date = Unavailable_dates::_fkDateunavailable

                  

                 4  Affiliations(Rosters)>-----<Unavailable_dates2

                  

                 Affiliations::__pKContactID = Unavailable_dates2::_fkDateunavailable

                  

            • 3. Re: Related Records Problem: Any Suggestions?
              philmodjunk

                   This makes no sense to me:

                   

                        2  Contacts(People)-----Organizations2(Events)

                   

                         

                   

                        Contacts::__pKNameID = Organizations2::__pKServiceID

                   You can't use the primary key (__pk) field for both tables in the same relationship. Which is the primary key and which the foreign? Primary keys uniquely identify the records in the table where they are defined and are most often defined as auto-entered serial numbers. Foreignkeys are fields that store a value that is not necessarily unique and that matches the value of a field in a related table. Thus relationships are usually __pk to _fk or _fk to _fk, not __pk to __pk.

                    

                   Since you already have a many to many relationship between contacts and organizations. I don't see how any direct relationship (Neither one to many nor many to one) will work for you. How do you use this relationship?

              • 4. Re: Related Records Problem: Any Suggestions?
                Bubba

                     Oops. This relationship shouldn't be there. It was left over from troubleshooting efforts.

                      

                • 5. Re: Related Records Problem: Any Suggestions?
                  philmodjunk

                       That reduces things to these relationships:

                       Contacts(People)-----<Affiliations(Rosters)>-----Organizations(Events)>-----<Unavailable_dates(Absences)
                                                       Affiliations(Rosters)>-----<Unavailable_dates2

                       The match fields for this last relationship were given as:

                       Affiliations::__pKContactID = Unavailable_dates2::_fkDateunavailable

                       Shouldn't that be:

                       Affiliations::_fKContactID = Unavailable_dates2::_fkContactID

                       ?

                       and _fkDateUnavailable is an ID number?

                       This last relationship works for logging for which dates an individual is unavailable, but I'd use a relationship between contacts and Unavailable dates for that purpose.

                       This relationship can give us a list of contactIDs for those who are NOT available:

                       Organizations(Events)>-----<Unavailable_dates(Absences)

                       We can define a calculation field in Organizations, cUnavailableIDs as:

                       -1 & ¶ & List ( Unavailable_dates::_fkContactID )

                       we can use this as an "exclusion list" to get a list of all available contacts:

                       Organizations-------<AvailableCONTACTS

                       Organizations::cUnavailableIDs ≠ AvailableCONTACTS::__pkContactID

                       which allows you to create a conditional value list set up with the "use values from field" option to list __pkContactID from AvailableCONTACTS for field 1 (Primary) and a contact name field from the same table occurrence as the secondary field. Then select "Include only related values, starting from Organizations".

                       You can use this value list to assign contacts to a given event by formatting the Affiliations::_fkContactID field to use this value list.

                  • 6. Re: Related Records Problem: Any Suggestions?
                    Bubba

                          

                         Phil:

                          

                         I'm still having trouble with the relationships.

                          

                         To make communication easier I have changed the field names NameID to ContactID and ServiceID to OrganizationID to be consistent with the table names.

                          

                         With these name changes the relationships are currently as follows, numbers corresponding to my earlier posts:

                          

                          

                         1  Contacts(People)-----<Affiliations(Rosters)>-----Organizations(Events)

                          

                         Contacts::__pKContactID = Affiliations::__pKContactID

                         Organizations::__pKOrganizationsID = Affiliations::__pKOrganizationID

                          

                         2  (Deleted)

                          

                          

                         3  Affiliations(Rosters)>-----Organizations(Events)>-----<Unavailable_dates(Absences)

                          

                         Affiliations::__pKOrganizationsID = Organizations::__pKOrganizationID

                         Organizations::_fkService_date = Unavailable_dates::_fkDateunavailable

                          

                         4  (Revised) Affiliations(Rosters)>-----<Unavailable_dates2(Absences)

                          

                         Affiliations::__fkContactID = Unavailable_dates2::_fkContactID

                          

                         5  (New)  Organizations(Events)>-----<Contacts(People) 

                          

                         Organizations2::cUnavailableIDs ≠ Contacts::__pKContactID

                          

                         Is Relationship 5 what you meant for finding the available contacts?

                          

                         I have defined the calculation field in Organizations as you suggested.

                          

                         Starting from the portal to Affiliations on the data entry layout for Organizations, when I use the conditional value list from Contacts including only related values from Organizations, the drop down list shows only the names of contacts already assigned to the event (by previously setting the VL to Include All Values), or a blank if no contacts were assigned previously. 

                          

                    • 7. Re: Related Records Problem: Any Suggestions?
                      philmodjunk

                           I don't think you understand what the __pk designation is supposed to identify. It stands for "Primary key". Primary key fields in FileMaker are most often defined as auto-entered serial numbers. Sometimes in FileMaker 12 they are text fields with the auto-enter calculation Get ( UUID ). Either way:

                           Contacts::__pKContactID = Affiliations::__pKContactID

                           Is confusing notation because Affiliations::__pkContactID is NOT a primary key field in Affiliations. It is  FOREIGN key and by this naming convention should be labeled: Affiliations::_fkContactID--which tells the reader that this field matchs to the ContactID primary key in Contacts.

                           I will assume that Affiliations::__pkContactID is really a simple number field matching to an auto-entere serial number field defined in contacts and likewise for your other examples. If this is not the case, let me know.

                           Organizations2::cUnavailableIDs ≠ Contacts::__pKContactID

                           Isn't quite the relationship that you need. The match fields are correct, but the table occurrences are incorrect. The duplicated table occurrence has to be an occurrence of contacts, not Organizations2.

                            

                           To produce this series of linked table occurrences:

                           Contacts(People)-----<Affiliations(Rosters)>-----Organizations(Events)-------<AvailableCONTACTS

                           And make sure that your value list lists values from AvailableCONTACTS, not Contacts. Both refer to the same table, but you must refer to availableCONTACTS in order for your "starting from" designation to identify the correct relationship.

                            

                      • 8. Re: Related Records Problem: Any Suggestions?
                        Bubba

                              

                             For clarification ContactID, AffiliationID, OrganizationID, and DateunavailableID are all auto-entered serial numbers.

                              

                             You are right that I incorrectly identified the keys and have made the corrections shown below in the list of current relationships:

                              

                              

                             1  Contacts(People)-----<Affiliations(Rosters)>-----Organizations(Events)

                              

                             Contacts::__pKContactID = Affiliations::__pKContactID

                              

                        should be    Contacts::__pKContactID = Affiliations::_fkContactID 

                              

                             Organizations::__pKOrganizationID = Affiliations::__pKOrganizationID

                              

                        should be   Organizations::__pKOrganizationID = Affiliations::_fkOrganizationID

                              

                             2  (Deleted)

                              

                              

                             3  Organizations(Events)>-----<Unavailable_dates(Absences)

                              

                             Organizations::_fkService_date = Unavailable_dates::_fkDateunavailable

                              

                             4  Affiliations(Rosters)>-----<Unavailable_dates2(Absences)

                              

                             Affiliations::__fkContactID = Unavailable_dates2::_fkContactID

                              

                             5  (Revised)  Organizations(Events)>-----<Contacts(People) 

                              

                             Organizations::_fkcUnavailableIDs ≠ Contacts2::__pKContactID

                              

                             I am assuming your notation "AvailableCONTACTS" to mean occurrences in Contacts2 based on the above relationship. So the VL for selecting AvailableCONTACTS should refer to Contacts2. Correct?

                              

                             On the other hand the VL when used for other purposes especially selecting contacts to place in the unavailable table should refer to Contacts, not Contacts2. Correct?

                              

                             When proceeding as above the VL from Contacts2 now shows all contacts and does not exclude the unavailable contacts.  So I don't yet have the relationships right.

                              

                             Many thanks for sticking with me on this.

                              

                              

                        • 9. Re: Related Records Problem: Any Suggestions?
                          philmodjunk

                               I am assuming your notation "AvailableCONTACTS" to mean occurrences in Contacts2 based on the above relationship. So the VL for selecting AvailableCONTACTS should refer to Contacts2. Correct?

                               One picky detail: "AvailableCONTACTS" is an occurrence of Contacts. If Contacts2 is also an occurrence of Contacts, then we are on the same page but with different names. (You can double click occurrence boxes in Manage | database | relationships to open a dialog where you can change the name.)

                               Details to check:

                               Put _fkcUnavailableIDs on a layout and either  make it many rows of data tall or click/tab into it to see all the data. make sure that it is displaying the correct list of unavailable ID's. Make sure that the return type for this field is set to "text". Also clear the "do not evaluate if all referenced fields are empty" check box.

                               Make sure that the date fields used in these relationships are of type date not text.

                               And in the value list, make sure that you select values from contacts2 and select Organizations as the "starting from" table occurrence. (Select the wrong table occurrence in either of these locations and you get a different value list.)

                          • 10. Re: Related Records Problem: Any Suggestions?
                            Bubba

                                  

                                 One picky detail: "AvailableCONTACTS" is an occurrence of Contacts. If Contacts2 is also an occurrence of Contacts, then we are on the same page but with different names. (You can double click occurrence boxes in Manage | database | relationships to open a dialog where you can change the name.)

                                 Response: Yes this is correct. I changed the name Contacts2  to availableContacts2.

                                 Details to check:

                                 Put _fkcUnavailableIDs on a layout and either  make it many rows of data tall or click/tab into it to see all the data. make sure that it is displaying the correct list of unavailable ID's. 

                                 Response: On the layout based on Organizations It shows:

                                  -1

                                 It is not getting the correct list of unavailable IDs.

                                 The VL drop down shows all ContactIDs as available.

                                 If I delete all the records in Unavailable_Dates or add additional records it still shows -1 and the VL shows all contacts.  Unavailable_Dates::ContactID shows the correct contactID that is being entered as unavailable. 

                                 Responses on your other items:

                                 Make sure that the return type for this field is set to "text".

                                 Yes. 

                                 Also clear the "do not evaluate if all referenced fields are empty" check box.

                                 Yes.

                                 Make sure that the date fields used in these relationships are of type date not text.

                                 Yes.

                                 And in the value list, make sure that you select values from contacts2 and select Organizations as the "starting from" table occurrence. (Select the wrong table occurrence in either of these locations and you get a different value list.)

                                 Yes. Set correctly.

                                  
                            • 11. Re: Related Records Problem: Any Suggestions?
                              philmodjunk

                                   In order for the relationship to work, there has to be at least one value returned by _fkcUnavailableIDs in order for it to work correctly. That's the purpose of the -1 a value that should not match to any IDs in your tables and thus the Not Equals operator is matching to all contacts.

                                   We need to figure out why no IDs are being listed in that field.

                                   Can you open the specify dialog box for this field and post a screen shot of it here? I'm particularly interested in what name is selected in the "context" drop down at the top of the dialog box.

                              • 12. Re: Related Records Problem: Any Suggestions?
                                Bubba

                                      

                                     Additional troubleshooting info:

                                      

                                     After carefully checking all the relationships, fields, VLs and so forth cUnavailableIDs is showing:

                                     -1

                                     1

                                     2

                                     3

                                     4

                                     5

                                     6

                                      

                                     and the VL is showing the first 6 Contacts as unavailable ( ContactIDs 1 through 6 missing from the VL).  However none of these 6 ContactIDs appear in the Unavailable_Dates table (there are 7 ContactIDs absent on the Organization date: 10, 48, 106, 41, 25, 21, and 5).

                                      

                                     If I delete all the records from the Unavailable_Dates table (or add more records) the above values for cUnavailableIDs do not change and the VL still omits the first 6 ContactIDs.

                                • 13. Re: Related Records Problem: Any Suggestions?
                                  Bubba

                                       Posted the last note before seeing your last one.  Here is the screen shot.  The context is Current Table: Organizations.

                                  • 14. Re: Related Records Problem: Any Suggestions?
                                    philmodjunk

                                         I mistyped. I wanted the Specify Calculation Dialog Box, not Specify FIeld.

                                         And is this a field of type calcuation or a text field with an auto-entered calcualtion? The failure of this field to update suggests that you set up an auto-entered calcualtion and we need an unstored calculation field here instead. (The field will automatically be unstored due to referencing data in a related table.)

                                    1 2 Previous Next