Calculating an ISO 6346 Check Digit in Power Apps

An ISO 6346 what now?

I recently had this requirement come to me on a customer project. A bit of googling revealed it hasn’t really been covered much. Hopefully the next person who googles ISO 6346 Power Apps will come across this blog.

Firstly I’ll point towards the one community thread that covers this topic here it dates back to 2017.

The really useful piece of info in there is the Excel formula to calculate the check digit and compare it against the input code:

=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")

Power Apps is quite a lot like Excel in many ways so it’s not such a stretch to adapt this formula to Power Apps, and having this formula in Excel to chop up and see the output of various sections of it is a great help to verify if you’re on the right track with your Power Apps equivalent..

The Excel formula uses some functions that either don’t exist in Power Apps or are quite different. In the end I didn’t really adapt this formula much and made my own based on the calculation steps in the Wikipedia article.

Anyway, let’s get to it. One of the first things you need to do is map each letter in the container code to a number according to this chart:

You could easily do this by creating a collection and then looking it up. I quite like the way the Excel formula uses the Code() function to get the ASCII code of the character and then apply some mathematics to that though, and I wanted to try the same.

The Excel formula does it like this:

=CODE(PROPER(MID(A1,{1,2,3,4},1))) + INT(CODE(PROPER(MID(A1,{1,2,3,4},1))) / 11) - 60

Edit: Since I posted this yesterday, it has been pointed out to me that some container codes don’t calculate properly. I figured the Excel formula I based this on is slightly wrong, and the codes for letters L, V and W are one digit too low. It’s easy enough to adapt the formula for this so I have corrected it with an If() statement that adds one to the code when the character code is 76, 86 or 87. To be honest I’d rather have the mathematical chops to do it properly but the If() will suffice.

I studied Electrical Engineering at University and dropped out, basically because the mathematics was too hard. You can’t solve partial differential equations with If() statements but you can hack your way around being a Power Apps developer so here I am!

In Power Apps we can create a table using the Sequence() function. There are 26 letters in the alphabet so we want a sequence of length 26, and we want the sequence index to start at the ASCII character number for the upper case A, which is 65:

Sequence(26,65)

Now we want to add the corresponding ASCII character to it:

AddColumns(Sequence(26,65),”Char”,Char(Value))

Now if we want to return the ASCII code of any given upper case letter, just do a LookUp():

Now we have this, we can extrapolate the number we need for the check digit calculation. This is copied directly from the Excel formula:

Now the letter G returns 17, which is what we get from the table above.

This is all fine to lookup the code for any given letter, but to calculate the actual check digit we need to work with 2 dimensional data, so each character of the container code needs to be an element in an array. We multiply this code by a number that relates to its position in the array and then sum the result.

The Excel formula uses the SUMPRODUCT() function to do this but in Power Apps we can dig out the amazingly useful Sequence() function again to achieve the same result.

There are 10 characters in the container BIC code (not including the check digit itself), so we want a sequence of 10. We’ll start it at 0 Sequence(10,0)

Now we have that, add a column for the character. Value is the number in the sequence (starting at 0) so we add 1 to that and use the Mid() function to get the character in the corresponding position in the text for each row in the sequence.

AddColumns(Sequence(10,0),”Character”,Mid(“CSQU3054383”,Value+1,1))

In reality we don’t hard code the container code into the formula so use a text input or label or whatever to input the container code. In my case I have a text input box then a label with the Upper() function to convert that to upper case.

AddColumns(Sequence(10,0),”Character”,Mid(lblUpperContainerCode.Text,Value+1,1))

Now that’s sorted we can apply the previous step to this sequence in an AddColumns to get the corresponding code (from the table).

Before we do that though, I’ll refer back to the documentation. Calculation step 2 in the Wikipedia article, which shows a table of numbers we need to slot into each position in the sequence:

To get this number we do 2 ^ Value where Value is the number in the sequence from 0 to 9:

AddColumns(Sequence(10,0),”Character”,Mid(lblUpperContainerCode.Text,Value+1,1),”Sum1″,2^Value)

