I've gone through a few books, and as many docs as I can find, but I can't figure out how to do the following (as simple as it is).
I have a file with all sales (sales.fp5)
A file with all invoices (invoices.fp5)
I want to create a new file called transactions.fp5 that:
a) brings sales_id, sale_amt, amt_paid, contact_id from sales
b) brings inv_id, inv_amt, amt_paid, contact_id from invoices
into the transactions file with the fields:
trans_id, entity_id, amt, amt_paid, contact_id
and have trans_id created on the fly (dynamically, sequential). With others relational databases, I can do this very easily (and extremely easily with SQL). I'm used to using any rdbms except filemaker, so maybe I'm missing something simple.
In the new transactions file, create your field, trans_id. In the field definitions dialog, click "Options", select the "Auto-Enter" tab, and check "Serial Number". This will make the trans_id a auto-enter field that grabs the next serial number every time you create a new record in that file.
To make the related data available in the transactions file, you'll have to create relationships from the transaction file to the invoice and sales files, based on the appropriate keys. (To create a relationship, go to File > Define Relationships) You then create calc fields in the transaction file that reference the data from the related tables (using this method you cannot edit the related data from within the transaction file), or you can just draw a field on the layout, specifying the data source as the field in the related table (this method will allow you to edit the data from the other file).
This could be a lengthy subject. Is this information guiding you in the right direction?