Has anyone built a calculated field using IFIN formula for a multiselect field on a Custom Field? | Community
Skip to main content
J
JaimeDa1
Level 2
September 8, 2021
Question

Has anyone built a calculated field using IFIN formula for a multiselect field on a Custom Field?

  • September 8, 2021
  • 2 replies
  • 2172 views

I'm trying the IFIN(value, value1, value2,..., trueExpression, falseExpression). However, my multi select field has numerous instances that need to be combined. When I reached out to support they said "CONTAINS" wouldn't work for what I'm trying to do. Has anyone had to do this or know enough about calculated fields? I'm trying to create a report to sum the projects for each True Expression but am not able to get the data.

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

    2 replies

    C
    ChloeRo
    Level 4
    September 10, 2021

    We have something similar, but it uses SUM and IF. I would imagine you could swap "IF" for "IFIN" though. Essentially, we set the true statement to the value "1" and the false statement to "0". That way, if true is met, it counts for 1 point, if false is met, it counts for 0 points. Then the SUM function that surrounds the formula will add up all the points. It's sort of confusing to explain, but I'll post a generalized example:

    =SUM(IFIN(Multi-Select Field,valueX,1,0),IFIN(Multi-Select Field,valueY,1,0),IFIN(Multi-Select Field,valueZ,1,0))

    So, in theory, if we had something where all 3 IFIN statements were met, WF would return the value 3. If only 2 IFIN statements were met, WF would return the value 2. Depending on how many multi-select options you have, this could get cumbersome. Some of our multi-selects have about 100 options and that wouldn't be practical for this use. In those cases, you probably won't get much use out of this calculated field.

    Is that sort of what you needed?

      C
      ChloeRo
      Level 4
      September 10, 2021

      I just tested this out with CONTAINS and it seems to work:

      SUM(IF(CONTAINS(Multi-Select Field,valueX),1,0),IF(CONTAINS(Multi-Select Field,valueY),1,0),IF(CONTAINS(Multi-Select Field,valueZ),1,0))

        J
        JaimeDa1Author
        Level 2
        September 13, 2021

        Wow thank you! I'll try it in my form. I appreciate it so much.

        A
        Alex3045010137kt
        Level 2
        October 20, 2021

        Hi @Jaime Davidson‚ , did this end up working for you? I've tried it in my instance, but when multiple items are selected, it doesn't seem to be summing anything up, only when a single option is selected. Thanks!

        EDIT: Figured it out, it looks like the variables should be flipped. This calculates correctly when multiple items are selected:

        SUM(IF(CONTAINS("Text to find in quotes",MultiselectField),1,0),IF(CONTAINS("Text to find in quotes",MultiselectField),1,0),IF(CONTAINS("Text to find in quotes",MultiselectField),1,0))

        J
        JaimeDa1Author
        Level 2
        October 20, 2021

        I ended up working with a consultant who helped me with the formula. We needed to figure out IF and Contains because we had multiple options for the field where someone can select multiple choice or only one.

        IF statement looked like this:

        IF(CONTAINS("x",{multiselect field})&&CONTAINS("y",{multiselect field})||CONTAINS("x",{multiselect field})&&CONTAINS("z",{multiselect field}),"Multiple Parent Regions")

        CONTAINS statement looks like this

        IF(CONTAINS("x",{multiselect field}}),"x",IF(CONTAINS("y",{multiselect field}}),"y",IF(CONTAINS("z",{multiselect field}}),"z")))

        In my formula, these two statements were strung together.

          F
          FemkeCr
          Level 4
          July 14, 2022

          This statement was so helpful to me. I struggled with my formula for weeks; this CONTAINS contribution gave me new insights and inspiration to sum values of a multi-select field.

          So, thanks for sharing!

            Terms & ConditionsAccessibility statement

            Loading footer...