Forum Discussion

GregGreg2's avatar
GregGreg2
Qrew Member
11 years ago

How to calculate a UPC check digit in QuickBase?

The last digit of a bar code number is a computer check digit which makes sure the bar code is correctly composed.

The final digit of a Universal Product Code is a check digit computed as follows:

  1. Add the digits (up to but not including the check digit) in the odd-numbered positions (first, third, fifth, etc.) together and multiply by three.
  2. Add the digits (up to but not including the check digit) in the even-numbered positions (second, fourth, sixth, etc.) to the result.
  3. Take the remainder of the result divided by 10 (modulo operation) and subtract this from 10 to derive the check digit.

Here is the Excel formula:

=MIN(MOD((SUM(--MID(A1,{1,3,5,7,9,11},1))*3+SUM(--MID(A1,{2,4,6,8,10},1))),10),10-MOD((SUM(--MID(A1,{1,3,5,7,9,11},1))*3+SUM(--MID(A1,{2,4,6,8,10},1))),10))

How can I convert this formula so that it works in QuickBase?

Thank you.

Mr. T

Data import and export
Formulas and functions
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    I just did a similar formula so I thought I would be a sport and post it here.  it will produce the whole 12 digit UPC code.  In this case my client was working off the Quick Base Record ID# after their own prefix.

    var text UPCMissingCheckDigit = ToText(742797) & ToText([Record ID#]+84000);
    // test a few kitchen items  :)
    //var text UPCTEST = "62815401171"; // Shreddies (Check digit should be a 2)
    //var text UPCTEST = "06410027191"; // All Bran (Check digit should be a 3)
    //var text UPCTEST = "05574250317"; // Sugar Cones (Check digit should be a 3)

    var text String = $UPCMissingCheckDigit;

    var number A = ToNumber(Mid($String,1,1));
    var number B = ToNumber(Mid($String,2,1));
    var number C = ToNumber(Mid($String,3,1));
    var number D=  ToNumber(Mid($String,4,1));
    var number E = ToNumber(Mid($String,5,1));
    var number F = ToNumber(Mid($String,6,1));
    var number G = ToNumber(Mid($String,7,1));
    var number H = ToNumber(Mid($String,8,1));
    var number I = ToNumber(Mid($String,9,1));
    var number J = ToNumber(Mid($String,10,1));
    var number K = ToNumber(Mid($String,11,1));

    var number Odd = ($A + $C + $E + $G + $I + $K) * 3;
    var number Even =($B + $D + $F + $H + $J);

    var number Sum = $Odd + $Even;
    var number CheckDigit = 10-Rem($Sum,10);

    $String & ToText($CheckDigit)
    • SimonH's avatar
      SimonH
      Qrew Cadet
      3 years ago
      Thanks for this formula, i just used it in an app.

      One change i did have to make is to the final line

      $String & right(ToText($CheckDigit),1)​

      I added the 'right' function so that when the mod is 0 and thus the result of the previous line 
      var number CheckDigit = 10-Rem($Sum,10);
​
      is "10" it will now only append the "0" and not "10"

      ------------------------------
      Simon H
      ------------------------------