3 Replies Latest reply on Apr 19, 2013 8:49 AM by philmodjunk

    Field value selects existing unique key or creates a new one



      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!



        • 1. Re: Field value selects existing unique key or creates a new one

               It would help to know more details about your basic design. My best guess is that you have this structure, but with your names instead of mine:


               Devices::__pkDeviceID = Join::_fkDeviceID
               AnotherTable::__pkAnotherTableID = Join::_fkAnotherTableID

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               I do not, however, recommend that you use your user entered serial number in place of __pkDeviceID. That should always be an internally, automatically generated unique ID such as an auto-entered serial number.

               But what you can do, is create a second Tutorial: What are Table Occurrences? for Devices and link it to Another table by this user entered serial number:


               AnotherTable::DeviceSerial = Devices|DeviceSerial::DeviceSerial

               And enable "Allow creation of records via this relationship" for Devices|DeviceSerial.

               Then the following script can be performed off an OnObjectSave script trigger on the AnotherTable::DeviceSerial field:

               If [ IsEmpty ( Devices|DeviceSerial::DeviceSerial ) // no such device in table ]
                  Set Field [ Devices|DeviceSerial::DeviceSerial ; AnotherTable::DeviceSerial ]
                  Set Field [ Join::_fkAnotherTableID ; AnotherTable::__pkAnotherTableID ]
                  Set Field [Join::_fkDeviceID ; Devices|DeviceSerial::_pkDeviceID ]
               End If

          • 2. Re: Field value selects existing unique key or creates a new one

                 You have the structure correct (as well as almost exactly my key naming). Thanks for the link; I'll use that notation henceforth.

                 And thank you for the advice on PKs. I was unsure of that - serial number seemed OK, but I think I understand the logic behind using an auto-generated one. I'm still learning forms, stnadard/ best practices etc.

                 I assumed some possibility that FM wouldn't require a script to create a record in this fashion when both value Uniqueness test and creation of records via relationship are configuration items. But, one never knows. I will gratefully follow your suggestion. THANK YOU!

            • 3. Re: Field value selects existing unique key or creates a new one

                   The script is needed to manage the data in the join table. And I'm not convinced that you need a join table, but then I know little about your database.

                   The risk to using any externally generated value as your PK value is that your database is then at the mercy of how that value is generated and entered into your computer. Should that value be generated incorrectly or be entered into your system incorrectly, fixing the issue in your datatabase can break links to related records if not done with care and attention to detail and a mistake can scramble things up pretty badly.

                   Even if you are scanning barcodes to enter that serial number, there is still the possibility, though much less likely, of an error--such as encountering a damaged label and having to manually enter the code.

                   So if you don't use the externally supplied serial number as your PK, any such issues become simple data entry edits.