Forum Discussion

RachelMiller's avatar
RachelMiller
Qrew Cadet
4 years ago

Report Listing Records With Invalid Field Selections

I am looking for a way to identify records that have an invalid entry for my multiple choice and multi-select fields.  We are going to be uploading several different sets of data want to be able to ca...
Tables and fields
EvanMartinez's avatar
EvanMartinez
Quickbase Staff
4 years ago
Hi Rachel,

For the multi select or multiple choice fields that you want to check for invalid entries do they have the option Allow users to create new choices  checked by chance in the field settings? If they do then new options added in by accident wouldn't show up red, they would just be counted as valid options and added to the list of possible choices. That might be part of the issue if you are seeing strange values appearing in the fields. 


------------------------------
Evan Martinez
------------------------------
  • RachelMiller's avatar
    RachelMiller
    Qrew Cadet
    4 years ago
    Thank you for the response Evan.

    The fields I would want to monitor do not have Allow users to create new choices checked.  I can create a report with the specific fields I am interested in but the problem is filtering it to only show records that have an invalid choice.  I'm not sure how to identify those without manually reviewing each one for red font - which would not be efficient based on the number of records I would have to review.  I want others to be able to upload but don't want to double check their spreadsheets for accuracy if I can audit these fields another way.

    ------------------------------
    Rachel Miller
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      4 years ago


      Rachel,

      You say that some of your fields are multiple-choice fields and some are multi select. 


      For the multiple-choice fields probably the easiest solution is just to have a brute force formula for each multiple-choice field to validate the field

      For example

      not Contains("apples;peaches;pears;plums", [Multiple choice field 1")

      That formula above would flag to be tracked if the response to the multiple-choice field was invalid.


      You also say that you have multi select fields. Those are a little more complicated because you would have to post them out into their individual pieces before validating the entries. I can help you with the formulas for that if you want to work through that on this form. 


      But I don't think there's a magic shortcut here. The only other alternative would be to build tables which have the valid choices for the various fields and make relationships but I think in the end that's more work than just writing formulas.



      ------------------------------
      Mark Shnier (YQC)
      mark.shnier@gmail.com
      ------------------------------