Formula returns unexpected results

[TxH]

Hi all,
I am facing a problem in a formula that converts hexadecimal strings into its integer equivalences.
The highest supported integer value (MAX_SAVE_INTEGER) in PowerApps is 9.007.254.740.991 (2^53-1) what corresponds to '1FFFFFFFFFFFFF' in hex.

Expected result
If the converted value is <= MAX_SAVE_INTEGER, the converted value should be returned. Otherwise, -1 should be the result.

Result
The formula does only return -1 if the converted value is equal to MAX_SAVE_INTEGER +1. In all other cases, the result is not comprehensible to me (0, 1, 2, 3...).

For me, it looks like the overflow of the integer influences and changes the expected result of -1.

Any help is appreciated.

With(
    {   /* remove leading '#' or "0x" */
        hexStr: If(Left(hexStr.Text, 1) = "#", Mid(Upper(hexStr.Text), 2, Len(hexStr.Text) -1),
                     If(Left(Lower(hexStr.Text), 2) = "0x", Mid(Upper(hexStr.Text), 3, Len(hexStr.Text) -2),
                         Upper(hexStr.Text))),
        /* highest supported int value */
        maxSaveInteger: 9007199254740991
    },        
    If( IsMatch(hexStr, "^[A-F0-9]{1,14}$"),
        Sum(
            AddColumns( 
                [1,2,3,4,5,6,7,8,9,10,11,12,13,14],

                "decVal", If( Value <= Len(hexStr), 
                              (Find(Mid(hexStr, Value, 1), "0123456789ABCDEF") -1) * Power(16, Len(hexStr) - Value),
                              0
                          )
            ),
            /** return sum of decimal values
              * fails if sum > 9007199254740991
              */
            If (decVal <= maxSaveInteger, decVal, -1) 
        ),
        /* invalid hex string, return -1 */
        -1
    )
)

A sample app with the issue and some background information can be downloaded from here 

[TxH]

Screenshot of the App:

[v-xida-msft]

Hi @TxH ,

Based on the formula you provided, I could not find any syntax error with it. Currently, I could not come up with any idea to fix this issue.

Please consider submit an assisted support ticket for further help through the following link:

https://powerapps.microsoft.com/en-us/support/pro

Best regards,

[Anonymous · Accepted solution]

@TxH The issue is the 'Numeric Formula' you are using in your Sum() function. It's getting evaluated for EACH ROW not for the entire "decVal" column.

Eg

for error HexNum = 20000000000001 Sum(decVal) returns Sum([-1,0,0,0,0,0,0,0,0,0,0,0,0,1]) = 0;

for error HexNum = 20000000000002 Sum(decVal) returns Sum([-1,0,0,0,0,0,0,0,0,0,0,0,0,2]) = 1;

You can test this with a slightly different number to get a better idea of what's happening

eg 20000000000302 = (-1) + 768 + 2 = 769

I managed to fix this for you by adding a condition inf front of the Sum() function. I'm not 100% sure this meets your needs but with my testing it throws -1 when it needs to. You maybe able to refactor my code down to something easier to read though? 

Here's my code for the 'Result' text  label:

// check for > max(int) => returns unexpected results
With({/* remove leading '#' or "0x" */
hexStr:If(Left(hexStr.Text,1)="#",Mid(Upper(hexStr.Text),2,Len(hexStr.Text)-1),If(Left(Lower(hexStr.Text),2)="0x",Mid(Upper(hexStr.Text),3,Len(hexStr.Text)-2),Upper(hexStr.Text))),
        /* highest supported int value */
maxSaveInteger:9007199254740991},If(IsMatch(hexStr,"^[A-F0-9]{1,14}$"),

// ***** start of extra If() 
If(Sum(AddColumns([1,2,3,4,5,6,7,8,9,10,11,12,13,14],"decVal",If(Value<=Len(hexStr),(Find(Mid(hexStr,Value,1),"0123456789ABCDEF")-1)*Power(16,Len(hexStr)-Value),0)),decVal)<=maxSaveInteger,

Sum(AddColumns([1,2,3,4,5,6,7,8,9,10,11,12,13,14],"decVal",If(Value<=Len(hexStr),(Find(Mid(hexStr,Value,1),"0123456789ABCDEF")-1)*Power(16,Len(hexStr)-Value),0)),

            /** return sum of decimal values
              * fails if sum > 9007199254740991
              */

// ***** remove existing If() from here
decVal),-1),
            

        /* invalid hex string, return -1 */
-1))

