Formula to count values in a field

  • 0
  • 1
  • Question
  • Updated 7 months ago
  • In Progress
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?
Photo of Rebecca

Rebecca

  • 94 Points 75 badge 2x thumb

Posted 7 months ago

  • 0
  • 1
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,774 Points 5k badge 2x thumb
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?
Photo of Rebecca

Rebecca

  • 94 Points 75 badge 2x thumb
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. 
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,774 Points 5k badge 2x thumb
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. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
... my formula will break on a space, or a comma or surprisingly the carriage return (new line) character. 
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,774 Points 5k badge 2x thumb
I have been starring at enough formulas today I didn't even see that comma in there at first glance. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
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<>"")
Photo of Rebecca

Rebecca

  • 94 Points 75 badge 2x thumb
It "works" but would be ridiculous to implement.... Is there no other way to count unique values in a field? 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
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.