Check whether a membership number has already been assigned

[RedCrossIuK]

I have a "Members" table and there is a [Membership Number] field. The membership number is an ID and also a label. I want the user to be able to enter any five-digit member number when creating a new member record, but the entry will only be accepted if the number does not already exist. If he enters a number that has already been assigned, he should receive the message “Number not permitted - already assigned”. Can someone please help me what the Valid If code should look like for this to work? Thanks Stephan (RedCrossIuK)

[AleksiAlkio]

If your column is already set as a key, in theory you don't need any validation. Though if you want to show your own alert, modify the text from the Editor > Settings > Localization > "There is already a row with the key".

[RedCrossIuK]

Ok, so far so good, but: For example, as an initial value with UNIQUEID() I get a value "9d6ca7ce". The membership number should only be numerical, i.e. only contain numbers. How can I achieve this?

[AleksiAlkio]

Use for example a RANDBETWEEN()

[RedCrossIuK]

Hi Aleksi! Thanks for helping me!

With using:

UNIQUEID() and RANDBETWEEN(10000,99999)

I get "770b44f7" Entry invalid

[AleksiAlkio · Accepted solution]

Forget about the UNIQUEID() and use just the RANDBETWEEN(). Also.. remove your min and max values (5).