Unique serial number custom message if validation fails

[Anuviel]

A field defined as a unique serial number, validate always with the option of display custom message if validation fails checked.

Is it possible to get a calculation in the custom message?

I would like to display the custom message saying "This SKU is already in use by (insert name of the item that the SKU belongs to). Please assign a new SKU.

Is it possible via custom message or should I try a workaround and if yes what would be the best way to go?

Thank you.,

[Vaughan]

The custom message can only display fixed text.

However using script triggering plugins a message could be generated but that would require removing the validation from the field itself. There are other methods that involve entry into global fields, which tend to take on a Rube Goldberg complexity.

However I don't think it's worth the effort to make a custom dialog in this instance. If the validation on that field fails it means the database is generating duplicate serial numbers. This is pretty much a show-stopper.

This is most likely caused by data being imported into a clone of the file without the next serial number being updated -- this is a classic gotcha for file update processes. In this case the dialog should warn that an administrator needs to be advised and the database no longer used.

[Anuviel]

It might be that the data structure of the db is not the best (as my skill level is not that good) but I believe that it is not the issue. The field is unique because two items cannot have the same SKU. The real unique serial is not seen by the user and that is the key which is used in relationships. The SKU is a code assigned by the user to the item they create, while they can assign any SKU they want to an item (they have their way to create them first two letters of the vendor plus cost and color) two items cannot have the same SKU. I just wanted a way to let them know at the same time that the SKU they are trying to use for their item is already in use and for what item.

[Vaughan]

Oh, I mis-read the OP and assumed the field is an auto-enter serial number. My bad.

Getting the customised message is still a bit tricky; it cannot be done with the validation message.

One way is to script the entry of the SKU: pop up a custom dialog, catch the SKU into a global field, then validate the field and generate the necessary message (a self-join on SKU would be handy for this).

[Anuviel]

Thanks. Thought about two ways to do this.

Make a button "Create SKU" which brings a dialog pop-up where user creates the SKU, when they hit OK the script validates the SKU as you described, if it is unique it goes through, if it is not then it lets the user know and re-prompts him again.

Use event script and when the user leaves the field the scripts fires and if the SKU is unique nothing happens and if it is not a pop-up shows letting the user know.

Any pro or con for either way?

[Vaughan]

With an event script the field level validation needs to be turned off otherwise it'll kick in the moment the field is exited.

The custom dialog method can capture entry into a global and only modify the field if it's unique, so field level validation can be left on.

I like field level validation, it's a certain way to ensure data integrity. Not everybody agrees.

[Anuviel]

Thanks, will go with the first choice, the button way.

[Vaughan]

"Make a button "Create SKU" which brings a dialog pop-up where user creates the SKU, when they hit OK the script validates the SKU as you described, if it is unique it goes through, if it is not then it lets the user know and re-prompts him again."

The nicest way to get the re-prompting is to put the script into a loop that exits if either the field validates or the cancel button is clicked. This avoids having to call the same script multiple times which can cause infinite loops if anything goes wrong

Allow User About [ off ]

...

Set Field [ gSKU ; "" ]

Loop

Show Custom Dialog [gSKU ; 1 = Add ; 2 = Cancel ]

Exit Loop if [ Get( lastmessagechoice ) = 2 or ]

Beep

Show Custom Dialog [ The SKU is not unique. ]

End Loop

#Either SKU is unique or Cancel button was clicked.

If [ Get ( LastMessageChoice ) = 1 ]

#SKU is unique

Else

#Cancel button clicked

End If