So, Value is the original sequence number from 0 to 9, Character is the letter and Sum1 is the result of 2^Value

Great, so now let’s apply those numbers we derive from the ASCII codes to this.

AddColumns(AddColumns(AddColumns(Sequence(10,0),”Character”,Mid(lblUpperContainerCode.Text,Value+1,1),”Sum1″,2^Value),”Code1″,LookUp(AddColumns(Sequence(26,65),”Char”,Char(Value)) As CharMap,CharMap.Char=Character).Value),”Code2″,If(Value<=3,Code1+RoundDown(Code1/11,0)-60+If(Code1 in [76,86,87],1,0),Value(Character)))

I think I should start to format the code at this stage because it’s getting a little complex:

AddColumns(
        AddColumns(
            AddColumns(
                Sequence(10,0),
                "Character",
                Mid(lblUpperContainerCode.Text,Value + 1,1),
                "Sum1",
                2 ^ Value
            ),
            "Code1",
            LookUp(
                AddColumns(
                    Sequence(26,65),
                    "Char",
                    Char(Value)
                ) As CharMap,
                CharMap.Char = Character
            ).Value
        ),
        "Code2",
            If(
                Value <= 3,
                Code1 + RoundDown(
                    Code1 / 11,
                    0
                ) - 60 + If(
                    Code1 in [76,86,87],
                    1,
                    0
                ),
                Value(Character)
            )
    )

Here is the result of this formula for the first 4 characters, which are alpha. However, characters > 4 are actually numbers. You might notice the sequence we used to map digits to codes is 26 rows long and starts at A, so these number characters aren’t represented.

In that case we just use the numeric value as the code, hence the If() statement in the expression for Code2
If(Value<=3,Code1+RoundDown(Code1/11,0)-60+If(Code1 in [76,86,87],1,0),Value(Character))

If Value (the number in the sequence) is less than or equal to 3 (it’s one of the first 4 characters) then do the thing, otherwise just return the value of the character itself.

The next thing is to multiple the code/number represented by Code2 in the table above with the base 2 exponent of the Value, which is the Sum1 Column. So Another AddColumns() to create a column called Calc, which is Code2 * Sum1:

AddColumns(
    AddColumns(
        AddColumns(
            AddColumns(
                Sequence(10,0),
                "Character",
                Mid(lblUpperContainerCode.Text,Value + 1,1),
                "Sum1",
                2 ^ Value
            ),
            "Code1",
            LookUp(
                AddColumns(
                    Sequence(26,65),
                    "Char",
                    Char(Value)
                ) As CharMap,
                CharMap.Char = Character
            ).Value
        ),
        "Code2",
           If(
                Value <= 3,
                Code1 + RoundDown(
                    Code1 / 11,
                    0
                ) - 60 + If(
                    Code1 in [76,86,87],
                    1,
                    0
                ),
                Value(Character)
            )
    ),
    "Calc",
    Code2 * Sum1
)

We could drop everything except Calc at this point with a ShowColumns around it all, but there’s really no need.

Now, we still have a 2 dimensional table and we need a single numeric value from this – the check digit.

Firstly sum the Calc Column:

Sum(The whole formula, Calc) gets us 6185.

To get the check digit we do this (from Wikipedia):

You can put this whole formula into a label twice but for the sake of the application performance, we’ll split it out into labels and work with their .Text property outputs:

So, starting with a, put that whole formula in:

Next, b:

Then c:

Now d:

And finally the check digit:

And some conditional logic to apply within the app:

A check digit of 10 is represented by a 0 at the end of the BIC serial number, so we have to evaluate the right most character of the BIC s/n with the right most character of the check digit.

Throw in a bad value; change the second character from S to T without changing the check digit at the end, and we get a sad face because our newly calculated check digit is 5.

Now, you’ll need to use some Regex in Match() to make sure your input conforms to AAAA999999 or these formulas will produce some unexpected results. Try “((?:[a-zA-Z0-9]{4}|/{4})[a-zA-Z0-9]{7})”