Hi,
I’m looking for best practices or practical recommendations on what to do here. Many thanks in advance.
I have to manage PERSON records in a database table that keeps only one record per individual. Each of these has or may have a value in some fields that uniquely identify them, like for example a club membership number that may or may not be assigned at the time the record is entered in the database. My application does not control the numbers that are assigned to each individual, just keeps to use them later.
I would like to ensure that when the application user edits a PERSON’s record, that the number they put in PERSON.MembershipNumber has not been already assigned to someone else.
I tried using the Validator but did not work and upon further reading I realized that the documentation says to use it when you want to insert a new record, not when you want to update an existing one.
From a performance perspective, perhaps the best is to mark the field as unique in the database table and let the RDBMS engine handle the check. This way, if the User input say Alice’s MembershipNumber in Robert’s record and then tries to save Robert’s record, the RDBMS would not be allow the operation. This is good, but I don’t know how to convey to the user the fact that the operation failed because the MembershipNumber was already in use by Alice.
I was thinking that another way to accomplish this would be to fire a search from the page form when the user leaves the Membership Number field (so the check happens before they hit the submit button) but I have no idea on how to do that. I don’t like the idea of having a “Check if this number is not already in use” button associated to the field because if the user did not click it, then the record could be updated with a duplicate Membership Number.
Any guidance on the above will be greatly appreciated!
Say that now I bring up the page that edits a record.
If I change another field and leave the watched field unchanged, when I hit the Save button, the validator does not let the update go. See below:
The validation checks for the presence if a value so if it is present as in an update the action will fail. You need to manually check for the presence of the record and then conditionally run an update or insert action
What Brian says is absolutely right.
I didn't think of this case (when you save the same value) on updating the record. In this case it will fail, as the value is already in the database.
Your case is similar as this, so the solution will be the same:
Many thanks. Now, I think I s/b able to do this, except for the part in which I inform the user the reason when the update fails. Pointers on that would be appreciated as well.
Also, if I wanted to have the RDBMS do the checks (by identifying the table column as unique), is there any way to capture the message when is not and pass it back to the user? [Note: it would probably be a better design to have the RDBMS do that check than having a server action do them, given that we never know if the record will always be updated using the front end of my app. ]
Properly written there should not be a failure. Firstly check if the record exists. This could be via a query or a simple check of the appropriate post value assuming it will be zero if an insert. Then just use a conditional to run an insert if not present or update if it is. Either way the action would be successful.
Thank you Brian, but what I meant is this:
If the value is already in use, I’d like not only to not do the update but also to tell the user that the update is not being done because the value is used elsewhere (and ideally, “who” is using it, i.e. something about the other record). How do I do that, in a manner similar to the way the Validator notifies that it failed with a message under the input field in the page?
