Discussions

Expand all | Collapse all

I need to create a code that counts how many time a particular codes appears in a field

  • 1.  I need to create a code that counts how many time a particular codes appears in a field

    Posted 11-07-2018 17:21
    I have the below string of codes in one quickbase field:

    80346,80329,80346,80371,80346,80335,80324,80346,80369,80332,80332,80356,80361,80361,80361,80361,80354,80358,80372,80329,80324,80367,80355,80346,80360,80346,80366,80373,80368,80335,80353,80365,80346,80365,80324,80338,80332,80332,80349,80362,80359,80359,80335,80332,83992,80332,80354,80365,80358,80335,80373,80369,80346,80346,80329,80342

    I need to create a code to count home many times a particular code (in this case "80361") appears in this string of numbers.

    Below is a code that we were using to count how many entries were populated in that string:

    Count([LCMS CPT Codes],1,"80332")

    This code is returning 3 when there are 6 instances of this code.
    Is there something in the code that is restricting the search?

    Thanks




  • 2.  RE: I need to create a code that counts how many time a particular codes appears in a field

    Posted 11-07-2018 22:47
    Hi Matt, 

    The Count function does not work the way you think it does. It's taking each of your items [LCMS CPT Codes],1, & "80332" and counting them... so there are 3.

    And while this explains why you're getting the response you got, it does not help you achieve your goal.

    What you are trying to do is not a simple task (although it ought to be). Is your string of text always the same length? Essentially what you'll have to do is parse out each 5 digit code (using Part and the comma delimiter) and compare it against your particular code and 'sum' the times it matches.

    -Sharon




  • 3.  RE: I need to create a code that counts how many time a particular codes appears in a field

    Posted 11-08-2018 03:49
    It is a simple task - just use a rich text formula field with this definition:



    var Text Code = "80332";
    "<img src onerror='this.outerHTML=(\"" & [Codes] & "\".match(/" & $Code & "/g)||[]).length'>"
    Count Substring ~ CPT Codes List All
    https://haversineconsulting.quickbase.com/db/bn52xgwt8?a=q&qid=1

    Pastie Database
    https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=688



  • 4.  RE: I need to create a code that counts how many time a particular codes appears in a field

    Posted 11-08-2018 22:53
    I'll have to try this one out! Thanks


  • 5.  RE: I need to create a code that counts how many time a particular codes appears in a field

    Posted 11-09-2018 14:58
    Hi Dan, 

    Thank you for you response. 
    Unfortunately, I am not familiar with the Var Text Codes. 

    I copy and pasted your code and it is throwing an error for me. 

    Are you able to diagnose what is wrong with the code?



    https://imgur.com/a/uvz3JLD 

    It looks like the screenshot didn't upload that well, so a link to the image has also been added. 

    Thanks,



  • 6.  RE: I need to create a code that counts how many time a particular codes appears in a field

    Posted 11-09-2018 16:20
    It looks like there are no double quotes at the start and end of the second line of the formula.


  • 7.  RE: I need to create a code that counts how many time a particular codes appears in a field

    Posted 11-09-2018 17:09
    Hi Dan, 

    Added the quotes, but it is still having an issue with the period in the "this.outer" portion of the formula

    https://imgur.com/a/pxfKwEw


  • 8.  RE: I need to create a code that counts how many time a particular codes appears in a field

    Posted 11-12-2018 04:42
    Now you have two double quotes at the start and end of the second line.