1 Reply Latest reply on Jan 8, 2013 11:25 AM by philmodjunk

    Auto revert on duplicate new record creation attempt?



      Auto revert on duplicate new record creation attempt?



           I have been trying to set up a FMP database to store and sort data from the Tests of certain manufactured Parts.  There are about 3000 Tests, each with unique data, covering about 2000 Parts.  So some parts have been tested multiple times.

           The Test data imports fine... 3000 records, all there, but I am trying to create a new table of the 2000 Parts, and link the (sometimes multiple) Tests to these Parts.  The best way I've found to do this is to link the "Part ID" field from the Part table the "Part ID" field in the Test table.  However, importing the data from Tests does not create new Part records, so I have made a script which loops over Test records, and creates a new Part record with that "Part ID."

           This all technically works, but I don't want to create multiple Part records with duplicate "Part IDs."  So I set the "Part ID" as a unique value under validation.  However, while running the script, this means I have to tab through and click the ~1000 or so dialogue boxes that come up, telling me I have a validation error with duplicate "Part ID" values, and I need to revert the record.  Is there anyway to set things up so that when the script attempts to create a record with one of these Duplicate "Part ID"s, instead of popping up a dialogue box, it just automatically reverts the record for me, or does not create it in the first place?


           Thanks for any help,





        • 1. Re: Auto revert on duplicate new record creation attempt?

               Here's a simpler way to get a table of records where Part ID is unique in every record.

               Open field options for the Part ID field in the Parts table. Click the validation tab and specify "unique values" and "validate always". Now import the same data you've been importing into Tests into the Parts table mapping only those fields, such as Part ID that contain data you need in this table.

               When you import, the records with duplicate Part IDs will be automatically excluded from the table.

               I also recommend that you add a new field to use as the Primary Key field in place of the externally provided Part ID. You can load this field with serial numbers and set up the field to auto-enter serial numbers for new records created. Then you can change over to using this field instead of Part ID in your relationships.