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