Help request: Scripted import into multiple tables, no ID field in data
Using: FMP10 Advanced on Windows
FMP experience: First time user
DB experience: I dream in SQL - MySQL is my normal system
Task: Converting several standalone FMP6 databases into a relational FMP10 database, normalising some tables into multiple tables in the process. Fair amount through the process - layouts, tables, fields, value lists all done, simple scripts done, complex ones in progress!
Background:Essentially, I have a "people" table containing all people registered with the institution, and I have an "applications" table containing the details of all applications. An application refers to the personal information in the "people" table via a person ID.
I need to import a new set of applications from a CSV. Previously (before normalisation), all the fields were in the "applications" table, and successful applicants were copied into the "people" table as a batch, at which point the person ID was created. There is therefore no person ID in the data for importing.
I believe the solution is something akin to the following:
1. Import data into a new table
2. Update new table with "person ID" where date of birth & name match existing "people" records
3. Cause all remaining records to have a newly generated "person ID" field (see below)
4. Import appropriate fields into "applications" from new table
5. Import remaining fields into "people" from new table
What is the appropriate function or combination of functions for step 2? I'm not sure how to script "update newtable set personID = (select personID from people where dob=dob and name=name)" - the logic of implementing this in FMP script is quite a different way of thinking than pure SQL.
Secondly, what's the best way to implement step 3? I have considered creating new "people" records and then updating "person ID" in the new table again (i.e. a repeat of step 2 as step 3b), or first inserting the new records in "people" if name & dob don't match, essentially reversing the order of the steps, or looking up the maximum value used for "person ID" and creating my own person IDs based on this rather than allowing them to be auto-generated. None of these feel like a particularly natural way to achieve my aim.
Any advice, recommendations, or pointers to other urls that I haven't found would be greatly appreciated.