Field value selects existing unique key or creates a new one
To set the expectation level, I'm new to Filemaker, understand basic elements of relational databases, and a fairly experienced programmer. Here's what I'm trying to do...
I have a table of "Devices" with a unique 'serial number' as the PK for each device record. Good enough.
In another table (related through a join table for other reasons) I have a field in which the user is expected to enter a serial number. If that serial number exists in the 'Devices' table, I want it to refer to that record & field in the Devices table. If it does NOT exist in the Devices table, I want to create a new record with the new serial number as entered by the user.
I assume I'll have to write a script to get the forms up for the user to enter the other information in the record: fair enough (if I don't, that would be nice too). But, if the record already exists, there is nothing new for them to do.
So.... is there some form / layout field configuration settings that can maked the determination between existing or not? If not, that's OK - I can scan the table in a script. It just seems like there is a uniqueness check that FM would already be able to do.
Any advice appreciated - thanks!