Discussions

Expand all | Collapse all

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

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

    Posted 07-09-2018 19:59
    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.


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

    Posted 07-09-2018 20:05
    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


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

    Posted 07-09-2018 23:51
    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.


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

    Posted 07-10-2018 00:00
    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.