Discussions

 View Only
Expand all | Collapse all

Trying to ""Count"" the number of selections in a multi-select text field

Archive User

Archive User10-03-2016 15:36

  • 1.  Trying to ""Count"" the number of selections in a multi-select text field

    Posted 04-23-2016 19:11

    Hi,

    I have a multi-select text field : [ERP Products]

    I'm trying to have a Formula - Numeric field, to count the number of products that were selected.

    I have tried something like this:

    var Numeric ERPCounter = List(",",ToText[ERP Products]));   // to get the text list of the products, then have a "," delimited, so that the next (Count) function would work.

    Then used:

    Count($ERPCounter)

    That doesn't seem to be working.

    Trying to find a way to count those products. Any ideas?

    Thanks!



  • 2.  RE: Trying to ""Count"" the number of selections in a multi-select text field

    Posted 04-23-2016 19:44
    I'm not sure if this is the best way to do it, but I did figure a way. It requires a bit of maintenance to the formula as products are added to the list, but here's what I did, in case it's helpful to anyone:

    My [ERP Products] field is a multi-select text
    Created a new Formula Text field [ERPProductsText]
    List:
    � Product1
    � Product2
    Formula: ToText(ERPProductsText]
    Will result in:
    Product1;Product2

    Create a Formula - Numeric field [ProductCounter]
    Sum(
    If (Contains([ERPProductsText],"Product1"),1,0),
    If (Contains([ERPProductText],"Product2"),1,0)
    )

    For every new entry in the Product list, a new line is needed in the counter formula.


  • 3.  RE: Trying to ""Count"" the number of selections in a multi-select text field

    Posted 04-23-2016 20:27
    This formula will require no maintenance.  Just don't have a semi colon in one of the choices though.



    Try this

    var text value = ToText([Multi select]);



    Count(

    Trim(Part($value,1,";")),

    Trim(Part($value,2,";")),

    Trim(Part($value,3,";")),

    Trim(Part($value,4,";")),

    Trim(Part($value,5,";")),

    Trim(Part($value,6,";")),

    Trim(Part($value,7,";")),

    Trim(Part($value,8,";")),

    Trim(Part($value,9,";")),

    Trim(Part($value,10,";")),

    Trim(Part($value,11,";")),

    Trim(Part($value,12,";")),

    Trim(Part($value,13,";")),

    Trim(Part($value,14,";")),

    Trim(Part($value,15,";")),

    Trim(Part($value,16,";")),

    Trim(Part($value,17,";")),

    Trim(Part($value,18,";")),

    Trim(Part($value,19,";")),

    Trim(Part($value,20,";"))

    )


  • 4.  RE: Trying to ""Count"" the number of selections in a multi-select text field

    Posted 11-24-2021 12:16
    I know this is an old thread, but I was searching for a solution and stumbled on my own question :)

    I have a different scenario requiring the same formula. In this case, I am creating a Summary Report, with a Report Formula containing the above, to DISTINCT count the number of items in the text list.

    It's working great, except for one detail: It's counting the empty values. Is there a way to adjust the above formula to avoid the empty values?

    I have tried the Nz() but that didn't seem to do the trick.
    I also tried to add an if statement on each Trim Line
    If(Trim(Part($value,1,";")) <> "", Trim(Part($value,1,";")),"") 

    That also didn't seem to work.

    Any other ideas?

    ------------------------------
    George Khairallah
    CTO
    gotomyerp, LLC
    ------------------------------



  • 5.  RE: Trying to ""Count"" the number of selections in a multi-select text field

    Posted 11-24-2021 13:53
    Can you post your whole formula?

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 6.  RE: Trying to ""Count"" the number of selections in a multi-select text field

    Posted 11-24-2021 13:59
    Sure.. it's actually exactly the one suggested in this thread:

    var text value = ToText([Product Add-On Apps]);
    Count(
    Trim(Part($value,1,";")),
    Trim(Part($value,2,";")),
    Trim(Part($value,3,";")),
    Trim(Part($value,4,";")),
    Trim(Part($value,5,";")),
    Trim(Part($value,6,";")),
    Trim(Part($value,7,";")),
    Trim(Part($value,8,";")),
    Trim(Part($value,9,";")),
    Trim(Part($value,10,";")),
    Trim(Part($value,11,";")),
    Trim(Part($value,12,";")),
    Trim(Part($value,13,";")),
    Trim(Part($value,14,";")),
    Trim(Part($value,15,";")),
    Trim(Part($value,16,";")),
    Trim(Part($value,17,";")),
    Trim(Part($value,18,";")),
    Trim(Part($value,19,";")),
    Trim(Part($value,20,";"))
    )

    This formula is being used as a report formula field in the Summary Report.

    ------------------------------
    George Khairallah
    CTO
    gotomyerp, LLC
    ------------------------------



  • 7.  RE: Trying to ""Count"" the number of selections in a multi-select text field

    Posted 11-24-2021 16:53
    try this

    var text value = ToText([Product Add-On Apps]);
    Count(
    Trim(Part($value,1,";"))<>"",
    Trim(Part($value,2,";"))<>"",
    Trim(Part($value,3,";"))<>"",
    etc etc


    Trim(Part($value,20,";"))<>"")


    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 8.  RE: Trying to ""Count"" the number of selections in a multi-select text field

    Posted 11-24-2021 17:02
    Hm. I don't think that made a difference.   Still seems to be picking up the empty values.

    As a side note, I'm not sure understand the  meaning of Trim(expression) <> ""  ... is this a shorthand for my "If" statement?

    If(Trim(Part($value,1,";")) <> "", Trim(Part($value,1,";")),"")

    ------------------------------
    George Khairallah
    CTO
    gotomyerp, LLC
    ------------------------------



  • 9.  RE: Trying to ""Count"" the number of selections in a multi-select text field

    Posted 11-24-2021 17:09
    Maybe you need to post a screen shot of your source data field. and you are confusing me this this 
    quesiton
    If(Trim(Part($value,1,";")) <> "", Trim(Part($value,1,";")),"")

    what does that how to do with your original question.

    This expression here
    Trim(Part($value,1,";")) <> ""
     will be true and Count as 1 in a count fucntion of the 1st part of the $value is not blank.
     

    this here

    <>

    means not equal.

    "" means comparing to empty quotes so blank.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 10.  RE: Trying to ""Count"" the number of selections in a multi-select text field

    Posted 11-24-2021 17:28
    Sorry Mark. Didn't mean to confuse things. You can disregard my "If" statement.
    Here is a screenshot of the values.
    The top one is a reference table report that shows the actual values of the multi-select field. You can see that there are 2 values: "Quickbooks Enterprise" and an "Empty" value.
    In the bottom screenshot is the Summary Report, showing the "Distinct Count" including the empty value in that multi-select field. What I'm looking for is for that number to be "1" instead of "2" (basically excluding the "empty" value)

    Does this clarify things?

    ------------------------------
    George Khairallah
    CTO
    gotomyerp, LLC
    ------------------------------



  • 11.  RE: Trying to ""Count"" the number of selections in a multi-select text field

    Posted 11-24-2021 17:32
    .. I think that someone would have to look at your app, one on one.  You can contact the Quickbase Care Team or else email me directly.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 12.  RE: Trying to ""Count"" the number of selections in a multi-select text field

    Posted 11-24-2021 17:36
    Ok thanks Mark. Will open a support case to get some further guidance.

    ------------------------------
    George Khairallah
    CTO
    gotomyerp, LLC
    ------------------------------



  • 13.  RE: Trying to ""Count"" the number of selections in a multi-select text field

    Posted 04-23-2016 20:31