7 Replies Latest reply on Nov 23, 2016 12:36 AM by philmodjunk

    Changing Relationship Types

    melclift

      HI there,

       

      I need some help!  I am a user who actively used FM up to about v6/7 - over the last few years haven't really used it, or done any development in it - now with a role change, its back on my plate (yay!!).

       

      I am struggling with relationships - Get the concept - find something in common to make a match, and bring through the rest of the info based on the match.

       

      So, I've started up a new database from scratch.  Essentially (it was all in XL) I've identified the primary components/common bits for it:

       

      1. Vendor (the developer who owns the land)

      2. Client (the purchaser who is going to buy the land)

      3. All of the stock available

      4. Sales In Progress

      5. drafting (for our building designer) - don't worry about this at this point in time

       

      So, my vendor has lots of stock to sell. Each item of stock can only be sold once, however there may have been a prior sale on the property which may have fallen over.

       

      My clients can purchase multiple properties.

       

      My sales keeps records of who entered into a contract on what property, when finance is due, settlement due, etc.

       

      I am having trouble with the relationships between the vendor, stock, sales in progress.  Each of the relationships I have created have defaulted to a setting of many to many - and I want to change it to one to many (i.e., one vendor has lots of different blocks in one estate).

       

      In some instances, it appears to be working. In others, its an epic fail.

       

      Can I have some help please? do the relationships between all the tables look ok? At the moment, clients is sitting in a separate database, whereas all the other tables are in one database called "Properties" - can I have the clients in this database as well? (it was in a separate one from my old old filemaker days pre-relationships).

       

      Many many thanks for your input - please don't get too technical on me at this stage, still remembering all the fantastic FM features,

       

      mel

        • 1. Re: Changing Relationship Types
          melclift

          PS - I have also given the following unique UUID in each table to each record:

          vendor

          stock

          client

          • 2. Re: Changing Relationship Types
            David Moyer

            Hi,

            1) UUID is not a bad idea, especially if you plan to do any remote stuff with FM Go; otherwise, I'd use a serial number.

            2) It looks like your relationships are all many-to-many.  This means that you should look through your tables and, for those primary keys (when appropriate), select Unique values under the Verification tab within the field definition.

            This may not solve your problem, but it was a glaring problem to me.  Hope this helps.

            • 3. Re: Changing Relationship Types
              David Moyer

              P.S.  All Primary Keys should be unique.

              • 4. Re: Changing Relationship Types
                melclift

                HI David,

                 

                thank you!  I'll go in and have a look.  Can you please help me understand Primary Keys and Foreign Keys? These concepts are all very new to me.

                 

                Greatly appreciate your help :-)

                 

                mel

                • 5. Re: Changing Relationship Types
                  Vaughan

                  Consider the coat desk at the theatre. Each person gets a ticket with unique number (a primary key) and all their items (hat, coat, umbrella) gets a ticket with that same number (foreign key) that matches the item to the person.

                   

                  Each person gets a unique number, otherwise there will be confusion.

                   

                  Each item gets one person's number, but because a person can have many items that number cannot be unique.

                   

                  This is a one-to-many relationship.

                  1 of 1 people found this helpful
                  • 6. Re: Changing Relationship Types
                    beverly

                    Excellent example! I'd like to add that every table has a primary key field. So each mitten, coat, etc. also has a primary (unique) identifier.

                    Even if they don't have a relationship further down, setting up the "PK" from the start may be handy later on.

                    And each record can have more than one Foreign Key field, depending on how the relationships go.

                     

                    Sent from miPhone

                    • 7. Re: Changing Relationship Types
                      philmodjunk

                      Now for the next lesson:

                       

                      The presence of "crows feet" on each end of a relationship line does not mean that you have a many to many relationship. It just means that there might be more than one one matching value on that side of the relationship. FileMaker will show a single line connector only if the field only if the field auto-enters a serial number or has a unique values validation option. Other options will function perfectly well as a "one" side of a relationship even though the relationship doesn't show a single line connector. An auto-entered UUID without the unique values option is one such case.

                       

                      A true many to many relationship requires either a join table or a field that stores multiple match values in either a return separated list or the repetitions of a repeating field. It would seem you have such a relationship between vendors and clients as a single vendor can sell to many clients and a given client can buy from more than one vendor.

                       

                      A table of sales transactions whether in progress or not can serve as that join table.