Displaying a new record from a related table
I’m at a level with FM where I’m learning to work with relationships. I’m a lawyer doing this as an amateur programming hobby (fun + frustrating :-) which might eventually have practical uses.
- The database. 2 relevant tables: Transactions and a related table called Files. When a new record is created in Files, the ID (primary key) is an auto-enter serial number, and the file number is created by a calculation, without user intervention.
- The layout name: “Data Entry” (context: Transactions table). It will create new Transaction records. For each new record, it should also create and display a new related Files record containing a unique file number for the new transaction. The layout contains related fields from the Files table which display the new file number and other data.
- The relationship: the primary key field in the Files table (pk_files_id) is connected to the foreign key field in the Transactions table (fk_files_id).
- The script: The layout has a “new record” button which runs the following script:
- New Record/Request # creates a new transaction record in the data entry table
- Go to Layout [ “Files Table” (Files_New) ] # “behind the scenes”
- New Record/Request # creates a new record in the Files table
- Go to Layout [ original layout ] # back to Data Entry layout
- Refresh Window # was supposed to update and display the related fields from the Files table
What I expected to happen is: after the script is run, there is a new Transactions record and a new Files record. The new Files record should appear in the related fields in the layout.
In actuality, a new Files record does get created, but nothing appears in the related Files fields in the layout.
So I changed the kf_files_id field in the layout from an edit field to a dropdown list displaying records from the Files table. This lets the user select (hopefully) the most recent record in the Files table as the file number for the new transaction. The remaining related Files fields then are immediately populated with the corresponding new file information.
- Can I make the new file number information appear automatically instead of the user selecting it with a dropdown menu?
- If I have to do it manually, how do I make sure that old file numbers are not assigned by a user in the future to new transaction records? (there will eventually be transactions tables for such things a real estate transactions, incorporations, estates and so on, various categories of legal transactions).
I need a separate Files table because there will eventually be several transactions tables, each handling a different category of transactions, but all fitting into a single file numbering system in which consecutive file numbers are assigned to new transactions from different tables, as they occur.)