Reporting to identify blank or missing data
Hello!
I am wanting to create a report that easily identifies missing data/blank fields in my app. Most of these are text fields, but we have some numeric and date fields as well. In short, we want to monitor that data is being effectively entered without making every field mandatory.
I would love if I could just run a summary report based on blank values on the specified fields to generate something similar to the dummy table below, but I have been unable to figure it out as currently configured.
| Field | Qty Missing |
| Client POC - Name | XX |
| Client POC - Email | XX |
| Client POC - Phone | XX |
| Adjuster - Name | XX |
| Adjuster - Phone | XX |
| Adjuster - Email | XX |
Any help is much appreciated!
Thank you,
Making fields required can be a problem for the user if it causes them to not be able to save the record if they are legitimately missing information.
So one alternative is to create a formula which is a warning message for all the outstanding missing information. At the bottom of this post I will paste one example.
If you simply wanted a report of records with missing information then you could simply run the report of records where that warning message is not blank.
But if you actually do want to know the statistics of how many records have which fields missing then you would have to create individual fields which calculate to the value 1, if that particular field is missing.
So for example,
IF(Trim([Client POC - Name])="", 1)
In your case you would have six formula fields and then you would do a summary report based on those six fields. The reason why you have to do it that way is because a particular record in theory could be missing virtually all of those fields so that same record has to appear in multiple columns on that report.
Here is an example of a long and crazy warning message field.
var text Warnings =
List("<br>",
If(Trim(ToText([Invoice#]))="","Invoice# is blank. Please provide an Invoice#", //Invoice# (With the Problem)
If(Trim(ToText([Account#]))="","Account# is blank. Please provide an Account#")), //Consumer Name
If(Trim(ToText([Product Category]))="","Product Category is blank. Please provide a Product Category"), //Cost Center
If(Trim(ToText([Product Line Code]))="","Product Line is blank. Please provide a Product Line"), //(Product Line Description)
If(Trim(ToText([Defect]))="","Defect is Unspecified. Please specify a Defect"),
If(Trim(ToText([Total Order Qty]))="","Total Order Qty is Unspecified. Please specify Total Order Qty"),
If(Trim(ToText([Problem Area Qty]))="","Problem Area Qty is Unspecified. Please specify Problem Area Qty"),
If(Trim(ToText([Problem Area / Total Order UM]))="","Problem Area / Total Order UM is Unspecified. Please specify UM."),
If(Trim(ToText([Date Installed]))="","Date Installed is Unspecified. Please specify the Installation Date"),
If(Trim(ToText([Type of Installation]))="","Please specify the Type of Installation"),
If(Trim(ToText([Installation Method]))="","Please specify an Installation Method"),
If(Trim(ToText([Rooms Installed]))="","Please specify to which Rooms the Product was Installed"),
If(Trim(ToText([Heating Type]))="","Please specify the Heating Type in use at the time of Installation"),
If(Trim(ToText([Subfloor Type]))="","Please specify the type of Subfloor over which the Product was Installed"),
If(Trim(ToText([Underpad Installed?]))="","Please specify if an Underpad or Underlay was Installed"),
If(Trim(ToText([Amount of Product Affected]))="","Please specify how much Product was Affected"),
If(Trim(ToText([Do you need a GR?]))="","Please specify if you require a GR (Goods Return)"),
If(Trim(ToText([$ Claim Dealer Material]))=""," Please Specify $ Value of Material being Claimed"),
If(Trim(ToText([$ Claim Dealer Goods Return]))="","Value of Goods being Returned is not Specified. Please Specify Value of Goods being Returned"),
If(Trim(ToText([$ Claim Dealer Labour]))="","Value of Labour is not Specified. Please Specify Value of Labour being Claimed"))
;var text PreWarning = "<font color=Blue><b>Warning - We need some information that is currently missing.</b>" ;
If(Trim($Warnings) <> "", $PreWarning & "<font color=Red><br>" & $Warnings)
