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!