Discussions

 View Only
Expand all | Collapse all

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

Archive User

Archive User03-04-2019 13:21

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

    Posted 03-01-2019 17:51
    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.



  • 2.  RE: Formula using a text list field was working and now isn't

    Posted 03-01-2019 18:54
    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.




  • 3.  RE: Formula using a text list field was working and now isn't

    Posted 03-01-2019 19:07
    I'm not understanding what this would do. I'm using a multi-select text field in my formula.


  • 4.  RE: Formula using a text list field was working and now isn't

    Posted 03-01-2019 19:17
    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.


  • 5.  RE: Formula using a text list field was working and now isn't

    Posted 03-04-2019 12:55
    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.


  • 6.  RE: Formula using a text list field was working and now isn't

    Posted 03-04-2019 13:18
    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?


  • 7.  RE: Formula using a text list field was working and now isn't

    Posted 03-04-2019 13:21
    No, the formula is no longer working.


  • 8.  RE: Formula using a text list field was working and now isn't

    Posted 03-04-2019 13:23
    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)") 






  • 9.  RE: Formula using a text list field was working and now isn't

    Posted 03-04-2019 13:34
    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 )


  • 10.  RE: Formula using a text list field was working and now isn't

    Posted 03-04-2019 13:39
    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.


  • 11.  RE: Formula using a text list field was working and now isn't

    Posted 03-04-2019 13:44
    Actually, no, I'm not all good. That worked for this test but it doesn't work for the real formula.



  • 12.  RE: Formula using a text list field was working and now isn't

    Posted 03-04-2019 13:48
    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"




  • 13.  RE: Formula using a text list field was working and now isn't

    Posted 03-04-2019 14:00
    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")&






  • 14.  RE: Formula using a text list field was working and now isn't

    Posted 03-04-2019 14:13
    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.