[SOLVED] Format field and check for duplicate values

1 year 4 months ago - 1 year 4 months ago #331767 by ialearn
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

Please Log in to join the conversation.

1 year 4 months ago #331773 by krileon
Replied by krileon on topic Format field and check for duplicate values
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:
SELECT `id` FROM `#__comprofiler` WHERE `FIELD_NAME` = '[value]'
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.


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

1 year 3 months ago #331792 by ialearn
Replied by ialearn on topic Format field and check for duplicate values
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?

Please Log in to join the conversation.

1 year 3 months ago - 1 year 3 months ago #331793 by ialearn
Replied by ialearn on topic Format field and check for duplicate values

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

Please Log in to join the conversation.

1 year 3 months ago #331807 by krileon
Replied by krileon on topic Format field and check for duplicate values
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?


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

1 year 3 months ago #331822 by ialearn
Replied by ialearn on topic Format field and check for duplicate values

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.

Please Log in to join the conversation.

Moderators: beatnantkrileon
Time to create page: 0.217 seconds

Facebook Twitter LinkedIn