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

  • 0
  • 1
  • Question
  • Updated 2 months ago
  • In Progress
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.

Photo of Stephanie

Stephanie

  • 676 Points 500 badge 2x thumb

Posted 2 months ago

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

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
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.


Photo of Stephanie

Stephanie

  • 676 Points 500 badge 2x thumb
I'm not understanding what this would do. I'm using a multi-select text field in my formula.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
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.
Photo of Stephanie

Stephanie

  • 676 Points 500 badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
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?
Photo of Stephanie

Stephanie

  • 676 Points 500 badge 2x thumb
No, the formula is no longer working.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
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)") 




Photo of Stephanie

Stephanie

  • 676 Points 500 badge 2x thumb
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 )
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
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.
Photo of Stephanie

Stephanie

  • 676 Points 500 badge 2x thumb
Actually, no, I'm not all good. That worked for this test but it doesn't work for the real formula.

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
Then you should break down the other parts of the formula into pieces as well.  Perhaps the other data is also not what you expected.

ie

[Related Department or Group] = 3

 and then the part 

[Need Advanced Search] = "No"


Photo of Stephanie

Stephanie

  • 676 Points 500 badge 2x thumb
I've tried each of the above and the check box checked. But my original formula still doesn't work.
Could it be the IF? Do I not need that part anymore?

This is what I have
If([Related Department or Group] = 3 and Contains(ToText([Tools]), "MFE"), "sec-idm-ccna-ssas-mfe-cbsalldata")&

Should it be
[Related Department or Group] = 3 and Contains(ToText([Tools]), "MFE", "sec-idm-ccna-ssas-mfe-cbsalldata")&




Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
This syntax is correct

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

So if that is not working then the data in the fields is not what you expected.   For a record that is not working, what is in the field 

[Related Department or Group]

 and what is in the field

[Need Advanced Search]

Note that the "No" will be case sensitive, for example if the data has "no", then that will not be true.