Find and Mark Empty Fields as Incomplete


A reference rule is used to monitor and ensure that your reference data is complete and quantifies which fields are missing. You can then add and fill in any missing fields.

By default, a Reference rule has statuses set to ‘Active’ and ‘Inactive’ but once you have created your custom fields, you can drive data stewardship actions off these fields. You can create a status based on whether column values are empty and add multiple criteria for a single status, for example the status ‘Incomplete’ for all records with empty values.

How to Apply Statuses to Find Missing Values

Under Record Status, you can create a status where if the value of a column is empty, the status of the record will be ‘Incomplete’. You can continue to add multiple conditions for other columns and custom fields that can all contribute to the one status. So, if a column named ‘Type’ is empty, it will contribute to the ‘Incomplete’ status and if we also add if ‘MaxQty’ is empty then its status will also be ’Incomplete’.

Edit your reference and click on the Record Status tab so you can add new statuses. It will show you the existing status conditions.

Record status tab

To add your condition for empty values, click on Add condition.

Add a condition

In the first dropdown, choose the column that this condition will apply to. If there are any empty values in this column, it will be marked as ‘Incomplete’.

Pick a column

Then choose the status condition. You can choose Is Null to display empty fields. This example uses ‘MaxQty’.

Pick a condition

Then name your field. For empty fields causing incomplete reference records, we have named it ‘Incomplete’ in this example.

Name the status

Now that you have your status condition ready, you can run this rule to assign this status to all records with incomplete fields.

You can also keep adding new conditions and also name them ‘Incomplete’ to apply empty values from multiple columns in the one ‘Incomplete’ status.

Run the rule

After it has been run, you can see that any fields with empty values in your chosen columns will have the status ‘Incomplete’. Learn more about Record Status actions here.

Updated statuses

Drive Further Action using Communication Rules

Edit the reference and click on the Communication Rules tab.

Create your first step and then choose Branching Logic.

This branch will cause an email to be sent to your chosen users when the condition has been met.

Communication rules tab

For this branch, you can choose the column ‘Status’ and the condition ‘Equals’ with the value ‘Incomplete’ so when the status is incomplete it will trigger an email to be sent to your chosen audience.

Communication branch conditions

Add a true step. Then choose Send Email.

Communication branch true step

Choose your email type, and choose the recipients of this email. (You can choose to add email addresses manually or use an email column in your results table.)

Next, provide the content of this email that will inform your users they have incomplete reference records. Learn more about communication rules here.

Communication email details

After you have run this rule again, it will check for incomplete records and notify your chosen audience of these records.