1 2 Previous Next 23 Replies Latest reply on Dec 13, 2011 1:46 PM by arthursc

    Location, Locations,. Locations

    arthursc

      Hello,

       

      I am looking for advice on how I create the relationships for the following criteria;

      I am designing a DB for Estates Management. The criteria for this is thus;

       

      Each Estate can have MULTIPLE Primary Locations.

       

      Each Primary Location can have MULTIPLE SECONDARY Locations.

       

      I want to bring this together on one Layout so the administration and user input is simplified.

       

      The Estate Table will consist of;

      __PK_Estate_ID

      Estate Name

      Add1

      Add2

      Add3

      Add4

      city

      County

      Postcode

      Tel Number

       

      The Primary Location Table will consist of;

      __PK_Primary_Location_ID

      __FK_Estate_ID

      Location_Name

      Grid ref

       

      The Secondary Location Table will consist of;

      __PK_Secondary_Location_ID

      __FK_Primary_Location_ID

      __FK_Estate_ID

      Location Name

      Grid ref

       

      Is the above correct to enable me to create the relationships? How do I join them so that all the data is available for input from one layout. I assume that from within the Estates Layout the Primary and Secondary tables would be portals??

       

      A simple relationship example would be very helpful and if possible a simple layout design example.

       

      I am relatively new to FM and am using FM Pro Adv 11.

       

      Regards

      Colin.

        • 1. Re: Location, Locations,. Locations
          Mike_Mitchell

          Hello, Colin. Welcome to FileMaker!

           

          In a case like this, you can certainly design your interface to show the primary and secondary locations in portals from a layout based on the Estates Table Occurrence (TO). However, it would be difficult to allow direct data entry through the portals, since your fundamental relationships graph would most likely look something like this (assuming I'm reading your post correctly):

           

          Graph.png

           

          The reason it would be difficult to allow data entry through the portals is that FileMaker's auto-entry function takes the primary key and automatically enters it on the child record. You've probably discovered that this breaks when you try to enter a Secondary Location from Estates. If I try to create a Secondary Location from Estates through a portal, I can get the Estate ID on it, but there's no way to get the Primary Location ID on the same record ... so the relationship between Primary and Secondary breaks (or is never established).

           

          So what's the fix? Recognizing that each Secondary Location is tied to a single Primary Location, you have to limit the creation of Secondary Locations from the Estates TO. Don't allow users to create related records from Estates to Secondary Locations. I suggest scripting this instead. Put a button on the portal row where the Primary Location appears. Store the Estate ID and Primary Location ID in variables. When the user clicks the button, execute a script to create the Secondary Location record and insert the IDs on the new record. Then you can allow the user to edit the record in the portal.

           

          Let me know if this doesn't make sense. Or if you need more details.

           

          Mike

           

          P.S. Unless you have another reason to have it there, you may not need the Estate ID on the Secondary Location record. If the Primary Location only points back to a single Estate record, then each Secondary Location will automatically relate back to a single Estate record via the Primary Location ID. This is the "tunneling" behavior of FileMaker's relational model. On the other hand, if each Primary Location can point to more than one Estate, then this model may not be adequate, because you have a many-to-many model, and a different setup may be required.

          1 of 1 people found this helpful
          • 2. Re: Location, Locations,. Locations
            arthursc

            Hi Mike,

             

            Thank you for your reply. In general I do understand what your defining as the fix. In practice, It's alittle beyond my knowledge to create a trigger script to define the relationship.

             

            Currently I have three layouts that generates what I am asking. It just seems alot of switching between layouts for the users and this is what I wish to simplify.

             

            I will look into this alittle more and try and generate something in an example to see if I can get it to work before trying this in my app.

             

            If you have a working example then I should be able to work out how to do it, but as a newbie from a green field example I would be stumbling.

             

            Many thanks again for your reply. Nice clean and a great explanation.

             

            Colin

            • 3. Re: Location, Locations,. Locations
              Mike_Mitchell

              Colin -

               

              You're welcome. Take a look at the attached file. It's a stripped-down example.

              • 4. Re: Location, Locations,. Locations
                arthursc

                Mike,

                 

                Great job. Right with you on this. I like it.

                 

                Is there any other logic that I should be considering in this. i.e a warning to the user that the estate has neither a primary or secondary location set up when a record has been created or they just specify a primary and no secondary location?

                 

                Many Thanks.

                Colin

                • 5. Re: Location, Locations,. Locations
                  Mike_Mitchell

                  Colin -

                   

                  That will depend on your business rules. If your business model requires that each estate record has at least one primary and one secondary location, then yes, I would put something in to alert the user that this has not been done. There are different ways to do this, depending on exactly how the workflow is meant to work:

                   

                  1) You can throw up a warning on the data entry layout via Conditional Formatting that goes away when the proper conditions are met (i.e. Count ( Primary Locations::{keyField} ) and Count ( Secondary Locations::{keyField} )). If that calculation evaluates to True, turn the text the same color as the background.

                   

                  2) You can do a similar test before allowing the Estate record to be included in a report of "legitimate" estate records.

                   

                  3) You can perform this test prior to allowing the record to be pushed to another stage in the workflow, if there is one.

                   

                  4) You can create a report of Estate records that do NOT meet this test and forward it to someone who cares.

                   

                  It's all a matter of who cares, what impact it has on the business, etc.

                   

                  Mike

                  • 6. Re: Location, Locations,. Locations
                    arthursc

                    Hi Mike,

                     

                    Thank you again for your reply. Your a credit to these forums.

                     

                    I think option one is the way to go. Just to inform the user or any user that the form has not been completed in full. I don't want to force them to complete the item as there maybe a scenario where an estate only has one location, but I am trying to think where that maybe. If that is the case, then a simple check box could be employed to "turn off" the notifcation. i.e "This Estate has no Primary or Secondary Locations"

                     

                    Not sure how I would override the conditional formatting as suggested above in this scenario.

                     

                    Regards

                    Colin

                    • 7. Re: Location, Locations,. Locations
                      Mike_Mitchell

                      Colin,

                       

                      It's relatively simple to add an additional condition to your Conditional Formatting calculation to allow you to override the notification. Here's how I would implement:

                       

                      1) On the layout, put a text object that says what you want it to say (e.g., "This Estate has no Primary or Secondary Locations) in the color it should be when it's true. If you want it to show up red, for example, then make it red.

                       

                      2) In the Conditional Formatting dialog, set a condition that is a Formula that, if true, causes the text object to have a Text Color equal to the background.

                       

                      3) Make the formula equal to this calculation:

                       

                      ( Count ( Primary Locations::{keyField} ) and Count ( Secondary Locations::{keyField )) or not IsEmpty ( Estates::{overrideField} )

                       

                      This way, if there is at least one primary location and at least one secondary location, OR the override field is not empty, the warning will not appear.

                       

                      The reason I would implement this way is that, when you go into Layout mode, you'll be able to see the text object. If you do it the opposite way - make the text object the same color as the background and then set the Conditional Formatting to turn it visible when the right conditions are met - it's too easy to lose track of it on the layout. At least in my opinion.

                       

                      HTH

                       

                      Mike

                      • 8. Re: Location, Locations,. Locations
                        arthursc

                        Hi Mike,

                         

                        That helps a great deal.

                         

                        I'm going to be cheeky here and ask...any chance you could add that your example you created?

                         

                        Are you UK or US based?

                         

                        Regards

                        ArthursC

                        • 9. Re: Location, Locations,. Locations
                          Mike_Mitchell

                          Sure. See attached.

                           

                          And I'm US based.

                           

                          Mike

                          • 10. Re: Location, Locations,. Locations
                            arthursc

                            That must be east coast then..or you drink way too much coffee.

                             

                            Many thanks again..

                             

                            regards The Slow Englishman!.. and his gun dog.

                            • 11. Re: Location, Locations,. Locations
                              arthursc

                              Just looked at it...

                               

                              Thats soo cool!!! Thanks.

                              • 12. Re: Location, Locations,. Locations
                                arthursc

                                Hi Mike,

                                 

                                I have run through this and added to my Db. I do one issue that I cannot get around. When you add a secondary location using the scripted button it only displays the Primary Location ID number and not the name.

                                 

                                How do i get it to display the name in place of the primary record ID?

                                 

                                Regards

                                Colin

                                • 13. Re: Location, Locations,. Locations
                                  Mike_Mitchell

                                  Colin -

                                   

                                  Just replace the primary location key number with the primary location name from the primary location table. The relational system will resolve it just fine.

                                   

                                  Mike

                                  • 14. Re: Location, Locations,. Locations
                                    arthursc

                                    Hey Mike,

                                     

                                    I tried this but it doesn't work correctly.

                                     

                                    Whe you change the secondary location field from Primary_ID to Primary_name and then go back to the primary data entry field and add records which ever primary location you create the secondary just uses the same field as you 1st entry in primary;

                                     

                                    Primary location TEST A

                                    Primary location TEST b

                                    Primary location TEST c

                                     

                                    Secondary will read as;

                                     

                                    Primary Location Name  Secondary lcoation name

                                    TEST A

                                    TEST A

                                    TEST A

                                     

                                    Is should be

                                     

                                    Primary Location Name  Secondary lcoation name

                                    TEST A

                                    TEST B

                                    TEST C

                                     

                                    Have a try on your example.

                                     

                                    Regards

                                    Colin

                                    1 2 Previous Next