Is it possible to alert the user of the duplicate records

Hello Team
I am asking whether it is possible to alert the user that the entry they are about to save is already in the database. How do we avoid deduplication in odk-x?

Any help please. I will really appreciate

1 Like

Hi @hissdev!

If you are using ODK-X to create the identifiers (the uuid) you would never end up with two of the same uuid, so duplication would be avoided. But if you are meaning to avoid two records for the same patient, for example, that you would have to program in. For example if there is a unique identifier, you can mandate that it is entered once and only once by counting the number of times it is already in the database using async_assign (see customizing prompts here: Using ODK-X XLSX Converter — ODK-X Docs). We do something similar in making sure people record only one father in 2_2_Father’s Characteristics section of this form quest2_00_0_ELMPS.xlsx (727.8 KB)
The zcheck_father variable is assigned based on a query counting the number of fathers an individual has entered, and it must be exactly one. You could do something similar for any kind of duplication you want to check for. The key is to set up the query with the right variables that should only occur in unique combination once.


A phone number can be a good method of detecting possible duplicates.

Also remember it’s possible to point out the duplication and ask the user filling out the form to check they are not the same.

As @elmps2018 has stated it requires adding custom formulas and queries to your form.

Many many thanks @elmps2018
I am unable to access the xlsx’s.Please reload the excel files

Excel should be fixed!

Many many thanks @elmps2018
I have tried modifying the codes to mimic what I want but still am having duplicates in my database.
I have one form, one unique identifier and one table
my_unique identifier is pid which is asked once.There are no other variables that I can make it a composite unique key.
Am still getting duplicates please help review code as attached
pdemo.xlsx (11.8 KB)

Hi @hissdev! So I see your query, which looks right, but then I am not seeing any constraint. The query won’t prevent someone from creating a duplicate but it will prevent them from proceeding when they do so due to the constraint. You’ll need to add the constraint pieces like in the example I shared.

Many thanks @elmps2018
I have added the constraint but its preventing me from proceeding.
pdemo.xlsx (13.4 KB)
please help am stuck

Yes, this is the idea, it should not be able to proceed if there are multiple observations (more than one). If it’s not working properly, you’ll need to investigate what exactly the error message is. You would want to go into tables and examine the check_id variable to see if it is present and has the expected value. I don’t see a model tab, and assigned variables need to be defined in the model table with their type to work correctly – that may be one of the problems.

1 Like

Agree with @elmps2018.

Also you might need to design the behavior into the form to help steer the user to fix the problem if not obvious to the user what to do. I often use an “if” with a prompt that explains what to do if the constraint check is invalid.

1 Like

Many thanks @elmps2018 and @W_Brunette
Have followed as example given by @elmps2018 but still having duplicates
Any other example help with be much appreciated

pdemo.xlsx (13.6 KB)

The latest form you shared doesn’t seem to have any constraints. Without the constraint it will allow duplicates. Simply counting the number of forms (which is what the form currently does), does not prevent having multiple forms. I know you were having problems with the constraint always binding. What do you see for that variable in tables?

Many thanks. pdemo.xlsx (13.6 KB) I did remove the constraint because it was preventing me from proceeding.
The variable records nothing in the table

Exactly. My headache is with the constraint binding

If nothing is recorded in the table for the count_pid variable, then there must be an issue somewhere with that getting created, that’s why the constraint always binds. It looks like for one problem you are missing the prompt_types tab and there may also be an issue with your customPromptTypes.js file. Please review the customizing prompts section here: Using ODK-X XLSX Converter — ODK-X Docs and make sure you have all those pieces in place.

Hello Team,
Am still stuck with this, who has another code we try out.
Have tried as advised by @elmps2018 and am still stuck
Any help I will appreciate

If you can share your current .xlsx and customPromptTypes.js file that will help folks identify the issue.

Many thanks again @elmps2018
Attached are the files
pdemo_refined.xlsx (29.4 KB) (2.1 KB)

So a few things:

  1. check_pid, the variable you are using to constrain, is not in your model statement (tab). It won’t exist in your table as a result, and therefore can never be 1. It needs to be added to your model statement, and defined as a type (integer) that is compatible with async_assign_count. It may be helpful to review the Customizing prompts/async_assign section here: Using ODK-X XLSX Converter — ODK-X Docs where it reviews the need to update your model statement.

  2. It looks like you are querying a different form, CBS_Case_Report, that is not referenced anywhere else, so the problem could be coming from an issue in that form. If #1 doesn’t solve problems please share this form as well.

  3. Another problem is that you did not update the path to be to your form in the custompromptstypes file. It still says my: templatePath: ‘…/config/tables/quest1_02_0/forms/quest1_02_0/templates/async_assign.handlebars’,
    You can use the one here: Using ODK-X XLSX Converter — ODK-X Docs to automatically get the table or update the quest1_02_0 to pdemo

1 Like

Many thanks @elmps2018
Am able to follow your guidance and am now getting somewhere. Now am getting undefined field after the check_pid that wont allow me procede until I insert 1.It should count and store in the database the value rather than allow the user to insert a value to it
Many thanks once again
Am I referencing this correctly. I have a unique identify pid and am using async-assign-count on another variable check_pid which again constraints

1 Like