10 Replies Latest reply on Feb 2, 2017 4:32 AM by muhammad.ikram@contedia.com

    Foreign key do not work

    muhammad.ikram@contedia.com

      Greetings.

       

      My application has a concept of customer, orders and credentials. When order arrive; we may attach any credentials to those them like username and password.

       

      I have an portal which shows all orders. What I want is when user click on that "Pencil button", FileMaker take user to "Credential Tab". On credential tab I have set of field which user may fill like username and password and save the credentials.

       

       

       

       

      The problem is the credentials Tab saves the wrong order foreign key in credential table. For example If i clicked order number "17626" and fill all the credential fields, in the credential table -> OrderID_ _ fk, it will have the order ID of "17624". I think it saves the order ID if first row from portal.

       

      Here is my relationship graph.

       

      What I want is FileMaker to store the correct OrderID against the credential so we know which credential is for which order.

       

      Hope this makes sense.

       

      Many Thanks.

        • 1. Re: Foreign key do not work
          wimdecorte

          We don't see enough of your setup to answer fully.  Sounds like the 'credentials' tab shows a portal whose relationship allows for creation of records?

           

           

           

          You are probably struggling with the concept of context.  If the assets tab shows a portal and you click on a portal row there, that does not put you in the right context for the relationship to credentials based on the clicked order.

          The portal on the credentials tab will show children (and create children) for the record you happen to be on for the layout that shows those two tabs.  By clicking on an order you are not changing context to that order.  You remain on whatever record you are on and that record is the parent for all related records you show in the portals.

           

          In my opinion: don't use portals for these kinds of record selection.  Use a list of orders so that when you click on one you are actually on that order.  Use a pop-over or simply go to another layout to enter the credentials.

           

          You can make it work by keeping your current layouts as long as:

          - when clicking on an order, you grab the ID of that order and set it in a global field

          - the relationship to credentials should be based on the global field on the parent side

          • 2. Re: Foreign key do not work
            mikebeargie

            You may also be overcomplicating something. Do you really need a separate table for credentials? If they are unique and joined to an order, could they just be fields in the order table?

             

            Or do you actually need the ability to add multiple "sets" of credentials related to each order.

             

            Just something to think about...

            • 3. Re: Foreign key do not work
              muhammad.ikram@contedia.com

              I would like to keep orders and credentials tables separate. And have an relationship between them.

               

              Is their anyway we can use GTTR on same layout.

              If that's possible, the main question will be solved.

              • 4. Re: Foreign key do not work
                wimdecorte

                muhammad.ikram@contedia.com wrote:

                 

                 

                Is their anyway we can use GTTR on same layout.

                If that's possible, the main question will be solved.

                 

                Sure you can.  You can pick the same TO as the destination as the one you are in.  But it is a kludge.  It does solve your problem but it is not terribly "neat".  I consider using a list for the orders as the starting point to be better.  That's just my opinion.  If it works for you then it works.

                • 5. Re: Foreign key do not work
                  mikebeargie

                  I'm not sure Go To Related Records will actually correct the problem you are trying to solve.

                   

                  If you want to deal with three tables on a layout, EG a parent table (Customers), First Tab (Orders) and Second Tab (Configurations), then you will need to do a little bit of trickery to get it to work.

                   

                  Ideally you would have a separate layout

                   

                  To do what YOU want to do, you need to create a global field in the Parent table (Customers) that will relate to the tertiary table (Configurations). This way you can establish a temporary relationship between Customers and Configurations.

                   

                  Your current relationship structure looks like this:

                  Customers --< Orders --< Configurations (Customers have one or many Orders, Orders have one or many Configurations)

                   

                  What you need to accomplish your layouts is:

                  Customers --< Configurations

                   

                  So essentially you need to:

                   

                  add a global field to Customers called “global_CurrentOrderKey”

                   

                  create a table occurrence of Configurations called CurrentConfiguration.

                   

                  Relate Customers::global_CurrentOrderKey = CurrentConfigurations::OrderForeignKey

                   

                  Change your Configurations portal to be based on CurrentConfigurations instead.

                   

                  Name your Configurations portal “configurations” so that you can go to it by script.

                   

                  Then write a script that “loads” that Order to the global:

                    Set Variable [$id ; Orders::PrimaryKey]   //this comes from your portal on tab 1 that is selected.

                    Set Field [Customers::global_CurrentOrderKey ; $id ]

                    Refresh Window [ flush join content ]

                    Go To Object [ "configurations" ]

                   

                  Lastly, tie that script to a button that is in the portal for Orders.

                   

                   

                   

                  All of this together will:

                   

                  1)      Select an order from your orders portal on tab 1

                   

                  2)      Establish a temporary relationship between Customers and Configurations based on the selected Order

                   

                  3)      Refresh portal data

                   

                  4)      Go to the configurations portal on tab 2

                   

                  OR

                   

                  You can just avoid all of this, get rid of tab 2, and use Go To Related Records from CUSTOMERS to go to an ORDERS form view layout that has a portal of Configurations for that Order, and a button to return to Customers.

                   

                  The latter is MUCH easier and almost always preferred to adding a bunch of RDBMS voodoo.

                  • 6. Re: Foreign key do not work
                    mikebeargie

                    GTRR doesn't solve the "context" issue if the main layout is not based on Orders as well.

                    • 7. Re: Foreign key do not work
                      mikebeargie

                      sorry, email responses to jive strip out content in brackets [ ]

                       

                      I have updated the above.

                      • 8. Re: Foreign key do not work
                        muhammad.ikram@contedia.com

                        Thank you All.

                         

                        Mike Beargie you solution was perfect. Everything does work.

                         

                        One thing that didn't worked is adding a credential primary key as a foreign key in orders table. We would like to see which orders have which credentials. So this mean when we add an credential, we also would to to add that credential key as foreign key in order table.

                         

                        I had a go at adding a global key in credential and relating it to credentialID__fk key in orders and following the trick you mentioned above; that didn't worked quite well.

                         

                        so how is possible I can add a reference of credential ID in orders table.

                         

                        I attached the example file I created.

                        • 9. Re: Foreign key do not work
                          wimdecorte

                          muhammad.ikram@contedia.com wrote:



                          One thing that didn't worked is adding a credential primary key as a foreign key in orders table. We would like to see which orders have which credentials. So this mean when we add an credential, we also would to to add that credential key as foreign key in order table.

                           

                           

                          That's not the way to do it though.  In Mike's example and your setup, the credentials will inherit the Order's primary key as their foreign key.  So Orders and Credentials are linked that way.

                          You do not need to bring back the credential pk to Orders as a foreign key.  There already is a valid relationship that will you to show all credentials for an order.

                           

                          By populate a credential fk on Orders you are in effect limiting that to a 1x1 relationship instead of a one Order to many Credentials.

                          • 10. Re: Foreign key do not work
                            muhammad.ikram@contedia.com

                            Great. Thank you so much for explaining that to me.