Discussions

Expand all | Collapse all

Contains with list

  • 1.  Contains with list

    Posted 10-09-2019 17:20
    Hello ,

    I have a checkbox formula  field that looks like this. 

    New values are added every week. Instead of editing the formula everytime is there a way i can create a table with values (a simple form to add new values) and reference the table in my contains formulae.


    If(
    Contains([Field Name],"Value 1")
    or Contains([Field Name],"Value 2")
    or Contains([Field Name],"Value 3 ")
    or Contains([Field Name],"Value 4")
    , true,

    false
    )


    Thanks
    Ashish

    ------------------------------
    ashish buddha
    ------------------------------


  • 2.  RE: Contains with list

    Posted 01-15-2020 14:29
    Edited by Justin Lapier 01-15-2020 14:33

    Hi Ashish,

    I know this is an old post and you may have figured this out by now, but I do have a solution to your issue, albeit an alternative.

    1. Create a table as you mentioned with 1 text field, Value.
    2. Make that field on the table the key field.
    3. Relate it as the Parent to your table that the checkbox and records with the Value are on.
    4. Send the Record ID# (even though it isn't the key) down as a lookup.
    5. Edit the Related field in the relationship and make it formula text, with the field from the child table that contains the Value in it (this is assuming that doesn't contain multiple values).
    6. Update your checkbox formula that when the record id# being sent down in the relationship is > 0, true, false.

    Then all you have to do is add a singular record for each value into the new table and all child records will auto-relate to that parent, pulling its record id# down, and evaluating in the formula checkbox. If there is no related record ID# it will be false. 

    I have used this same solution in multiple other instances. For example, employee time cards and receiving 1.5x pay on holidays. You create a Holidays table with the date as the key field, then relate that in the same format, if there is a record ID coming down from the Holiday record, it multiplies the rate x 1.5, otherwise, x 1. 



    ------------------------------
    Justin Lapier
    Synctivate Inc.
    Synctivate Website
    ------------------------------