If you had such a composite key--which is possible, how would you use it?
Such a key is rarely needed in a FileMaker database.
Presumably you want to combine the customerNumber with the TransactionNumber.
If you defined a text field in ItemFromTransaction with an auto entered calculation such as:
TransactionTable::CustomerNumber & "|" & TransactionNumber
You'd get such a composite key, but note that you can refer back to data in Transaction and Customer from a layout based on ItemFromTransaction without using such a key.
You know I think I might have been mistaken about the composite key being necessary now that I've looked back over my project. I'm just so used to having to set some form of a primary key in every table. My larger issue is still reseting the auto-generating field with each new transaction...
If you need a primary key for the third table, it can also be an auto-entered serial number. It's fairly easy to add such to an existing table in FileMaker, should the need arise:
1) Add the field and specify the auto-entered serial number field option.
2) Put the field on a layout based on the same table, select Show All Records, put the cursor in the field and use Replace Field Contents with the serial numbers option to put a serial number into every existing record. Be sure to specify the "update entry options" option so that your next serial value automatically updates to be one larger than the largest serial number assigned by this operation.
My larger issue is still reseting the auto-generating field with each new transaction
That shouldn't be necessary. Can you describe in more detail what you mean by that?
This database is tracking customers and their transactions. Each transaction can have more than one item sold, so the ItemNumber field in the ItemFromTransaction table is meant to track that. I want it so that ItemNumber '1' from TransactionNumber '1' is the first item of that transaction, ItemNumber '2' is the second item of that transaction, and so on.
I'm entering sample data to test my database and I've entered 3 items for the first transaction. I've moved onto entering the second sample transaction, and instead of the first item listed in the second transaction having an ItemNumber of '1' it's listed as ItemNumber '4'.
I realize this will make the ItemNumber field not unique but I think this is the best way to tackle my problem, as each transaction could be a single item or upwards of twenty.
IF you are using Filemaker 12 consider using get(uuid) which generates a very long string that 'probably' will never be duplicated and is quite useful when you have remote, non-connected users. Serial numbers don't cut it in these situations.
Your Mother file would have its own uuid for each record. Each related child record would have its own uuid and store the mother's uuid for linking. The same with the grand child which has its own uuid for identifcation and can have a linking field for both the Mother file and the child file.
Your use of transaction is often called an Invoice with the other file being the Line Items. Transaction would then refer to the action of creating the invoice and line items or a purchase order and line items, etc.
I prefer to forget what the tables are being used for since no matter what someone calls them the rules and design are the same.
An invoice, purchase order, library checkout, etc. all have the same table structures and links and rules. Learn one and you've learned them all.
That 'probably' makes me nervous. I want my primary keys to be absolutely 100% unique, no "probablies" allowed! When I read that description of Get (UUID), I stopped recommending it as an option for primary keys.
Why do you need the numbering to start over with 1 for each order? This is not a primary key as it cannot uniquely identify records in the table.
If you must have the number, here's one way to get it. Define a field, constOne and define it to auto-enter a 1. You can do this by entering 1 in the data box on the auto-enter tab of field options for this field.
Define a second field sCounter as a summary field and define it to compute a running total of constOne. It won't make any difference for your portal, but if you use the options for restarting the running total and specify TransactionNumber as your "group by" field, you can sort your records by transaction number and get a list of ItemsFromTransaction that number sequentially, restarting with each change in transaction number.
I'd just like each transaction to have a list of items per transaction is all.
Which you have in the ItemsFromTransaction table.
You may want to look at this demo file that illustrates the basic tables for invoicing: http://fmforums.com/forum/showpost.php?post/309136/
It uses this table/relationship design:
Note the similarities to your design. You have all but the final table, products.