How to calculate a UPC check digit in QuickBase?

  • 39
  • 1
  • Question
  • Updated 8 months ago
  • Answered

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

Photo of Greg

Greg

  • 0 Points

Posted 4 years ago

  • 39
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
Assuming you have the digits broken down into individual numeric fields ([1], [2], ... [12]) here is the formula for the check field digit (ie [12]):

10 - Mod( 3 * ( [1] + [3] + [5] + [7] + [9] +[11]) + [2] + [4] + [6] + [8] + [10] , 10 )


See:

http://en.wikipedia.org/wiki/Check_digit#UPC
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)