Disallow record commit?

[eljefejb]

Hello -

I've got a puzzler, at least for me...

Here's my setup: I have a data-entry layout ("Certs") that allows the user to associate an inspector with a part. There *should* be only one record which links an inspector and a part, but it's the record ID field that FileMaker checks to see if the record is unique. A user can click the New Record button and FileMaker creates a new record. The user then selects a part and an inspector.

What I'm looking for is a way to not allow the newly created record to be committed if there is already a record that links the inspector and the part. I created a self-join relationship based on the inspector and the part, and a global (calc) field that counts the IDs from that relationship. If the calc field is greater than zero, then there is already a record linking the part and the inspector. That much works. It detects the presence of an existing record linking the two. How, then, do I stop the new (duplicate) record from being committed?

Confusedly,

Jeff

(Thanks, in advance.) :)

[comment]

You could prevent duplicates by inputting the data into global fields first (and modifying your relationship accordingly). Then, if there are no related records, create the actual record.

What really confuses ME, is why you need those records in the first place. If a part is allowed only one inspector, you can enter the inspector's ID right into the part's record and be done.

[eljefejb]

Sorry for the confusion...

A part can have multiple people who are certified to inspect that part. An inspector can be certified for multiple parts. However, a certification can be for only one part and one inspector. It is a record of Inspector X's certification status for Part Y. The status is one of "Certified", "Decertified", "Consultation Required", "Backup Only", etc. If the inspector's certification status for a part needs to be modified, the existing record should be changed, rather than having a new record created.

Does that make more sense?

I'll play around with the globals idea. Thanks for the tip! :)

[comment]

Yes, that makes perfect sense. Another way you could tackle this, is to add a new occurence of Certifications and link it to Inspectors thus:

Inspectors::InspectorID = Certifications 2::InspectorID

AND

Inspectors::gPartID = Certifications 2::PartID

Allow creations of new records in Certifications 2.

Now you can place the Status field from Certifications 2 directly on the Inspectors layout. Select a part ID in the global gPartID field, and you will see the current inspector's status for the selected part. If no status has been created, you can create it by simply typing into field (or selecting the desired status from a value list).

[eljefejb]

Hi comment -

The globals idea seems to work well, at least, for the most part.

I made an intermediate data entry layout that is shown when the user clicks the "New Record" button from the Certifications page. This gets values for the part number, sub company, and inspector, then does the check to see if a record already exists. If not, the user can click the "OK" button in the custom dialog to have the script go back to the Certifications layout and create a new record. The script then fills in the part number, company, and inspector ID into the appropriate fields of the new record, using the values from the global fields. However, the value for the inspector ID keeps being removed when the script ends! I can't figure it out! If I put a Pause script step just after the Set Field (InspID), I can see that the script *does* set the value, but when the script ends, that value gets chucked out the window!

Any ideas?

[comment]

Hard to say without seeing the file. Perhaps the field is set to auto-enter or validate?

[eljefejb]

Nope, neither. :