Note the // ***** parts where I've added some extra comments.


Let me know if this helps?

[TxH]

@v-xida-msft, a support case (id 119110122001014) is open since November 1st. Until today I have not received an answer showing that the problem has been understood at all. The standard answer I receive is "We are waiting for information from another team".  

[TxH]

@Anonymous, You are absolutely right. Thank you for the solution you came up with - it works.

I knew how to get around the problem but I couldn't find the reason for the issue.

This is the formula you can find in the expectedResult.Text property of the downloadable app:

With(
    {   /* remove leading '#' or "0x" */
        hexStr: If(Left(hexStr.Text, 1) = "#", Mid(Upper(hexStr.Text), 2, Len(hexStr.Text) -1),
                     If(Left(Lower(hexStr.Text), 2) = "0x", Mid(Upper(hexStr.Text), 3, Len(hexStr.Text) -2),
                         Upper(hexStr.Text)))
    },
    /* kudos to Toto from stack overflow for helping with the regex pattern */
    If( IsMatch(hexStr, "^(?:[01][A-F0-9]{13}|[A-F0-9]{1,13})$"),
        Sum(
            AddColumns( 
                [1,2,3,4,5,6,7,8,9,10,11,12,13,14],
                "decVal", If( Value <= Len(hexStr), 
                              (Find(Mid(hexStr, Value, 1), "0123456789ABCDEF") -1) * Power(16, Len(hexStr) - Value), 
                              0
                          )
            ),
            /* return sum of decimal values */
            decVal
        ),
        /* invalid hex string, return -1 */
        -1
    )
)

[Anonymous]

@TxH  No worries, glad you got a solution sorted. To be honest, I enjoyed working on this problem. I learnt new things (I haven't used With() before so that was nice to see in action), gave up a few times, haha, and was intrigued about why you would be using such large numbers. It was an interesting problem mixed amongst a lot of familiar ones.

All the best

[TxH]

@Anonymous, I love to work with the With() function. It's very powerful and perfectly suited to avoid situations where you usually would repeat code (because of the lack of self-defined functions in PA). Your code example could be refactored by using a nested With() function.

With(
    { /* remove leading '#' or "0x" */
      hexStr: 
          If( Left( hexStr.Text, 1) = "#", 
              Mid(Upper(hexStr.Text), 2, Len(hexStr.Text) - 1 ),
              If( Left( Lower(hexStr.Text), 2) = "0x", 
                  Mid(Upper(hexStr.Text), 3, Len(hexStr.Text) - 2 ),
                  Upper(hexStr.Text)
              )
          )
    },
    If( IsMatch(hexStr, "^[A-F0-9]{1,14}$"),
        With(
            {
              /* highest supported int value */
              MAX_SAFE_INTEGER: 9007199254740991,
              decResult: 
                  Sum( 
                      AddColumns( 
                          [1,2,3,4,5,6,7,8,9,10,11,12,13,14],
                          "decVal", 
                              If( Value <= Len(hexStr), 
                                  (Find(Mid(hexStr, Value, 1), "0123456789ABCDEF") - 1) * Power(16, Len(hexStr) - Value),
                                  0
                              )
                      ),
                      decVal
                  )
            },
            /* return sum or -1 */
            If( decResult <= MAX_SAFE_INTEGER,
                decResult,
                -1
            )
        ),
        /* invalid hex string, return -1 */
        -1
    )
)

[Anonymous]

@TxH thanks for posting this refactored code - I figured you'd be able to do this by the way, lol - it has given me a much better understanding of the use of With(). As you say, it is quite powerful and I'm going to start working it into my future powerapps, where I can.

I do really appreciate you taking the time to help me extend my coding vocabulary. It's always a good day when I learn something new.

Cheers