Unique constraint


#1

How do I ensure that no two records have the same value in a given column? E.g. prevent the user from creating a second product with the same number.

I can find out if this uniqueness constraint is being violated using a lookup, but what are the places to trigger that?


#2

It seems that the “No Duplicates” option in the Properties Panel has no effect (unless I’m doing something wrong :confused:). I can get things to work by putting a procedure in the Code pane of the Properties Panel to catch a duplicate and remove it with a warning.

If arraycontains(listchoices(info("fieldname"),¶,1),«»,¶) and «» ≠ ""
    setfield info("fieldname"),""
    nsnotify "Duplicates are not allowed!"
endif

This will trap any duplicate entry for that field except a blank or empty entry. This will work for a text field but would have to adjusted if used in a numeric field.


#3

It seems that the “No Duplicates” option in the Properties Panel has no effect

I didn’t find this in the help system.

Your procedure only works when tabbing out of a field. If you hit return, the new value will already be in listchoices, so everything is considered a duplicate.


#4

Oops…I missed that because I guess I only was checking for actual duplicates and did not thoroughly check new entries. You can adjust the listchoices threshold parameter to 2 instead of 1 and it should then work.

listchoices(info("fieldname"),¶,2)

#5

You can adjust the listchoices threshold parameter to 2 instead of 1 and it should then work.

But then it will allow one duplicate when tabbing out of a field instead of hitting return. Maybe the problem is this inconsistency in when field procedures are run.


#6

Well, I’m not seeing that happen in my test database. Tab, Return & Enter all do the same thing except that the tab moves to the next field. Anyway, for what you are describing you could put a trap in the threshold parameter to check to see if it was activated by a tab and then set that parameter to 1 intsead of 2.

If arraycontains(listchoices(info("fieldname"),¶,?(info("keyboard")=tab(),1,2)),«»,¶) and «» ≠ ""
    setfield info("fieldname"),""
    nsnotify "Duplicates are not allowed!"
endif

#7

Panorama 6 included No Duplicates and No Unique options, but they would only provide warnings, they did not prevent a user from entering any value into the database. This feature hasn’t been added to Panorama X yet, mostly because I haven’t decided how to present the warning. I’m leaning towards using a notification.

Instead of Gary’s suggestion of using the listchoices( function, I would suggest using the lookuplast( function. This function skips the current record, so it will reliably tell you whether a value appears in any other record besides the current record, which is what you want in this situation. You could also use the superlookup( function with the skipcurrentrecord option.

Keep in mind, though, that this will not prevent someone from entering a duplicate value through commands in the Morph menu, or by appending data. To fully enforce unique values you would have to lock down all of these alternate methods of modifying data.