Forum Discussion

ArchiveUser's avatar
ArchiveUser
Qrew Captain
7 years ago

Formula using a text list field was working and now isn't

I have a multi-select text list field [Tools] that I am using in a formula. When items in the field are selected another field shows corresponding information. The formula below has been working for several months. I went in to add to it and now it is not working. I have several set up like this and I checked the ones I had not changed and they also are not working. What has changed with Text Lists that is causing this not to work? How should I rewrite this formula?

If([Related Department or Group] = 3 and Contains(ToText([Tools]), "ASR (Legacy)") and [Need Advanced Search] = "No", "sec-idm-ccna-mstr-asrlegacy-cbsalldata")

Thank you.

App builders
Formulas and functions
Tables and fields
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    I suggest a step to debug is to make a new formula checkbox field with this formula

    Contains(ToText([Tools]), "ASR (Legacy)") 

     and see if it works.


  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    7 years ago
    I'm not understanding what this would do. I'm using a multi-select text field in my formula.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    All I'm saying is there is nothing that appears to be wrong with your formula so I suggest a next debugging step is to try testing that one piece of your formula and report back what the string you were testing was and what the result of the formula was.

    It's unlikely it is a Quick Base bug in the Contains function, but to try to prove a bug you would have to have the most simple formula possible.
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    7 years ago
    It's highlighting the field [Tools] in my formula and giving me the following message.
    "Expecting number/text/bool/date/datetime/timeofday/duration/workdate/user/userlist but found textlist."

    This has been working fine for several months now so I'm not sure why it all of a sudden doesn't.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    Sometimes the formula editor gives false warnings. It is meant to be an interactive aid, but its not always perfect.  So the question is ... is the formula actually working?
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    Did you make this formula as a stand alone alone formula checkbox field in an attempt to debug your main formula?

    Contains(ToText([Tools]), "ASR (Legacy)") 




  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    7 years ago
    I had to make a small change but it works now. The checkbox is now checked.
    (ASR (Legacy) doesn't exist now so I had to change it )
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    So the issue was some confusion over the data that was actually in the data. 

    That is why I suggested the debugging tactic to break down the formula  into smaller pieces to isolate the part that is causing the problem and determine cleanly if the data was not what you expected or the formula has a problem.

    There was also the misleading situation with the formula editor giving a false warning, that threw you off.

    Thx for letting me know that you are now all good.
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    7 years ago
    Actually, no, I'm not all good. That worked for this test but it doesn't work for the real formula.