Tables and Relationships

Hi Juan, Despite what the white paper says, it's really unnecessary to designate a Primary and a Foreign key with different prefixes. Relationships in FileMaker are bi-directional - though in practical use they are mostly used in one direction - and the only thing the "pk" and "fk" prefix does is tell you what direction the original developer was using. However, if your ER (entity-relationship) graph is well-organized and your key fields appropriately named, then there is no need to designate primary or foreign...the direction should be quite clear based on the arrangement of the graph and the logic of the structure.

With respect to your graph above, using pk and fk also needlessly complicates things by requiring two fields rather than one. My strong recommendation - and I've been using FM for 30 years and developing in it for 24 - is to use ONE field for each key that is required such as Contact_ID and Invoice_ID, with Auto-Enter Serial set for the Primary key field in the home table for that key, i.e. Contact_ID in Contact table and Invoice_ID in Invoice table. When you create a relationship from Contact to Invoice, you simply connect the Contact_ID field in each.

One naming approach that I do find useful when working with complex databases having hundreds of fields, is to preface key fields with "zk_", so for example, zk_ContactID and zk_InvoiceID.  This nicely organizes all key fields at the end of the field list. (I also use "zi_" as a preface for interface elements that may appear on screen but that the user has no direct control over, e.g. "zi_CreditCardLogo" ).

So are you saying to create a primary key on the contacts table name contact_id set that to auto-enter serial and then create the same key without auto-enter serial on the invoice table and connect them together to create the relationship?

Drawing1.vsdx
 Like this Will?

Exactly. All related Invoice records will have the Contact_ID value in their Contact_ID field. New invoice records can obtain the Contact_ID of the customer in a number of ways. The simplest is to create a short script:

Set Variable [ $Contact_ID ; Customer::Contact_ID]
Go to Layout [ <some layout based on the Invoices table> ]
New Record/Request [ ]
Set Field [ Invoice::Contact_ID ; $Contact_ID ]

You can tie this to a "New Invoice" button in Customers if you want.

It is also possible to create new records in a related table using a portal which will automatically populate the Key field on the related record, however I generally don't recommend this because of the tendency for people to accidentally start and then abandon new records, thereby cluttering up the related table with blank records. There is also a technique called the "magic key" which allows you to create new records by a single "Set Field" script step but that's a little more advanced technique.

And, to further your diagram, the Invoice table will likely have a "child" table of "InvoiceItem" for the Individual Line Items that appear in the Invoice with Product_ID, Quantity, Price, etc. The relationship then will be from Invoice to InvoiceItem using the key field Invoice_ID. InvoiceItem will have a relationship to Products using Product_ID, with Product_ID being an auto-enter serial field in Products and a plain number field in InvoiceItem. When you add a line item to the invoice, you select the Product and populate the Product_ID field.

Key-TEst.fmp12

The above is very simple just to test it out.
All seems to work.
Thank you.

I see what you mean, so many question to sort out...
I'll get it soon.

It's not that hard but it's helpful to get some guidance up front....

Thank you for your help

Very helpfull