Need help with a formula field -- Trying to populate a new field based upon whether or not the original fields are blank or not.

  • 0
  • 1
  • Question
  • Updated 4 months ago
  • Answered
I am trying to create a new field that can be used as a filter for a report.  The issue is this -- I need to create a field that enters a value based on Question 1, Question 2, or Question 3 being answered or not.  For example, I want to be able to filter results if only question 1 was answered.  The same for Question 2 or 3 independently.  I thought I would need a formula that says: 
If (Question 1) is not " ", then "Q1 taken", etc...  but cannot seem to get it right.  Any help appreciated.
Photo of Pro Survey Solutions

Posted 4 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 50,850 Points 50k badge 2x thumb
Try this

List("\n",
IF(Trim([Question 1])<>"","Question 1 taken"),
IF(Trim([Question 2])<>"","Question 2 taken"),
IF(Trim([Question 3])<>"","Question 3 taken"))

The result would be a vertical list like 

Question 1 taken
Question 3 taken

If they answered questions 1 &3.

The report filter could use the contains as a filter like 

where [Questions Taken] Contains 1
Thanks for your help.  This is not quite working the way I had intended.  When I tried the above it is grouping in the filter as:

Question 1 Taken

Question 1 Taken
Question 2 Taken

Blank

What I really want is just:

Question 1 Taken

Question 2 Taken

If there are blanks they should be excluded.  When I refer to the filters they are the dynamic ones on the report, not within the report criteria.  I don't want it to be grouped into a new filter option if the user answers both questions.  The use case is tricky and perhaps it would be easier for me to send a screen shot and full use case?  Unless the above makes sense and is something simple I am missing. Thanks again.
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 50,850 Points 50k badge 2x thumb
If you want a Dynamic filter you will need to make three fields and have three dynamic filters, not one dynamic filter with three choices.

So just make a formula for [Question 1 Taken] with a result value of Yes or No.

Get that working then duplicate the field twice and adjust the formula for the other two questions.