Verify value is unique before continuing multi step form.

[poirazis]

I have a multi step form, where in the first step the user enters the Contact's mobile number.
I want to verify, that that phone number is not already registered in the DB (MySQL) before allowing him to continue to the next steps of the Form.

My thought was to insert a Data Provider, filtered on the Form Field value, and if it has 0 records it means the value is unique.

Any ideas on how to achieve this using the above, or some other method ?
Is there a method to use the Validation with the Constrain being "Unique" or a hack-ish way ?

Thank you in advance

[melohagan]

Hi @poirazis

Definitely a common use case! I had a go and I think this works reasonable well:

1. Add a form with a Data Provider, form fields and save button
   
2. The Save button will have the usual Validate Form and Save Row actions
3. Add a filter binding to the Data Provider as follows:
   
4. Configure validation on the 'Value' field (the form field you want to ensure is unique)
   

JavaScript

return $("New Data Provider.Rows Length") === 0 ? $("Form.Fields.Value") : "NOT_UNIQUE";

[poirazis]

Thank you so much @melohagan 🙏

[poirazis]

Hey @melohagan
I "think" we may have found a bug here, with the timing of the events firing.
Following the above approach, I have set the Input field "On Change" actions to "Refresh Provider" and then "Validate Form ( Current Step only)".

It seems that the Validation Rules are checked BEFORE the "On Change" defined actions, which leads to the below behavior.
I change the value in the Input Field
I press Enter / Tab
Data Provider gets refreshed but he Validation isnt applied
If i click on / off the field again, THEN the validation is applied.

Screen.Recording.2022-06-14.at.12.52.04.PM.mov

[melohagan]

Hey @poirazis
I noticed some issues with OnChange myself for this particular case, but in the example above the OnChange event with the Refresh Data Provider action wasn't actually needed.
This is because when a Data Provider has a filter on a binding, it will automatically refresh when that binding value changes.

[poirazis]

I know @melohagan , i just used it to make sure
The behavior is the same with the Refresh DP action removed.
The "On Change" -> "Validate Form (Current Step Only)" is still misfiring and you need to get focus / lose focus twice.

[melohagan]

The OnChange is really an OnBlur/On loss of focus for text inputs.

Because of the async nature of the refresh data provider action. I think there'll need to be an option to wait for the refresh to complete.
I've raised a feature request here: #6303

[JCV67]

Hello @melohagan
I have the same need to validate date fields, I dis as you explained for the new/row and it worked fine. Now I'm using the same logic to validate the /:id form but does not work. Looks like the result of the function is always TRUE, no matter the date and time I choose.
Can you help me
Thank you

[poirazis]

Hey @JCV67
I actually ended up implementing it another way, more straight forward I think.
What i do to Verify the value entered is unique before proceeding ( I am using MySQL )

Create a Form, Mode : Update
Insert Text Field -> name : "phone"
Text Field -> OnChange ->

1. Validate Form - do client side validation of the input
2. Execute Query that takes a binding ( SELECT count(*) as Matches FROM contacts WHERE phoneNumber = {{ inputPhone }} )
3. Set the above Query Binding to {{ Field.Value }} ( the new value inputted into the text filed )
4. Update State -> set UniquePhone to JS { return $("Action 2.Query result.data.length") < 1 }

The above setup will update State.UniquePhone whenever the Text Input looses focus, and you can use a Paragraph with Conditional UI to show the error message.

Hope this helps!

[JCV67]

Hi @poirazis,
Thank you for your fast reply. I am using internal tables only, so SQL won't work for me. The method that @melohagan suggested worked well for record creation but it is not working for changes in the existing records. I'm almost sure that it is my fault, but I can't find the error.
I'll try to adapt your SQL logic
Thank you

[JCV67]

Hello,
Just to say that it worked on both, record creation and changes in the existing records. My problem was with the Data Provider location in the Screen objects tree.
Thank you

[Bewo]

Hi @JCV67,
I have the same problem as you described above. The condition always seems to be true. In which location you set your data provider now?

It's probably just a small error but I just can't get it to work for me since hours... :-(

Thanks!

[JCV67]

Just look at this:

and this

Filterind is on Data Provider

[Bewo]

Thanks @JCV67 for your fast reply! But i didn't get working the solution from @melohagan. I can't find the reason.
If changed the structure, the filters, the actions.... and so on... it doesn't work. If a value is inserted, it's always "not unique".

For testing i've added a number field, which is updated with the number of rows from the data set provider. Triggerd on change of the field which should be unique. The number of rows is correct. And the table shows the right row, or no row if it's unique. In my test-db are only 6 rows. See in the video below.

Bildschirmaufnahme.2024-01-06.um.16.36.13.mov

The validation rules are:

The Javascript is:
return $("Zugriff Datenbestand.Rows Length") === 0 ? $("Eingabeformular.Fields.KW_Zuordnung") : "NOT_UNIQUE";

Can anybody gave me an hint?