Skip to main content
Question

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.

This topic has been closed for replies.

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.


I'm not understanding what this would do. I'm using a multi-select text field in my formula.
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.
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.
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?
No, the formula is no longer working.
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)") 




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 )
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.
Actually, no, I'm not all good. That worked for this test but it doesn't work for the real formula.

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"


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")&




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.


Terms & ConditionsAccessibility statement