Skip to main content
Question

Search for invalid entries

  • September 12, 2017
  • 0 replies
  • 55 views
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 search my app for such entries.  The app has gone through some changes and there could be an entry or two that are no longer valid.  I would like to run a report so that I can say with confidence that any bad entries have been resolved.  Does anybody know how to do this?
    This topic has been closed for replies.

    Those valid choices are probably from a hard coded drop down list in a multiple choice field
    properties.

    You can copy them on your clipboard and then paste them into a formula like this

    [All valid choices] formula is

    List(";", "apples","peaches","pears","plums")

    Then run a report where 

    [All choices] does not contain [My field]
    Thanks Mark,
    I'm trying for a more comprehensive approach.  I have many tables, each with many fields and different entry restraints; I would have to go field by field, table by table this way.  If QuickBase flags invalid entries with the same metadata I could report on them all with one logic statement.  In fact, if I could report on fields with font color red, that would satisfy my needs just as well.  I just don't know how to do that and I have a feeling any solution is going to involve javascript.
    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:
    1. Iterate over all applications
    2. iterate over all tables in application
    3. iterate over all fields in table
    4. 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
    5. Now iterate over every record in the response looking for field choices that are not in the schema choices for the field
    6. 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:


    Terms & ConditionsAccessibility statement