Quick Base Discussions

Expand all | Collapse all

Formula to count values in a field

  • 1.  Formula to count values in a field

    Posted 04-17-2018 15:34
    I have a multi-line text field that clients use to input various Skus or Barcode values. The values can be scanned in or copy/pasted using various delimiters (comma, enter, space). I need another field that will count how many values were entered so that I can compare it to which values were returned. I'm having issues creating this field because they could enter any number of values (sometimes upwards of 250 values). Is this even possible?


  • 2.  RE: Formula to count values in a field

    Top
    Contributor
    Posted 04-17-2018 15:53
    Hi Rebecca,

    So currently your clients are entering or putting in all of these SKUs and they aren't using one set delimiter? So it could be a comma, a space, or other character? There isn't anything consistent to help identify one entry from another?


  • 3.  RE: Formula to count values in a field

    Posted 04-17-2018 16:28
    Not currently. If forcing them to use one delimiter would allow me to solve for a count of the values entered, then I could make that sacrifice. 


  • 4.  RE: Formula to count values in a field

    Top
    Contributor
    Posted 04-17-2018 17:59
    Hi Rebecca,

    I was going to say something similar to what Mark posted below, it is going to require a pretty long formula to check on all of those different fields but it is possible to build out so long as you have a reliable breaking character to read off of. In his example he is doing it off of /n but alternatively it could be off of a ; or : between each entry. A reliable delimiter would allow you to utilize a formula like this. Then the Count will add up all the non blank Part entries. 


  • 5.  RE: Formula to count values in a field

    Posted 04-17-2018 18:02
    ... my formula will break on a space, or a comma or surprisingly the carriage return (new line) character. 


  • 6.  RE: Formula to count values in a field

    Top
    Contributor
    Posted 04-17-2018 18:13
    I have been starring at enough formulas today I didn't even see that comma in there at first glance. 


  • 7.  RE: Formula to count values in a field

    Posted 04-17-2018 17:26
    Rebecca,
    Here is a formula for up to 5, but you would need a lot of repetition to get it to count to 250!  I would suggest testing with say 5 or 10 to see if it would work for how your staff paste.


    var Text parta = Part([paste target], 1, "\n ,");
    var Text partb = Part([paste target], 2, "\n ,");
    var Text partc = Part([paste target], 3, "\n ,");
    var Text partd = Part([paste target], 4, "\n ,");
    var Text parte = Part([paste target], 5, "\n ,");

    Count(
    $parta<>"",
    $partb<>"",
    $partc<>"",
    $partd<>"",
    $parte<>"")


  • 8.  RE: Formula to count values in a field

    Posted 04-17-2018 20:41
    It "works" but would be ridiculous to implement.... Is there no other way to count unique values in a field? 


  • 9.  RE: Formula to count values in a field

    Posted 02-01-2019 22:06
    I am in need of something like this. Rebecca were you able to get a formula for this? 


  • 10.  RE: Formula to count values in a field

    Posted 04-17-2018 20:45
    I know that someone like Dan Diebolt who is on this forum from time to time would have a way to do this with the Image Onload Technique,  But it would only calculate on a form and not actually be a field which would be usable elsewhere on the app.