Updating unique incremental ID in a linked table when importing new records
sorry if my questions seems trivial, but I'm a complete newbie in FMPro.
I've attached a picture of the different tables I set up and their relationships.
I will have to import sample manifests to populate the Samples table, among others; in each manifest there is a field with a Sample ID, which will populate the Samples-Sample ID field, as well as Previous submission, which is empty if it is the first time a sample is sent to us, or contains an already existing Sample ID is this is a resubmission (thought this new aliquot will have a separate and new Sample ID itself).
To be able to link all the records, be it physical samples, haplotypes etc, for a specific individual, I have created a table called UID. In this table there is a field with the same name. I defined it a a number, incremental and unique.
My question is, when I import new records into the Samples table, how do I get FMPro to automatically generate a new UID when the Previous submission field is empty (and add it to the UID table, as well as the UID field in the Samples table), but when it is not empty, go fetch the UID associated with the Sample ID in this Previous ID record, populate the UID field in the Samples table with it, and of course not create a new UID (i.e. no incrementation) in the UID table.
Example of a manifest with two records:
|Sample ID||Previous ID|
So, this is the first time we receive the first sample, DSF001, but we already have records for the second individual, RSF034, and its Sample ID in the Samples table is RSF02. The UID of RSF02 is 56, and the current highest UID in the UID table is 70.
When I import this manifest into Samples, I need to get the following:
|UID||Sample ID||Previous ID|
and of course 71 added to UID in the UID table.
I hope this makes sense. Is it possible to do that?
Thanks in advance for your help,