Failed publishing due to missing unique ID field – SurveyCTO Support Center

When you have dataset publishing set up, you may come across an error like this:

There are X form submissions that are queued to be published into server datasets but they are stuck due to errors.

When you click on that error, this may appear:

Publishing from the form with ID [form ID] has been suspended. Submission [KEY] cannot be accepted: Row insertion failed; the unique record field [unique ID field] is required. Update the publishing configuration or un-approve, correct, and re-approve the submission using the review and corrections workflow.

When this error appears, it means there are invalid submissions that cannot be published to the server dataset. Submissions like this can also slow down your server, slowing down dataset publishing overall.

Cause

Short explanation

This error is caused when the field that publishes to the Unique ID field does not have a value. Since the server dataset cannot identify which row should be updated, this error occurs.

Detailed explanation

Some server datasets have a Unique ID field. This is the dataset column used to identify rows in the server dataset. Every value in this column should be unique. The Unique ID field cannot be changed once it is set. Once a Unique ID field is set, all rows of the server dataset must have a value for the Unique ID field; it can never be blank.

The Unique ID field property of server datasets is used in offline dataset publishing, and it is required when offline dataset publishing is turned on for that server dataset. Setting a Unique ID field is encouraged, but it is only required if your workflow uses offline dataset publishing.

When you create a cases dataset or an enumerator dataset, the Unique ID field is automatically set to the 'id' column. Otherwise, you can set the Unique ID field manually.

Whenever you publish to a server dataset with a Unique ID field, the server dataset will check which form field publishes to the Unique ID field. However, if that field does not have a value, the data cannot be published, since there is no data for the required Unique ID field, and therefore the error occurs.

Resolving the error

When resolving the error, you need to both fix the immediate issue of the submission not containing a unique ID, and also create a long-term fix so this does not happen again.

No matter what, you will need to identify the form field that publishes to the Unique ID field. We call this the "unique ID form field".

Find the unique ID form field

At the top of the server console, click Alerts (the triangle with an exclamation point).
Go to the error message related to the server dataset you are publishing to (don't click on it).
Note the form ID of the form causing the error, as well as the dataset ID of the server dataset that has the error.
On the Design tab of your server console, go to the server dataset with the error, and click Settings.
Note the Unique ID field. Close the Settings once you note it.
For that server dataset, click Publish into.
Go to the form noted in step 3, and click Edit.
Note which form field publishes to the Unique ID field noted in step 5. This is the unique ID form field.
Make sure the Form field to identify unique records is the unique ID form field. Click Save if you update it.

Immediate fix

Use the review and corrections workflow to give the unique ID form field a value.

Go to the Monitor tab of your server console.
Scroll to the form noted in Find the unique ID form field step 3.
Click Review workflow. Make sure Enable review and correction workflow for this form? Is set to YES, and make sure Un-approve and un-reject is set to ON. Click Save if you make changes.
Click Monitor form data, make sure All Submissions and Approved are selected (it is fine if Awaiting approval and Rejected are also selected) and click Start Data Explorer. The Data Explorer will open in a new tab.
Go to the submission causing the error. There are a few ways to do this:
1. If the error message mentioned the KEY of the submission with the issue (it will start with "uuid:"), click Search, and search for that KEY.
2. Export your form data, and find the submissions with a blank value for the unique ID form field, noting their KEY. Then, search for them in the Data Explorer using the Search button.
3. On the left, click the blue number to the right of available. Go through each submission until you find one that has a blank value for the unique ID form field.
Click Click here to un-approve, then at the popup, click Un-approve submission.
Hover over the unique ID form field, and click the Edit button (the pencil icon).
Give that field a value, then click the checkmark to confirm the change.

If you cannot edit the field because it was not relevant, then it cannot be published to the server dataset at all. You will have to purge that submission instead. First, it is a good idea to update your form definition so the unique ID form field will be relevant (see Long-term fix below), then fill out the form again, re-entering the data. Once the data has been re-submitted successfully, you can safely purge the old submission.

Click Flagged and held for review (not yet released), classify the submission, and click Approve.
At the popup, click Approve.
Click the red Save changes button. The updated submission should be published within the next 5 minutes or so.
Repeat steps 5-11 for all submissions that are also missing a value for the unique ID form field.
Return to the main server console (which has the Design, Collect, and other tabs), and at the top, click Alerts (the triangle with an exclamation point).
Click the alert that says something like "There are X form submissions that are queued to be published into server datasets but they are stuck due to errors."
At the popup that appears, click Retry all.

Long-term fix

Update the form definition noted in Find the unique ID form field so that it cannot be submitted without a value for the unique ID form field. Once the updated form has been deployed, make sure all enumerators download the latest form definition from Get Blank Form, so they will fill out the new, fixed form (we highly recommend configuring your devices so that forms always update when opened if there's an Internet connection).

Depending on the nature of your unique ID form field, there are several different methods you can use:

Ensuring caseid unique ID fields have values

To make sure the form is always opened from a case, add a note field with a required value of "yes", and a relevance expression so it will only be relevant if the caseid field is blank. A required field must have a value, but a note field cannot have a value, so as long as that field is relevant, the enumerator will not be able to move on from that field, and the form cannot be submitted.

Here is an example of what that field can look like:

type	name	label	relevance	required
note	no_caseid	Please close this form without saving, and open it from the Manage Cases menu.	empty(${caseid})	yes

This should be the first visible field in the form. This way, if the form is not opened from a case (and therefore the field "caseid" will not have a value, and so there is no value to publish to the Unique ID field), the enumerator will be forced to close the form, and open it from the Manage Cases menu instead, so the field "caseid" will have a value.

Ensuring hidden unique ID form fields have values

If your unique ID form field is a hidden field such as a calculate field, make sure its calculation and your workflow is set up so that the field always has a value. For example, if the unique ID form field uses the pulldata() function, make sure there is data for all possible lookup values.

It can also help to use the required note method from Ensuring caseid unique ID fields have values above, but instead of referencing "caseid", reference the unique ID form field. That way, if the enumerator enters values that lead to a blank unique ID form field (e.g. they enter a lookup value that does not exist), they will see the note field as a warning, and they will be forced to go back and enter valid data.

Also, make sure the field is always relevant.

Ensuring visible unique ID fields have values

If your unique ID field is a visible field, such as a text field or an integer field, make sure that field is required, so the field must have a value before the form is submitted.