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
    Thanks Mark! this works a treat, and removes the management aspect of that field. Cheers!


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

    Posted 09-29-2016 14:30
    Leveraging Mark's awesome formula to pull the Sum...

    var text value = ToText([MultiSelectFieldName]);
    Sum(
    ToNumber(Trim(Part($value,1,";"))),
    ToNumber(Trim(Part($value,2,";"))),
    ToNumber(Trim(Part($value,3,";"))),
    ToNumber(Trim(Part($value,4,";"))),
    ToNumber(Trim(Part($value,5,";"))),
    ToNumber(Trim(Part($value,6,";"))),
    ToNumber(Trim(Part($value,7,";"))),
    ToNumber(Trim(Part($value,8,";"))),
    ToNumber(Trim(Part($value,9,";"))),
    ToNumber(Trim(Part($value,10,";"))),
    ToNumber(Trim(Part($value,11,";"))),
    ToNumber(Trim(Part($value,12,";"))),
    ToNumber(Trim(Part($value,13,";"))),
    ToNumber(Trim(Part($value,14,";"))),
    ToNumber(Trim(Part($value,15,";"))),
    ToNumber(Trim(Part($value,16,";"))),
    ToNumber(Trim(Part($value,17,";"))),
    ToNumber(Trim(Part($value,18,";"))),
    ToNumber(Trim(Part($value,19,";"))),
    ToNumber(Trim(Part($value,20,";")))
    )


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

    Posted 10-03-2016 14:34
    >This formula will require no maintenance.

    When QuickBase finally replaces the Formula Language with JavaScript that "no maintenance" Formula will have to be replaced with JavaScript. It is inevitable!

    Atwood's Law
    http://cryptochaos.com/atwoods-law-any-application-that-can-be-writt


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

    Posted 07-26-2022 08:32
    This comment aged like milk.

    ------------------------------
    Joseph Reynolds
    ------------------------------



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

    Posted 10-03-2016 15:36
    It's good to be a consultant...


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

    Posted 04-21-2019 01:58
    It seems that the above solution has worked for people previously, but I am having a hard time with it. When I insert my multi-select text field after the ToText command, I get an error stating that was not expecting a textlist. It will still save, but doesn't return any results. I'm fairly new to this platform, so I'm not sure if I'm just doing something wrong or if this really isn't possible. Any help you can offer would be greatly appreciated. Thanks


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

    Posted 04-21-2019 02:23
    I suggest you post your formula if you say it doesn�t work.


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

    Posted 04-23-2019 18:59
    Sorry for the delay. I have just copied the formula that you have shared above and changed the multi select field to the field in my app...

    var text value = ToText([Caller Needs, Interests, Drivers]);
    Sum(
    ToNumber(Trim(Part($value,1,";"))),
    ToNumber(Trim(Part($value,2,";"))),
    ToNumber(Trim(Part($value,3,";"))),
    ToNumber(Trim(Part($value,4,";"))),
    ToNumber(Trim(Part($value,5,";"))),
    ToNumber(Trim(Part($value,6,";"))),
    ToNumber(Trim(Part($value,7,";"))),
    ToNumber(Trim(Part($value,8,";"))),
    ToNumber(Trim(Part($value,9,";"))),
    ToNumber(Trim(Part($value,10,";"))),
    ToNumber(Trim(Part($value,11,";"))),
    ToNumber(Trim(Part($value,12,";"))),
    ToNumber(Trim(Part($value,13,";"))),
    ToNumber(Trim(Part($value,14,";"))),
    ToNumber(Trim(Part($value,15,";"))),
    ToNumber(Trim(Part($value,16,";"))),
    ToNumber(Trim(Part($value,17,";"))),
    ToNumber(Trim(Part($value,18,";"))),
    ToNumber(Trim(Part($value,19,";"))),
    ToNumber(Trim(Part($value,20,";")))
    )

    I have this in formula numeric field and trying to hopefully get a pie graph out of the data.


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

    Posted 09-23-2019 09:19
    Hi, I am trying this out but am getting a Syntax Error: Extra characters beyond the end of the formula. I copied the formula below. I am a bit of an amateur so forgive any silly mistakes. Any ideas? 

    New_variable = ToText([TRAD/CON Q12]);
    Sum(
    ToNumber(Trim(Part($value,1,";"))),
    ToNumber(Trim(Part($value,2,";"))),
    ToNumber(Trim(Part($value,3,";"))),
    ToNumber(Trim(Part($value,4,";"))),
    ToNumber(Trim(Part($value,5,";"))),
    ToNumber(Trim(Part($value,6,";")))
    )

    ------------------------------
    Fady Ibrahim
    ------------------------------



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

    Posted 09-23-2019 09:29
    not tested but try this.
    If there is a syntax error please post the your formula and the whole error message.

    var text value = ToText([TRAD/CON Q12]);

    Count(
    Trim(Part($value,1,";"))<>"",
    Trim(Part($value,2,";"))<>"",
    Trim(Part($value,3,";"))<>"",
    Trim(Part($value,4,";"))<>"",
    Trim(Part($value,5,";"))<>"",
    Trim(Part($value,5,";"))<>"")





    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------



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

    Posted 04-27-2021 09:01
    Hello Mark,

    I just have a quick question on this formula.  Where do I put that formula?  Inside the summary report?  On the form in the multi-select formula field?

    ------------------------------
    Courtneah Thomas
    ------------------------------



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

    Posted 04-27-2021 09:04
    You would create a new formula numeric field to calculate the count.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



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

    Posted 04-27-2021 10:56
    And once that is done, how would we then put that into a report?  More specifically, a graph?

    ------------------------------
    Courtneah Thomas
    ------------------------------



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

    Posted 04-27-2021 11:31
    I think that you need to start a new thread with a fresh question and completely describe what you are trying to do.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



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

    Posted 04-23-2019 19:31
    Try changing Sum to Count


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

    Posted 04-21-2019 02:03
    I hope my memory serves me correct.  I **think**, there is a bug in the ToText function with the "Text Lists". The way to know is, if it saves anyway, despite the error, you might be ok. 

    Hopefully someone from the QuickBase can confirm or deny this.



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

    Posted 04-21-2019 02:22
    There is no bug in the ToText function other than the preview of the syntax editor while you were entering the formula gives a false warning. But it will save just fine and it will work just fine


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

    Posted 04-21-2019 02:25
    Thanks for confirming! I recall having seen something in another thread a couple days ago ( I think from you ). Just didn�t remember if it was the function itself or the formula editor itself.


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

    Posted 07-28-2021 10:38
    Count(";",ToText([Field]))+1

    ------------------------------
    Charlene Wilbur
    ------------------------------