AnsweredAssumed Answered

New to FileMaker Validating a Container Number

Question asked by zjghazal on Oct 3, 2018

I am new to FileMaker I hope someone can help, I am trying to create a short Script validating an ISO 6346 compliant shipping container number: below are the calculation steps I have an Excel array Formula that works I hope it will help too 

A1= "Container Number Field Value"

 

=IF(LEN(A1)=11,IF(MOD(MOD(SUMPRODUCT(CODE(PROPER(MID(A1,{1,2,3,4},1)))+INT(CODE(PROPER(MID(A1,{1,2,3,4},1)))/11)-60,{1,2,4,8})+  SUMPRODUCT(MID(A1,{5,6,7,8,9,10},1)+0,{16,32,64,128,256,512}),11),10)=RIGHT(A1)+0,"VALID","INVALID"),"INVALID")

 

Calculation Step 1[edit]

An equivalent numerical value is assigned to each letter of the alphabet, beginning with 10 for the letter A (11 and multiples thereof are omitted):

ABCDEFGHIJKLM
10121314151617181920212324
NOPQRSTUVWXYZ
25262728293031323435363738

The individual digits of the serial number keep their numeric value.

Calculation Step 2[edit]

Each of the numbers calculated in step 1 is multiplied by 2 position, where position is the exponent to base 2. Position starts at 0, from left to right.

The following table shows the multiplication factors:

1. nbr2. nbr3. nbr4. nbr5. nbr6. nbr7. nbr8. nbr9. nbr10. nbr
20212223242526272829
1248163264128256512

Calculation Step 3[edit]

  1. Sum up all results of Step 2
  2. Divide them by 11
  3. Round the result down towards zero i.e. make the result a whole number (integer)
  4. Multiply the integer value by 11
  5. Subtract result of (iv) from result of : This is the check digit.

If the final difference is 10, then the check digit becomes 0. To ensure that this does not happen, the standard recommends that serial numbers should not be used which produce a final difference of 10; however, there are containers in the market which do not follow this recommendation, so handling this case has to be included if a check digit calculator is programmed.

Notice that step (ii) to (v) is a calculation of the remainder found after division of by 11. Most programming languages have a modulo operator for this. Attention should be paid on how it is working in the language chosen; i. e. if it is giving back the decimal rest or the integer rest in order to get proper results. 11 is used as divisor because a container number has 11 letters and digits in total. In step 1 the numbers 11, 22 and 33 are left out as they are multiples of the divisor.

Example[edit]

CSQU305438Calc.
13302832305438
×1248163264128256512
136011225648032051276840966185 [a]
b) Division by 11:562.272...
c) Erase decimal digits:562
d) Multiply by 11:6182
a) minus d) = Check Digit:3

 

Thank you in advance

Outcomes