[SOLVED] Format field and check for duplicate values

Hi
We do online training, we require individuals to enter an ID number which is then added to their certificate, what has been happening is that when a person starts at a company their employer requires them to register with us for training, when the person resigns and moves to another company instead of just updating their details on our website, they create a new account, which is causing records to be split over multiple profiles.I want to add validation to the ID field so that the system automatically checks the database to ensure the ID number does not yet exist. I also want to automatically remove any spaces that they may add between digits.I am not sure what would be the best way to run this check

To prevent spaces add a validation rule to your ID field to only accept a alphanumeric string. If it's a text field the included "Single Alphanumeric Word (letters, numbers, and underscores only)" validation rule should work if you allow underscores. If you only allow letters and numbers you can use the below.

Parameters > Validation
Validation Rule: Custom REGEXP Validation
Custom REGEXP Validation: /^[a-zA-Z0-9]$/

As for checking if an ID was already used you'll need CB Query Field for that. It provides Query Validation to validate a field using a custom database query. Example as follows.

Integrations > CB Query Field > Validation
Query Validation: Enable
Query: Validate On: Empty Results
Ajax Validation: Enable

Replace FIELD_NAME with the name of your field. That should cause it to fail validation if the id is already in use.

Hi
Thanks for the feedback.
I have made the changes as recommended, on Integrations > CB Query Field > Validation I have added the code, when you click submit it does the check and validates the field as expected, the part I am not getting right however is even though I have set Ajax Validation to enabled, in only validates on submit and not dynamically.
Am I missing something?

ialearn wrote:

Hi
Thanks for the feedback.
I have made the changes as recommended, on Integrations > CB Query Field > Validation I have added the code, when you click submit it does the check and validates the field as expected, the part I am not getting right however is even though I have set Ajax Validation to enabled, in only validates on submit and not dynamically.
Am I missing something?


 

I have done more tests, the Ajax Validation seems to work as long as I don't add regex validation to the field.
I don't specifically mind if users add a space to their ID number, the only reason I am wanting to remove spaces is so that the Query can validate the field since it does not pick us 1234 and 12 34 as the same value. If the query can strip "space" from the field when running a comparison that would also work.

I have another related question.
Is it possible to create a condition on a field so that it only shows if SELECT `id` FROM `#__comprofiler` WHERE `FIELD_NAME` = '[value]' passes validation

You should be able to use both regexp validation and the ajax query validation. By on submit do you mean its erroring and sending back to the form or is it preventing the form from submitting?

You should be able to use both regexp validation and the ajax query validation. By on submit do you mean its erroring and sending back to the form or is it preventing the form from submitting?

I mean the ajax is supposed to run the query while the user is busy on the form, if I remove regex it does just that as soon as the user clicks on another field the query runs and it gives the result as it should, as soon as I add the regex, from what I can see the regex take precedence and does it's test and ignores the query, the query is then only processed when the clicks submit and not live.