Tips when using forms: Validation

Hello everyone,

Last week, I posted some tips about filtering and styling forms, so if you haven’t seen them yet, check them out here!

Over the past few days, I’ve been working on a doc and made some additional discoveries that I’d like to share with you. Until now, I was struggling with the validation formula to prevent duplicates.

Avoiding Duplicate Entries in a Form

There are many use cases where you need to check if a value already exists before submitting a form. The most obvious approach in the validation formula would be:

Not([DB_Table].[Column].contains(currentValue))

However, this throws an error because referencing the same column creates a circular reference.

Common Workaround (and its issue)

A known workaround is to create an additional column that duplicates the original column’s values, then reference this new column in the validation formula. While this works within a normal doc, it does not work in published docs—likely due to references not being passed properly to the public link. Note in the image below that, in the native form, the validation correctly flags the current value as invalid, whereas in the published form, it does not.

Alternative Workaround (Using a Helper Table)

The workaround I discovered involves creating a helper table that stores a list of all values to be checked during form submission.

  • If you only need to check one field, the helper table can have a single column.
  • If you need to check multiple fields, the helper table should have two columns:
    • The first column acts as an identifier (e.g., Row ID).
    • The second column contains the list of values to be validated.

Example formula in the helper table:

Switch([name],
   "codes", [DB_Table].[CodeColumn],
   "names", [DB_Table].[NameColumn]
)

Linking the Tables

In your main table (DB_Table in this example), you need to create a relation column linking it to the helper table. This relation is not used for data retrieval—it exists solely to establish a connection so that the “Include referenced tables” toggle in the form privacy settings allows access to the helper table.

Final Validation Formula

In the form’s validation formula, you must now check against the helper table instead of the main table.

  • Since Filter() does not work in published forms (based on my tests), you must manually select the row from the helper table using Nth(), First(), or Last().

Final validation formula:

Not([Helper_Table].Nth(1).[Value].contains(currentValue))

If everything is set up correctly, the validation will work even in embedded and published forms.

or me, this workaround was especially beneficial because I needed a way to both prevent duplicates and pre-fill the form.

Before this workaround, I couldn’t do both simultaneously—I either used the native form to prevent duplicates or the published form to pre-fill fields using the URL feature.

I’ve added this test to my demo doc. Feel free to copy it and explore the workaround.

Let me know if this is useful!
In my next post, I’ll talk about how I add new entries to lists within a form.

Best regards,
Arnhold

7 Likes

This is awesome, Arnhold! Thank you so much for the contribution.

Could you please add a “Copy This Doc” button to you example doc so we can play with it?

Thanks!

1 Like

Hi @Tony_Harion

Sorry, I forgot that I hadn’t published the doc.
I’ve now added the button.

Thank you,
Arnhold

3 Likes

@Felipe_Arnhold, once again I applaud your contribution to the community.
An excellent post explaining the workaround extremely well.
Well done.

1 Like

Great contribution! Thanks for sharing!!!

1 Like