Find and Mark Empty Fields as Incomplete
Overview
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.
To add your condition for empty values, click on Add 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’.
Then choose the status condition. You can choose Is Null to display empty fields. This example uses ‘MaxQty’.
Then name your field. For empty fields causing incomplete reference records, we have named it ‘Incomplete’ in this example.
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.
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.
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.
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.
Add a true step. Then choose Send Email.
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.
After you have run this rule again, it will check for incomplete records and notify your chosen audience of these records.