6 Replies Latest reply on Jan 24, 2017 9:44 AM by philmodjunk

    Keys not matching on table relations

    amerritt

      Hi guys,

       

      I have a series of tables all related through PK/FK dependencies. Pretty standard stuff.

      Here's a screenshot to give you an idea of whats going on.Screen Shot 2017-01-24 at 11.14.35 AM.png

      All of the relationships are =

      Also note that this layout is using the Production Cost Sheet table, and if I do the same thing from the Sales People side of things it looks correct.

      Can you give me any idea why the relationship is breaking between my Join table and Sales People table? You can clearly see that the ID's are not the same, but they should be. Also, this is the only record (that I know of) that's having this issue.

      There IS an entry in the Join table for SALES000886 to Territory_ID::T00374, and when I remove that entry the relationship works correctly. I'd rather not have to remove the entry though, because I want that person associated with that territory, but I'd also not like it to overwrite the correct relationship as it's doing.

        • 1. Re: Keys not matching on table relations
          philmodjunk

          My crystal ball is once again down for repair.

           

          On what table occurrence is this layout based?

           

          What does your actual relationship graph look like?

           

          If you manually edit that field to get a matching value, do your relationships work as expected?

           

          Are all of the fields shown edit boxes? (no value list formatting used here?)

          • 2. Re: Keys not matching on table relations
            amerritt

            This layout is based off the Production Cost Sheet Table.

             

            The actual relationship graph looks identical to what I've created in this layout.

            Sales People::Sales_ID = JoinTerritories::Sales_ID

            JoinTerritories::Territory_ID = Territories::Territory_ID

            Territories::Territory_ID = Production Cost Sheet Territory_ID

             

            Some of the relationships have the "Allow create and deletion" options checked.

             

            I cannot change the Sales People::Sales_ID to the correct value, because it's the PK for the Sales People table. It's unique and auto generated on record creation.

             

            Yes, all the fields are edit boxes.

            • 3. Re: Keys not matching on table relations
              philmodjunk

              Without a screen shot of the relationships graph, it seems that the relationships you describe look something like this:

               

              Sales People----<JoinTerritories>-------Territories-------<Production Cost Sheet

               

              note that ----< means "one to many" in my notation.

              I cannot change he Sales People::Sales_ID to the correct value....

              But I was asking you to edit the JoinTerritories::Sales_ID field, the foreign key, not the primary key.

               

              But what you show in your screen shot appears inconsistent with what you describe as your relationships. Either your relationships are not what you report, or there is something wrong with the design of your layout--such as selecting an ID field from the wrong table occurrence when setting up the layout.

               

              If you add a portal to JoinTerritories to your layout and add the following fields to the portal row, do you still see SalesID values that do not match?

              JoinTerritories::Sales_ID

              Sales People::Sales_ID

              JoinTerritories::Territory_ID

              • 4. Re: Keys not matching on table relations
                amerritt

                You are correct in the relationship. Here's a picture of the graph, sorry for it being messy but I had to pull them all close enough to get a screenshot.

                Screen Shot 2017-01-24 at 12.10.05 PM.png

                When I put a portal to the Join table with the requested fields, here's the result.

                Screen Shot 2017-01-24 at 12.08.30 PM.png

                Here is the Portal with the same fields to the Sales People table.

                Screen Shot 2017-01-24 at 12.16.07 PM.png

                • 5. Re: Keys not matching on table relations
                  amerritt

                  To your point about editing the Join table, the entries are already there.Screen Shot 2017-01-24 at 12.21.36 PM.png

                  • 6. Re: Keys not matching on table relations
                    philmodjunk

                    What I see confirms that your relationships and data are correct. Any apparent "mismatches" of the sales ID are layout design based issues.

                     

                    Take your second portal--which appears to be based on Sales People instead of the Join table.

                     

                    You get the same JoinTerritories::Sales_ID field in every portal row because you have referenced a table occurrence that is on the "wrong side" of your relationships to work the way you expect it to work when put on a layout based on

                    Production Cost Sheet. FileMaker evaluates this from a "starting point" based on the current record of Production Cost Sheet, not a specific record in Sales People. From the current Record in Production Cost Sheet, any reference to JoinTerritories::Sales_ID will refer to the first related table in the join table and thus all show the same ID value. In order for the Sales People to show data from JoinTerritories but from the context of each portal row's record, you'd have to add yet another occurrence of JoinTerritories and put it on the "other" side of this chain of relationships:

                     

                    Production Cost sheet>-----Territories-------<JoinTerritories>------Sales People------<JoinTerritories 2

                     

                    Given those TO's, you'd add fields from JoinTerritories 2 instead of JoinTerritories to your Sales People Portal.