11 Replies Latest reply on Oct 29, 2016 9:43 PM by philmodjunk

    Linking layouts


      I have been struggling to find an adequate way to connect data between tables other than using a portal. In this instance, a portal is not ideal because there is too much data to enter.

      The basic layout connects tables representing Owner, Pet, and Clinical record data. From the Owner form I have placed a tab to enter a new pet. This tab takes the user to a new form where the pet details can be entered. I need to link pet with owner using the foreign key relationship. I want to populate the foreign key data automatically once the Pet form opens in data entry format. I have tried a number of recommendations without success. One of the problems I have also struck if I do place the correct data in the Pet foreign key and subsequently delete this particular Pet, it also deletes the Owner details from the Owner Form. I have undertaken a lot of reading around this problem with little joy. Everyone seems to recommend the use of a Portal. I must admit this is a lot easier, but inadequate for these purposes. I anyone has a solution that doesn't involve complicated jiggery pokery that would be greatly appreciated.

        • 1. Re: Linking layouts
          Markus Schneider

          ...One of the problems I have also struck if I do place the correct data in the Pet foreign key and subsequently delete this particular Pet, it also deletes the Owner details from the Owner Form...

          can You post a picture of the define-relationship - window? (relationship graph, double click the relationship box (the one with '=' or other operators))


          You can define relationships so parent/child data is deleted as well..

          • 2. Re: Linking layouts

            Screen Shot 2016-10-29 at 11.19.31 AM.png

            Screen Shot 2016-10-29 at 11.15.42 AM.png

            Screen Shot 2016-10-29 at 11.14.55 AM.png

            Screen Shot 2016-10-29 at 11.13.55 AM.png

            • 3. Re: Linking layouts

              Your primary key in the client window "vc3" does not match the foreign key in the pet table. The client ID in the pet window  shows "vc9". It should be vc3.... Are you showing a related example there?


              you seem to be on the right track with your script. How are you generating your primary keys.

              • 4. Re: Linking layouts

                Your Patient layout is based on Client when it should really be based on Patient. Because it is based on Patient, your new window and new layout has the same context as the first layout and the new record script step is creating a new client record instead of a new patient record.

                • 5. Re: Linking layouts

                  I put in the screenshot to show that the data introduced into the foreign key isn't that in the Client primary key. This is where I am having difficulty copying from the Client table into a new pet record.

                  The data in the primary key is generated from settings specifying a unique value during the generation of a new Client record.


                  Sent from my iPhone

                  • 6. Re: Linking layouts

                    See my previous post. Step one is to base your patient layout on the patient table. The go to layout step shows me that your patient table specifies client in "show records from" in layout set up and that defeats the purpose of opening your new window and then changing layouts.

                    • 7. Re: Linking layouts

                      What Phil is saying is an excellent tip.


                      This layout context issue still trips me if I'm not being careful -- even though I've been using FM for many years.

                      • 8. Re: Linking layouts

                        I was wondering how you deduced the context Issue. But you had evidence 

                        • 9. Re: Linking layouts

                          Of course Phil is on the money. .... Go to "layout setup" for your patients layout and select the proper table from the drop down.

                          • 10. Re: Linking layouts

                            Plus, when data aren't showing correctly, or as expected, that's one of the first things to check.

                            • 11. Re: Linking layouts

                              To elaborate on how I knew that this was the case, Go to Layout always specifies both the name of the layout in quotes and the name of the table occurrence specified in layout setup in parenthesis. By comparing that name to the name of the layout and the table occurrence boxes in the relationship graph, you can tell that the context wasn't correct here.