Sometimes it is not appropriate to make a field required, as you may not initially have the required information. However, it may be important to your business work flow that this information is filled in before you can proceed on to the next step of your process. For example, it would not be appropriate to make the 'Finance Contact' field on an Opportunity required, as you are unlikely to ask for this until you have won the deal. However, at that point, you need to know the 'Finance Contact' so that you can send the Order to the right person.

If you would just like a report that shows all records where one of the fields is blank, then you can build a simple report with a criteria for field is blank. However, if you want to find out if one or more of many fields is blank, then you will need to add a calculated column to your report: 

Step 1: Create a report based on the relevant record type

In our example, we want to see when the 'Finance Contact' and/or 'Finance Email' field is blank on an Opportunity, so will start the report from Opportunities. Add the columns that will help you to identify the affected record, like Object Reference, Name and Assigned To.

Step 2: Add a calculated column

This calculated column will be the flag that shows whether either or both of the fields have not been completed. 'Finance Email' is a text field, and 'Finance Contact' is a DLI, so the 'is blank' check will be slightly different for both.

For 'Finance Contact', we will drill through to the Id - if there is no Finance Contact then there will be no Id and so we use IS NULL:

IF( linked_item_model_association_for_cf_opportunity_finance_contact.id IS NULL, 1, 0 )

i.e. if there is no Finance Contact then return a 1, else return a 0.

For 'Finance Email', we need to check if it is null or blank. We can do this one of 2 ways:

IF( cf_opportunity_finance_email IS NULL OR cf_opportunity_finance_email = "", 1, 0 )

or

IS_BLANK( cf_opportunity_finance_email )

i.e. if there is no Finance Email then return a 1, else return a 0.

Then we can merge the formulas together:

IF( linked_item_model_association_for_cf_opportunity_finance_contact.id IS NULL OR (cf_opportunity_finance_email IS NULL OR cf_opportunity_finance_email = "" ), 1, 0 )

or

IF( linked_item_model_association_for_cf_opportunity_finance_contact.id IS NULL OR IS_BLANK( cf_opportunity_finance_email ), 1, 0)

i.e. return a 1 if either or both of the Finance Contact and Finance Email are blank, return a 0 if they are both completed.

Make sure you keep all of the brackets as these ensure that the logic is correct.

Step 3: Add criteria

We are only interested in the Opportunities that are in the 'closing' stage, so can add a criteria for 'Stage equals "Stage 3 - Closing"'. We also need to add a calculated criteria to return only those rows where the calculated column returns a 1.

You will now have a report of your closing Opportunities which have not yet been fully filled in. You can use this to chase up with each individual to ask them to fill it in, or can send it as a scheduled email. 

Add a report cell to your records...

You can take this even further by making it obvious from the record itself that something needs to be filled, by adding a report cell. This can use the same report, however a couple of tweaks are required:

Step 1: Remove calculated criteria

Remove the calculated criteria that was restricting the report to only show those where the 'is blank?' formula output a 1. This means that on any records where the fields have been filled in correctly, the Report Cell can output a statement like 'All required fields are completed', rather than showing 'No results'.

Step 2: Add another calculated column

This will return a statement which will make it clear to the User as to what they need to fill in:

CASE
WHEN  linked_item_model_association_for_cf_opportunity_finance_contact.id IS NULL 
AND  !IS_BLANK(cf_opportunity_finance_email) THEN 'Finance Contact is blank'
WHEN  linked_item_model_association_for_cf_opportunity_finance_contact.id IS NOT NULL 
AND IS_BLANK(cf_opportunity_finance_email) THEN 'Finance Email is blank'
WHEN  linked_item_model_association_for_cf_opportunity_finance_contact.id IS NULL 
AND IS_BLANK(cf_opportunity_finance_email) THEN 'Finance Contact and Finance Email are blank'
ELSE 'All required fields are completed'
END

Step 3: Add CSS Styling

You can add traffic light colours so that it stands out to the User that they need to fill in some fields. You'll need to add another Calculated Column and your formula will be the same as above, but instead of outputting a statement, you will output the CSS styling colours:

CASE
WHEN  linked_item_model_association_for_cf_opportunity_finance_contact.id IS NULL 
AND  !IS_BLANK(cf_opportunity_finance_email) THEN 'background-color:yellow;color:black'
WHEN  linked_item_model_association_for_cf_opportunity_finance_contact.id IS NOT NULL 
AND IS_BLANK(cf_opportunity_finance_email) THEN 'background-color:yellow; color:black'
WHEN  linked_item_model_association_for_cf_opportunity_finance_contact.id IS NULL 
AND IS_BLANK(cf_opportunity_finance_email) THEN 'background-color:red; color:white'
ELSE 'background-color:green; color:white'
END

Then open your 'statement' calculated column, go to the Style tab, apply 'Calculated conditional styling' and choose your new CSS Styling calculated column:

Your new report should look like this:

Step 4: Create Report Cell field

On the relevant Record Type, create a new custom field of type 'Report Cell' and set it up with your new report:

Amend your Form Layout so that the report cell shows in a sensible place - in this example we only want the report cell to show on Opportunities where the Stage is "Stage 3 - Closing'.