Good morning, everyone!
The Set-Up: Our department utilizes FileMaker Pro to track student admissions, records, grades, and other historical data. Most recently, we've made a modification to our "Student Information" table and established relationships to two others: "Documents" and "Emails." In tandem, these three tables now allow us to attach both documents and correspondence to a student's file for centralized access via a portal tool, and they're all connected by the unique value assigned to the 'SID' field (which contains a student's assigned ID number) in "Student Information." In short, when I attach a file to a student's record, then a 'new record' is generated in either Documents or Emails, which contains the content of the item in question.
The Problem: With our current admissions process, I get the student's files days before the system can assign an ID number to them. So, when the SID field is blank, I can't attach documents/emails to the student's record (because the intertable relationship requires that SID number to look up/make a record in either the Documents or Emails table). I've experimented with the SID field to auto-serial a number in the interim so that I can attach files, but when I go to change the SID to the student's actual ID number, the records that were previously attached disappear; which makes sense, because the system recognized "Temp01" as the student's ID at the time, and doesn't know that the actual ID number will be the permanent, real number.
The Question: Is it possible to script (or automate via calculation) a way to make a field in another table take on the contents of the field in another table? For example, if I've attached a transcript to a student and the system thought the student's ID was "Temp01" when it made a record in the "Documents" table, is there some way to make that "Documents" record automatically update to the real ID number of "Real02" when I change the field contents in the 'SID' field of the "Student Information" table?
This is my first time asking for help on the FMPro forums, so please let me know if you need to see anything additional to help understand my problem.