Is there a way to search or create a report where the filter criteria is <invalid entries>? QuickBase can obviously identify invalid entries because the font color changes to red but I can't seem to ...
This of course can be done with script and is a good candidate for using the Async / Await technique because of the large number of API calls you would have to make.
This is how I would proceed:
Iterate over all applications
iterate over all tables in application
iterate over all fields in table
perform a query for all records of the current table including the current field using API_DoQuery with &fmt=structured. In addition to the record data the response will include information about the field and the current choices allowed if the field is a multiple choice field
Now iterate over every record in the response looking for field choices that are not in the schema choices for the field
report illegal choices as they are encountered and continue the iterations
This may sound complicated but it will turn out to be pretty simple. I will probably put this example on my to do list and knock it off when I have more time.
Below are a few screenshots of a preliminary approach to the problem. The first screenshot shows a report with both allowable choices for two fields and a few illegal choices. The second screenshot shows a portion of the XML response listing the allowed choices for one of field. The third screenshot shows the XML response.
Report of All Records with allowable and illegal choices:
Fragment of XML Response showing allowable choices for one field:
Fragment of XML Response showing all data records: