5 Replies Latest reply on Dec 14, 2012 8:26 PM by tlawson

    Can't create one to many relationship


      Can't create one to many relationship


           Obviously, I am new to FileMaker

           I created two tables:

      1.           Store
      3.           Locations

           I want to create a 1-to-many relationship between "Store" and "Locations".  I created a unique ID field on each table using a string with the formula "Get (UUID)".  The reason I'm not using serial numbers is that the database will be multiuser and I want to make sure values are unique (I'm not sure how FileMaker handles a multi-user environment with sequential numbers as the primary key).

           I also created a foreign key in the "Locations" table.  I linked the fk in the Locations table with the pk in the Store table.   I also created a portal to test the results (which appear to work).

           The problem is that when I go into the "Manage DB" and look at the "Relationships" tab, the relationship shows as a "many-to-many" relationship instead of the "one-to-many" I'm looking for.  How do I change the relationship or does it even really matter?





        • 1. Re: Can't create one to many relationship

               Serial numbers work just fine in a multi-user environment. Get ( UUID ) is a feature new to FileMaker 12. We've used the serial number in multi-user environments for years. Get ( UUID ) should do the job, but make sure that you define this ONLY for Dealership, not the kf field in Location. That field should be a simple text field with no such auto-enter calculation.

               If you add a "unique values" validation to the __kp field, you'll then see the expected single line connection to the Dealership table occurrence. (or you can switch to serial numbers.)

          • 2. Re: Can't create one to many relationship

                 That was it!!!!!  Thanks!

                 I have another related question.  You say not to use Get (UUID) for the foreign key _kf_Location_ID.  I get that.  What I'm trying to do is it autoassociate a new location with an existing Dealership.  I set _kf_Location_ID to be a autoentry textfield caculated by: $$CURRENT_DEALERSHIP_ID

                 I've seen this construct used in many of the "Starter Solutions".   They set a initialize foreign keys of associated objects using a global that is set to the master record (in this case, Dealership).  I know that you need to set a global variable in a script.  This technique would allow you to associate locations, employees, groups, etc... with a dealership.  Just not sure how to work it.

                 Here's where I'm confused:  I don't know when to set that global variable or how to read it in when I created an associated object (in this case, Location).  I'm sure it has to do with triggers but I'm not sure what triggers to put where.  I'd like to be able to create a location inline using a portal but also be able to create a location by clicking on a button that says "Add Location".

            • 3. Re: Can't create one to many relationship

                   It depends on what you want to do. If you put a portal to location on your Dealearship layout, you can use it to create location records linked to the current dealership record without using this method at all. Just enable "allow creation of records via this relationship" for lcoations in the relationship to Locations.

                   If you want to create a new record on the locations layout and have it automatically link to the current record shown on the Dealership layout, then you can use the onRecordLoad trigger to perform this script:

                   Set Variable [$$Current_Dealership_ID ; value: Dealership::__pk_Dealership_ID ]

                   Be aware that any scripts that:

                   Change layouts to come to the dealership layout

                   Enter browse mode on the dealership layout

                   Change the focus to a different record on the dealership layout

                   will also trip this script trigger.

              • 4. Re: Can't create one to many relationship

                     Would you use this method or another?

                     Here's what I'm trying to model:

                •           Dealership
                •           Location(s)
                •           Consultation(s) - Many to one relationship with Dealership           
                  •                     Contains multiple agenda items (Agenda Item Table)
                  •                     Multiple observations during the day (Observations Table)
                  •                     Multiple recommendation (many to many relationship Observations) - in other words, 1 recommendation may satisfy multiple observations AND 1 observation may be related to multiple observations)
                  •                     People/Group(s) associated with an observation or recommendation (Employee Table, Group Table)
                  •                     Note(s) associated with any of the above (Notes table)

                     As you can see a single consultation may have many different objects associated with it.  This is why I thought that using a global variable might be the best solution.

                     I did try the entry within the portal and it worked just like you said.  Thanks again for you help.  It's been very accurate and straight to the point.

                • 5. Re: Can't create one to many relationship

                       Trying the portal worked just like you said.  Thanks!

                       The process I'm trying to model is a little complicated.

                  •           Dealership w/ multiple addresses
                  •           Employees / Groups
                  •           Consultation (Over time, there could be multiple of these and many record types associated with that visit)           
                    •                      Agenda Item(s) - many-to-one relationship with consultation
                    •                     Observation(s) - many-to-one relationship with consultation
                    •                     Recommendations(s) - many-to-one relationship with consultation                     
                      •                               There is a many-to-many to relationship between observations and recommendations, all related to this consultation
                      •                               This record is like a task with a start date, etc...
                    •                     Note(s) - many-to-one relationship with consultation, observations, recommendations
                    •                     Employees/Groups - many-to-one relationship with both observation and recommendation                     
                      •                               Who was I watching when I decided to write down an observation
                      •                               Who am I talking about when it comes to making a recommendation

                       Because I have so many different object types associated with an instance of a consult I thought this might be a good use of a global variable.  I'm not sure, but I think I can make TOs that merge that $$current_consult_ID with the recommendation table and others.  Not sure.

                       Thanks for all your help.  It was very timely.  Thanks again.