What do I need to know to work with many-to-many relationships in FileMaker Pro?
A student can take many classes and a class can have many students. Related these two tables together would be difficult without the use of a join table. The join table will contain the primary keys from the student table and the class table. The join table can be called anything you want. For this example we will call the join table “signups.”
Italics does not identify the primary key in Manage | database | relationships. It simply identifies that the field is a match field in the relationship. It could be either a primary or a foreign key.
I now know you can not simply 'set' a primary key in FMP but instead rely on unique auto-generated serial # by FMP.
With FileMaker 12, you now have the option of using a text field with an auto-entered Get (UUID) as the primary key. This approach can make moving data from one copy of the file into a new copy of the file much simpler to do as you don't have to update serial number settings with Get (UUID).
My other question is I plan to create another table to be used in a subform and plan on using a combination of the TransactionIDNumber and Item number as a compound key. Is there any common sense that suggests not to do this?
Why do you need to match values in two fields instead of just one? This is sometimes useful to do, but should only be done if there is a reason to do so.