10 Replies Latest reply on Apr 9, 2014 4:41 PM by Mike_Mitchell

    Multi-Key Relationship

    tm9

      I am having trouble with a multi-key relationship. I have 4 tables.

       

      1. Person

      2. PersonOrganization

      3. Organization

      4. Requirements

       

      The Person & Organization tables are a many-to-many relationship.

       

      I would like to have a field for each person record that also stores the KP's of the organizations they are apart of to be used as a Multi-Key to related each person to the respective organizations requirements.

       

      I have a calculation field in the Person table " List ( person_personorganization_ORGANIZATION::__kp_Organization ) " It works to return a list in the field (I think with carriage returns) of all the organization KPs the person is a part of.

       

      However..... The person records do not show up in a portal when viewing a layout with a Requirements table. BUT.. When I change the field from a calculation to a normal field and manual enter in multiple KP's pressing enter each time the relationship works and the portal shows the persons...

       

      Why is this??? Why doesn't the list calculation work?

        • 1. Re: Multi-Key Relationship
          Stephen Huston

          Calculated lists are generally unstored calcs so they will update as the data changes. UNstored (unindexed) fields are not reliable as Keys in relationships.

           

          It sounds like you need a join table which records each combination of Organizaiton Key and Person Key in its own record, and serves as the link between Persons and Organizations. The fields used as Keys for relationships in all of these tables need to be stored (indexed) data.

          • 2. Re: Multi-Key Relationship
            Mike_Mitchell

            Stephen is correct. The standard practice for a many-to-many relationship is to insert a join table (which effectively converts it to a pair of one-to-many relationships).

             

            If you do want a multi-key to work, it'll need to be an indexable text field, updated via scripting. This can be done, but it's pretty much up to you to manage.

             

            Mike

            1 of 1 people found this helpful
            • 3. Re: Multi-Key Relationship
              tm9

              Thanks Stephen & Mike!

               

              How would I go about an efficient way to update it using scripting?  I have utilized a few many-to-many relationships but I would like to use an indexable text field for secuirty access to indvidual person records.  I would like to have a table of Filemaker Accounts and a text field for each account record that has the KPs of the person records that account has access to view/edit.  Then connecting this via a simple relationship I could utilize the filemaker security settings?

               

              Thanks

              • 4. Re: Multi-Key Relationship
                Mike_Mitchell

                We've both already given you the best answer: Create a join table between each primary entity table that contains the keys of the two parent tables, like this:

                 

                Account ID   >----- Account ID

                                               Person ID ----------<  Person ID

                 

                Then you can create a value list that will give you the list of person IDs associated with the current account. That value list can be plugged into a calculation in the Security dialog to create your restriction.

                 

                In order to do a multi-key, you'll have to insert the person ID into the multi-key field in the Accounts table, while simultaneously verifying that you don't have any duplicates. You can do this via a script something like this:

                 

                Set Variable [ $personID ; Person::Person ID ]

                Go to Layout [ Account ]

                Perform Find [ {desired account record} ]

                Set Field [ Account Key ; "<cr>" & Account Key ]

                // To remove the key

                Set Field [ Account Key ; Substitute ( Account Key ; "<cr>" & $personID ; "" )

                // To add the key

                Set Field [ Account Key ; Account Key & "<cr>" & $personID ]

                Set Field [ Account Key ; Substitute ( Account Key ; "<cr><cr>" ; "<cr>" ]

                 

                In this case, "<cr>" is the pilcrow (carriage return) character. Whether you're removing or adding the key, you'll want use the script step to remove it, because you don't want any duplicates. Only use the "add" step if you want to add that person to the key.

                 

                This is really not the recommended practice. A join table is the accepted best practice for a many-to-many relationship. However, if you really want to do a multi-key, you can do this.

                 

                Mike

                1 of 1 people found this helpful
                • 5. Re: Multi-Key Relationship
                  tm9

                  Thanks Mike! I have no desire to do something that isn't standard.  I am new to implementing security measures using calculations. I have clearly done them incorrectly which led me to the multi-key solution...

                   

                  Thanks for the value list suggestion. I will try that first and hoefully I will get it to work!

                  • 6. Re: Multi-Key Relationship
                    flybynight

                    Looking at tm9's original post, it looks to me like he does have a join table in place.

                    From tm9's original post:

                     

                    I am having trouble with a multi-key relationship.  I have 4 tables.

                     

                    1. Person

                    2. PersonOrganization

                    3. Organization

                    4. Requirements

                     

                    ...

                     

                    I have a calculation field in the Person table " List ( person_personorganization_ORGANIZATION::__kp_Organization ) " ...

                     

                     

                    At least, I would assume that is what PersonOrganization is. When I look at that calc field referencing a table named "person_personorganization_ORGANIZATION" that makes me think even more that PersonOrganization is a join table.

                     

                    Sounds like the issue isn't that he doesn't have a join table… but rather that his calculation is unstored (unindexed)?

                     

                    Maybe tm9 can chime in to confirm or deny this. A screen shot of your relationship graph might clarify things.

                    I just hate to see a thread go on and on, based on a misunderstanding of the original issue.

                     

                    HTH,

                    -Shawn

                    • 7. Re: Multi-Key Relationship
                      tm9

                      Could you please provide some more explanation to your values list solution and what the calculation might look like?

                      • 8. Re: Multi-Key Relationship
                        Mike_Mitchell

                        You'll need to build a value list based on the relationship between the parent table and the join table. For example, you want a list of all personID values in AccountPerson (the join table) based on the relationship between Account and AccountPerson. This value list will change (obviously) as you scroll through the Account records. Then, you build a calculation like this:

                         

                        not IsEmpty ( FilterValues ( ValueListItems ( Get ( FileName ) ; "{value list name}" ) ; {personID you want to check} )

                         

                        If that calculation is true, it means the person ID you want to check is in the value list - meaning it's in the join table. I don't know how you're trying to establish what person ID to check, but that would be how to do it.

                         

                        Mike

                        • 9. Re: Multi-Key Relationship
                          tm9

                          How would it know to pull the correct values list for the current account logged in?

                           

                          This is what I currently have set in the calculation.

                           

                          If ( KP_Person = Account::zg_KF_PERSON; 1 ) or not IsEmpty ( FilterValues ( ValueListItems ( Get ( FileName ) ; "New Value List" ) ; KP_Person ))

                           

                          Where the global account field is set as the record for the person associated with the account by a script when logging in.  For example, if an account was created for you to access the database, you would be able to view your own person record in addition to those on the values list. 

                           

                          The first part appears to be working when I view a layout with a persons record I can see your record.  However I cannot see other records from the relationship. They are showing up in the values list though...

                          • 10. Re: Multi-Key Relationship
                            Mike_Mitchell

                            I wasn’t aware you were using a global field for the current person. (When posting a question, it’s best to give the complete setup.) Set a relationship between the global field and the person key in the join table. Use that for the value list.

                             

                            Your calculation would then just read:

                             

                            not IsEmpty ( FilterValues ( ValueListItems ( Get ( FileName ) ; "New Value List" ) ; KP_Person ))