Specifying a primary key

Specify a primary key field for a Data Analytic to uniquely identify records in the table. When a table has a primary key, you can export records from Analytics to the table and update existing records.

If a table does not have a primary key, exported records from Analytics are appended to the bottom of the table, or they completely overwrite the table, depending the Overwrite table setting in Analytics. For more information, see Exporting exceptions to Diligent One Results.

How it works

Setting up the primary key field

Once you create a Data Analytic and import records into the table, you can select one of the imported fields as a primary key field. The values in the primary key field must be unique, and this field is used to uniquely identify each record in the table.

Note

When specifying a primary key field for a Data Analytic, you must select an imported field. Questionnaire responses and information columns cannot be used as primary keys because they are not unique. You cannot set primary key fields on Surveys or Event Reports.

Updating the table

After the primary key field is set, subsequent exports from Analytics can update individual records in the table:

match if a matching value exists in the primary key field and the corresponding field exported from Analytics, the existing record is updated with the values present in the exported record
Note
Only fields that are present in the exported record are updated in the existing record. If the exported fields are a subset of the fields in the existing record, the existing record is only partially updated.
no match if a matching value does not exist in the primary key field and the corresponding field exported from Analytics, the existing record is not updated and the exported record is appended to the table

If Results cannot update the table with the data from Analytics, an email notification is sent to the person who performed the export.

Example: updating a record

Data Analytic in Results

You have an existing record in Results. The Invoice ID field is set as the primary key field, and the primary key value for the record is "132":

Invoice ID	Data 1	Data 2	Data 3	Questionnaire 1	Questionnaire 2
132	A	B		Answer 1	Answer 2

Analytics table in Analytics

In Analytics, you have a table that contains a record with an Invoice ID field and fields for Data 1 and Data 3. It also contains a field not present in the Data Analytic called Data 4:

Invoice ID	Data 1	Data 3	Data 4
132	B	D	New

The value in the Invoice ID field matches the value of the Invoice ID field in the Data Analytic in Results. When you export this data from Analytics to the Data Analytic in Results:

the values in Data 1 and Data 3 are updated
the value in Data 2 is unchanged
the Data 4 field is added to the Data Analytic

Data Analytic in Results after running the export

After the export runs in Analytics, the record is updated:

Invoice ID	Data 1	Data 2	Data 3	Data 4	Questionnaire 1	Questionnaire 2
132	B	B	D	New	Answer 1	Answer 2

Overwriting when exporting

The Overwrite table export option in Analytics deletes existing data in the Results Data Analytic before adding the exported data to the table. To update individual records in Results, do not select the Overwrite table option.

If you select Overwrite table, the following occurs to the primary key field:

if the exported data contains the same field name as the current primary key, the primary key is retained regardless of the exported field's data type
if the exported data does not contain the same field name as the current primary key, the primary key is unset and no primary key field is used

Specify a primary key field