1 2 Previous Next 21 Replies Latest reply on Nov 20, 2015 2:04 AM by weedonpaul

    product key & relationship


      I've been looking at my relationships compared to the relationships in the pre made databases, and I noticed that I don't have a product key(unique id) for each table that is tied t other tables. I'm just looking for some sound advice as to weather I should keep my relationships as is or change them by adding a product key?Screen Shot 2015-11-12 at 7.34.40 PM.png

        • 1. Re: product key & relationship

          A unique ID (Primary Key) IS sound advice. It can be numerical, alphabetical or both (I avoid anything not alphanumeric with keys).


          It need not be labeled as ID.


          If what you have is unique in each table, then you shouldn't need to change anything.


          The fk (Foreign Key) in related tables, of course doesn't need to be unique as there can be one-to-one, one-to-many and many-to-many relationships.


          If, however, you use keys that at some point may NOT be unique (such as names or people or places), then you may re-think just those tables. You can create and populate the keys, then push to related tables using your current relationships before changing to the new relationship in the graph.



          • 2. Re: product key & relationship

            I always use get (UUID) for unique identifiers, but it doesn't have to be done that way. There are other ways that are probably less resource intensive.

            • 3. Re: product key & relationship

              I built an entire data base like you without unique ID's. The problems started to come when people changed their names or other things that identified them selves. I had clients and used their company name as an identifier, and when they decided to rebrand all my relationships had to be altered.


              I am now rebuilding with unique ID's that are alfa numeric.


              hope that helps you decide

              • 4. Re: product key & relationship

                Check out the FIleMaker webinar series page.

                There was a recent session on Relationships. FileMaker Web Seminars, FileMaker Webinars | FileMaker

                It points out the need for unique keys and such.


                And as always, reading the FileMaker Training Series is a good place to get a great discussion about this.

                • 5. Re: product key & relationship

                  I'm sorry I didn't look at the relationship table that I had posted prior posting it, I have 2 tables that are related to the Baseball card table. The are conditional formatted relationships. If I chose to make UUID for all the tables will I loose my conditional formatting?

                  Screen Shot 2015-11-13 at 5.12.59 PM.png

                  • 6. Re: product key & relationship

                    conditional formatted relationships? do you mean cascading/conditional selects (such that if you select a value in a drop-down, the next field's value list is based on the relationship)?


                    IF you keep the relationship, but change the keys, then no. Use my steps to create the primary keys, update the new foreign keys (using the old relationship), then change the relationship "matches" to use the new keys.


                    Make a backup of your file(s) before making such a radical change, of course.

                    • 7. Re: product key & relationship

                      I'm not sure what you mean by conditional formatted relationship. Do you mean you have fields that are conditionally formatted that depend on the relationship? If you set up the relationships correctly - again, they don't have to be UUIDs, just sequential serial numbers will work, but using some unique identifier is critical - the conditional formatting should be retained.

                      It's not a bad idea to name your key fields something like _kp_Manufacturer in the Manufacturer table, _kf_Manufacturer in the related tables. The k means key, p means primary, f means foreign, and the initial underline ensures (when you sort by field name) that these critical fields end up at the top of the table. Then, you change the relationship link from Card Manufacturer = Manufacturer to _kp_Manufacturer = _kf_Manufacturer; I'd be surprised if your conditional formatting was affected in any way.

                      • 8. Re: product key & relationship

                        I'm not sure I know what you mean by "Conditional Formatted Relationship".


                        If you add primary keys, you will lose your relationships that you've set up. But with some careful data management, you can easily get them back to the properly related status.

                        In your current set up, I'd create primary keys in your Sports and Manufacturer tables. Then create foreign key fields in the Cards table. Wherever you see a record in your cards table that matches one in the sports table, copy the key from the sports table to the foreign key field of the cards table. Then set up the relationship between the foreign key and the primary key. Do this for each of the four Table Occurrences you have.


                        HOWEVER . . .

                        You may want to rethink your table set up: You have five entities, which should be five different tables: Cards, Manufacturers, Brands, Teams, and Sports.

                        The reason they should be different tables is because one sport can have many teams. It is not the best advice to keep writing "Football" over and over in the teams table.


                        I'd recommend thinking through the relationships that are needed for this. If you're a member of Lynda.com, check out the courses there about FIleMaker. There's a specific one about relationships. And again, read through the basic FTS:


                        You have to be a member, but its worth it: http://www.lynda.com/FileMaker-Pro-tutorials/FileMaker-Pro-14-Essential-Training/373330-2.html

                        Database Skills, FileMaker Pro Training | FileMaker

                        • 9. Re: product key & relationship

                          What I mean by conditional formatting is that lets say I select Upper Deck for the Manufacture and then the Brand will show all related fields for my selection in Manufacture

                          • 10. Re: product key & relationship

                            Yes! that's conditional Value Lists. It's based on a relationship between the first value and the second.


                            there may be a table of these values used for "lookup" of the second based on the first.


                            You can search for Conditional Value List or Cascading Value List and get several articles that might help:


                            such as this one:


                            And there are more!



                            • 11. Re: product key & relationship

                              I always learn new ways of doing things reading threads like these.

                              • 12. Re: product key & relationship

                                Ok, new problem. I have finally figured out how to use the primary and foreign keys but I've noticed a big issue. Is if I use the PK method for my drop downs, it trickles down to all to other records but does not show a value that was there prior. That is a alarming to me considering I have over a thousand records with blank spots.

                                • 13. Re: product key & relationship

                                  if you have 2 tables with the relationship

                                  Table1::Pk_ID = Table2::Fk_ID

                                  and you have a layout based on table2 and want to have a drop down to get data from table 1 you put the field Table2::Fk_ID on your layout and have a drop down choosing values for Table1::Pk_ID

                                  Now the record is related:-)


                                  Not sure if that answers your question or helps

                                  • 14. Re: product key & relationship

                                    Manufacturer field on this layout is the one that I used the PK and the FK to relate. After I altered the fields I reviewed the rest of the records to see if it would trickle down to the other records. As you see now I have blank fields in the rest. My question is can I make the change in relationship and change to the value field and it apply to all records?Screen Shot 2015-11-19 at 11.52.19 AM.png

                                    1 2 Previous Next