Can you post a more detailed example of what you want to do?
On a side note:
"The first table has fields containing various chemicals, amounts and procedures related to a certain chemical "recipe"."
Sounds like you might be better off with two tables for that. One table where one record = 1 "recipe". ANd then a related table to list the materials used in that recipe.
A little more description of what your audit file is supposed to record/display would be helpful.
I have experience creating audit tables. I usually link my audit table to a Table of Global fields where I store information and use script triggers to store and eventually write out my audit record.
OK. so the purpose of the audit form is to again list the same ingredients as found in the original chemical formula. Along with this the audit form lists more detailed info about the specific chemicals used. For example. if the original formula calls for the 20MG of ascorbic acid, the audit form would again display this along with the specific lot number, and expiry date of the ascorbic acid. To go back to the original "recipe" idea, the first table is the original recipe, and the audit form needs to be created everytime we actually make that recipe, to show that we in fact followed the recipe correctly. Hopefully this helps explain what i'm trying to do!
Ok in this case audit is a process of verifying the original record.
In this case you will need to establish at which point in the original process you want data in the audit record to be established.
You could do this a number of ways.
You can create the audit record at some point along the original data entry process or you could create the autid record when you decide to perform the audit I prefer the later.
I cant swear that my process doesnt have some short cuts that can be taken but the general way I would do it is
First I would define my audit record to have a number of _fk (foriegn key fields) the same ones the original record had for itself and displaying any referenced information.
I would define a table of global fields to hold all these keys.
I would define a relationship between the original record and the audit by the originals id to the _fk field in audit.
I would also define any other key relationships to other tables
Desgin my audit layout to display any orginal record fields as related fields I want to display along with any other referenced data
From a layout where the orginal recepie record you wish to audit is execute a script to pass all that records relationship keys into a table of global fields
Move to an audit record layout create a new blank record and set the relationship key fields in the new record.
Some things to think about here is omitting all records before adding one so you only have the related one in the found set.
This would allow you to lookup the original values that were posted in the record and display them as related fields on your audit layout.
You can apply this same principle for creating a set of recipie items by looping through the orginal recepie items and creating individual audit records after the main audit header record is created and view them in portals in your audit record layout.
OK, that makes sense! i know this is a newb question, but how do i define my fields as primary or foreign key fields?
Filemaker doesnt care about them being keys but for your own sanity making the field names prefaced with _pK(primary Key) and _fk(foreign Key) really helps you know what fields to link to.
A primary key is a field that has a unique value within the table
A foriegn key is a field that has a unique value in another table (and can be repeated in this one) and is in this table to establish the relationship between them.
And FileMaker primary keys are almost always defined as auto-entered serial numbers.