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):

A | B | C | D | E | F | G | H | I | J | K | L | M |

10 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 23 | 24 |

N | O | P | Q | R | S | T | U | V | W | X | Y | Z |

25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 | 34 | 35 | 36 | 37 | 38 |

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. nbr | 2. nbr | 3. nbr | 4. nbr | 5. nbr | 6. nbr | 7. nbr | 8. nbr | 9. nbr | 10. nbr |

2^{0} | 2^{1} | 2^{2} | 2^{3} | 2^{4} | 2^{5} | 2^{6} | 2^{7} | 2^{8} | 2^{9} |

1 | 2 | 4 | 8 | 16 | 32 | 64 | 128 | 256 | 512 |

#### Calculation Step 3[edit]

- Sum up all results of Step 2
- Divide them by 11
- Round the result down towards zero i.e. make the result a whole number (integer)
- Multiply the integer value by 11
- 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]

C | S | Q | U | 3 | 0 | 5 | 4 | 3 | 8 | Calc. | |
---|---|---|---|---|---|---|---|---|---|---|---|

13 | 30 | 28 | 32 | 3 | 0 | 5 | 4 | 3 | 8 | ||

× | 1 | 2 | 4 | 8 | 16 | 32 | 64 | 128 | 256 | 512 | |

13 | 60 | 112 | 256 | 48 | 0 | 320 | 512 | 768 | 4096 | 6185 [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

I have taken the liberty of creating custom functions and a solution example for this validation.

There are four (4) custom functions. You could do it all in one but it would be harder to troubleshoot and maintain. And some of these may be useful separately.

You will see that I have included a few additional levels of validation in the ISO6346Validate custom function – length is 11 char; first four chars are letters, last seven are digits; fourth is U, J or Z. These will all fail with error text.

All you need to do is to import all four custom functions into your solution. And then use the ISO6346Validate function to validate as needed.

Let me know if you have questions.

Cheers, David