Filter report on record containing 'Unknown'

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I am creating a few validation reports to check for errors in uploaded data to a table.  The table that includes the records I want to validate is called 'Expenditures'.  The Expenditures table has a many to one relationship with the 'PARs' and 'Budget Lines' tables.  There have been instances where users have uploaded expenditure records into the Expenditures table that include PAR Numbers (reference field to PARs table) and/or Budget Line (reference field to Budget Lines table) that do not exist.  The upload completes without error.  When you go the form for the records created with these issues, you find that the fields will read 'Unknown PAR (2017 BT 001)' or 'Unknown Budget Line (2017 BT 002)'.

I'd like to create a validation report filters on records that have these 'unknown' entries as these create mapping issues for reporting purposes in my app.  How do I do this?

Perhaps there is a preventative solution that can prevent my users from uploading records with invalid reference field data?

Any and all help is appreciated!

Photo of Brett Telford

Brett Telford

  • 20 Points

Posted 3 years ago

  • 0
  • 1
To detect Children with no Parents, a simple method is to put a field on the Parent called [Parent Exists?] as a formula checkbox field with a formula of 

true

Then on the Relationship lookup that field down to the children.  The Orphans are the ones where that [Parent Exists?] lookup field is false

I can't think of an easy native way to block the import of Orphans, ie children with bad data for the Parent Key field.  But you can set up a Notification for records being added where [Parent Exists?] is false, so the importer would get instant feedback.
Photo of Brett Telford

Brett Telford

  • 20 Points
You da man.