Forum Discussion

RebeccaRebecca's avatar
RebeccaRebecca
Qrew Trainee
8 years ago

Formula to count values in a field

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?
Ask about Quickbase functionality

9 Replies

Sort By
  • EvanMartinez's avatar
    EvanMartinez
    Moderator
    8 years ago
    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?
    • RebeccaRebecca's avatar
      RebeccaRebecca
      Qrew Trainee
      8 years ago
      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. 
    • EvanMartinez's avatar
      EvanMartinez
      Moderator
      8 years ago
      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. 
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      8 years ago
      ... my formula will break on a space, or a comma or surprisingly the carriage return (new line) character. 
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    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<>"")
  • RebeccaRebecca's avatar
    RebeccaRebecca
    Qrew Trainee
    8 years ago
    It "works" but would be ridiculous to implement.... Is there no other way to count unique values in a field? 
    • JassyJackson's avatar
      JassyJackson
      Qrew Trainee
      7 years ago
      I am in need of something like this. Rebecca were you able to get a formula for this? 
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    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.