Discussions

View Only

How to calculate a UPC check digit in QuickBase?

• 1.  How to calculate a UPC check digit in QuickBase?

Posted 02-09-2015 16:52

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

• 2.  RE: How to calculate a UPC check digit in QuickBase?

Top
Contributor
Posted 02-09-2015 17:07
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

• 3.  RE: How to calculate a UPC check digit in QuickBase?

Top
Contributor
Posted 11-26-2018 04:01
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)