Check if response is unique

You can determine whether a response is unique within a project by using an SQL field type and then altering workflow behavior based on the result.

This approach is especially useful for public surveys, such as registration forms, where you may want to prevent multiple submissions using the same email address (or any other unique identifier).

The method works for both instruments and surveys.

[email]

This variable is the one that should be checked for uniqueness.

• Email is used in this example because it was the original use case
• Any field (username, ID, phone number, etc.) can be used instead

[existing_email]

This variable will take a value of 1 if the value of [email] exists in another record within the project.

select 1, 'exists' from [data-table] 
  where project_id = [project-id] and field_name = 'email' 
    and value = (select value from [data-table] 
               where project_id = [project-id] and field_name = 'email' and record = [record-name]) 
    and record != [record-name]  limit 1

@SETVALUE="1"

• The SQL query searches the project for any other record with the same email value using the [data-table], [project-id], and [record-name] smart variables.
• If a match is found, the SQL field returns a row
• @SETVALUE=“1” sets [existing_email] to 1
• If no matching record exists, the field remains blank or null

The [existing_email] field must exist on a form after the form containing [email].

This is required because:

• The [email] value must be saved to the database
• SQL fields query stored data, not unsaved form values

Using a calculation or logic based on [existing_email]:

• You can display a custom message informing the user the email already exists
• You can stop progression through the workflow
• You can branch form display logic (instruments)
• You can control the Survey Queue (surveys)

This allows you to prevent duplicate submissions while keeping the user experience clear and intentional.