Validating that the data in a field is unique

[JoniS]

Here's a little bit of background on a section of a FileMaker solution that I'm working:

I have a table that holds all loan-able equipment (like a library but not with books).

Some equipment has serial numbers, like external hard drives. And some equipment does not, like chairs. (Don't ask me why. I think they should have serial numbers for everything, even if they need to make them up, but I'm not in charge.)

In order to accomodate such different equipment in the table, I have two fields:

Serial Number, and

Quantity

Now, here's what I want to figure out how to do.

Since the Serial Number field isn't mandatory, I can't make it a unique ID. BUT, I still need to make sure that when a piece of equipment DOES have a Serial Number, it is not a duplicate of a Serial Number already in the table.

Is there a way to validate this during the user's data entry via the "validation calculation"? I'm not sure how to loop through and check all existing Serial Numbers to make sure the new entry is unique (but to disregard when the value is null, of course).

Thank you for any help you can give in this matter.

[mr_vodka]

To answer your second question first, See here.

For your first question, you can have the users enter data into a global field and upon hitting a button, a script would run to check if that serial already exists in the table.

[JoniS]

mr_vodka,

I tried creating a button next to the Serial Number field for testing purposes, and created a script nearly exactly like the one you suggested, except instead of deleting records, mine pops up a dialog window when it finds a duplicate.

Here is mine:

Sort Records [ Restore; No Dialog]

Go to Record/Request/Page [ First ]

Set Variable [ $DupCheck; Value:GFE::SN ]

Go to Record/Request/Page [ Next; Exit after last]

Loop

If [ Value:GFE::SN = $DupCheck ]

Show Custom Dialog ["Warning! Duplicate Serial Number found!"]

Exit Loop If [ get(recordnumber) = get(foundcount) ]

Else

Set Variable [ $DupCheck; Value:GFE::SN ]

Go to Record/Request/Page [ Next; Exit after last]

End If

End Loop

The script is not quite working as it should for me--very buggy. If I put in a new serial number and click the button to check for duplicates, nothing happens, except it seems to jump around to different records. If I put in a duplicate serial number and click the button, my dialog box comes up, but then it is stuck in an infinite loop. If I hit "ok" or "cancel," nothing happens and I have to crash FileMaker to get out of it. Could you please explain further why each of these lines are needed? I'm worse than beginner level at scripting. I don't understand why I need to sort, and I don't know where the variables "recordnumber" and "foundcount" are coming from...