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.
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.
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
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.
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.
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.
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
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
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!
I always learn new ways of doing things reading threads like these.
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.
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
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?