web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro - Series II
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / COMPLICATED IF FUNCTION.
Power Apps
Answered

COMPLICATED IF FUNCTION.

(0) ShareShare
ReportReport
Posted on by ATL_COM_LAB 11

I am trying to make an app to collect info as containers are loaded in the field. i have FINALLY suceeded in making the basic app work, and it does collect info. Now, I am trying to add some validation to different fields.

 

The CONTAINER field will contain a container number which has a specific formula (ISO 6346) to evaluate if the number is correct. More info and code samples are HERE.

 

We were using Excel to run an If function and it works great, but when trying to adapt that If function to PA i get  lot of errors.

I know PA can run If, Len, and Mid; so is it just too much for PA to handle? or am i going about this the wrong way?

 

I would greatly appreciate any ideas.

-TIA

(i am trying to use containerid as a global variable with a button to set it and trigger the validation)

If(Len(containerid)=11,
If((Mid(containerid,11,1)*1)=(1*SUBSTITUTE((If(Mid(containerid,1,1)="A",10,If(Mid(containerid,1,1)="B",12,If(Mid(containerid,1,1)="C",13,If(Mid(containerid,1,1)="D",14,

If(Mid(containerid,1,1)="E",15,If(Mid(containerid,1,1)="F",16,If(Mid(containerid,1,1)="G",17,If(Mid(containerid,1,1)="H",18,If(Mid(containerid,1,1)="I",19,

If(Mid(containerid,1,1)="J",20,If(Mid(containerid,1,1)="K",21,If(Mid(containerid,1,1)="L",23,If(Mid(containerid,1,1)="M",24,If(Mid(containerid,1,1)="N",25,

If(Mid(containerid,1,1)="O",26,If(Mid(containerid,1,1)="P",27,If(Mid(containerid,1,1)="Q",28,If(Mid(containerid,1,1)="R",29,If(Mid(containerid,1,1)="S",30,

If(Mid(containerid,1,1)="T",31,If(Mid(containerid,1,1)="U",32,If(Mid(containerid,1,1)="V",34,If(Mid(containerid,1,1)="W",35,If(Mid(containerid,1,1)="X",36,

If(Mid(containerid,1,1)="Y",37,If(Mid(containerid,1,1)="Z",38,FALSE)))))))))))))))))))))))))))+

(If(Mid(containerid,2,1)="A",10,If(Mid(containerid,2,1)="B",12,If(Mid(containerid,2,1)="C",13,If(Mid(containerid,2,1)="D",14,If(Mid(containerid,2,1)="E",15,

If(Mid(containerid,2,1)="F",16,If(Mid(containerid,2,1)="G",17,If(Mid(containerid,2,1)="H",18,If(Mid(containerid,2,1)="I",19,If(Mid(containerid,2,1)="J",20,

If(Mid(containerid,2,1)="K",21,If(Mid(containerid,2,1)="L",23,If(Mid(containerid,2,1)="M",24,If(Mid(containerid,2,1)="N",25,If(Mid(containerid,2,1)="O",26,

If(Mid(containerid,2,1)="P",27,If(Mid(containerid,2,1)="Q",28,If(Mid(containerid,2,1)="R",29,If(Mid(containerid,2,1)="S",30,If(Mid(containerid,2,1)="T",31,

If(Mid(containerid,2,1)="U",32,If(Mid(containerid,2,1)="V",34,If(Mid(containerid,2,1)="W",35,If(Mid(containerid,2,1)="X",36,If(Mid(containerid,2,1)="Y",37,

If(Mid(containerid,2,1)="Z",38,FALSE))))))))))))))))))))))))))*2)+
(If(Mid(containerid,3,1)="A",10,If(Mid(containerid,3,1)="B",12,If(Mid(containerid,3,1)="C",13,If(Mid(containerid,3,1)="D",14,If(Mid(containerid,3,1)="E",15,

If(Mid(containerid,3,1)="F",16,If(Mid(containerid,3,1)="G",17,If(Mid(containerid,3,1)="H",18,If(Mid(containerid,3,1)="I",19,If(Mid(containerid,3,1)="J",20,

If(Mid(containerid,3,1)="K",21,If(Mid(containerid,3,1)="L",23,If(Mid(containerid,3,1)="M",24,If(Mid(containerid,3,1)="N",25,If(Mid(containerid,3,1)="O",26,

If(Mid(containerid,3,1)="P",27,If(Mid(containerid,3,1)="Q",28,If(Mid(containerid,3,1)="R",29,If(Mid(containerid,3,1)="S",30,If(Mid(containerid,3,1)="T",31,

If(Mid(containerid,3,1)="U",32,If(Mid(containerid,3,1)="V",34,If(Mid(containerid,3,1)="W",35,If(Mid(containerid,3,1)="X",36,If(Mid(containerid,3,1)="Y",37,

If(Mid(containerid,3,1)="Z",38,FALSE))))))))))))))))))))))))))*4)+
(If(Mid(containerid,4,1)="A",10,If(Mid(containerid,4,1)="B",12,If(Mid(containerid,4,1)="C",13,If(Mid(containerid,4,1)="D",14,If(Mid(containerid,4,1)="E",15,

If(Mid(containerid,4,1)="F",16,If(Mid(containerid,4,1)="G",17,If(Mid(containerid,4,1)="H",18,If(Mid(containerid,4,1)="I",19,If(Mid(containerid,4,1)="J",20,

If(Mid(containerid,4,1)="K",21,If(Mid(containerid,4,1)="L",23,If(Mid(containerid,4,1)="M",24,If(Mid(containerid,4,1)="N",25,If(Mid(containerid,4,1)="O",26,

If(Mid(containerid,4,1)="P",27,If(Mid(containerid,4,1)="Q",28,If(Mid(containerid,4,1)="R",29,If(Mid(containerid,4,1)="S",30,If(Mid(containerid,4,1)="T",31,

If(Mid(containerid,4,1)="U",32,If(Mid(containerid,4,1)="V",34,If(Mid(containerid,4,1)="W",35,If(Mid(containerid,4,1)="X",36,If(Mid(containerid,4,1)="Y",37,

If(Mid(containerid,4,1)="Z",38,FALSE))))))))))))))))))))))))))*8)+
(Mid(containerid,5,1)*16)+(Mid(containerid,6,1)*32)+(Mid(containerid,7,1)*64)+(Mid(containerid,8,1)*128)+(Mid(containerid,9,1)*256)+(Mid(containerid,10,1)*512)-11*ROUNDDOWN((((If(Mid(containerid,1,1)="A",10,If(Mid(containerid,1,1)="B",12,If(Mid(containerid,1,1)="C",13,

If(Mid(containerid,1,1)="D",14,If(Mid(containerid,1,1)="E",15,If(Mid(containerid,1,1)="F",16,If(Mid(containerid,1,1)="G",17,If(Mid(containerid,1,1)="H",18,

If(Mid(containerid,1,1)="I",19,If(Mid(containerid,1,1)="J",20,If(Mid(containerid,1,1)="K",21,If(Mid(containerid,1,1)="L",23,If(Mid(containerid,1,1)="M",24,

If(Mid(containerid,1,1)="N",25,If(Mid(containerid,1,1)="O",26,If(Mid(containerid,1,1)="P",27,If(Mid(containerid,1,1)="Q",28,If(Mid(containerid,1,1)="R",29,

If(Mid(containerid,1,1)="S",30,If(Mid(containerid,1,1)="T",31,If(Mid(containerid,1,1)="U",32,If(Mid(containerid,1,1)="V",34,If(Mid(containerid,1,1)="W",35,

If(Mid(containerid,1,1)="X",36,If(Mid(containerid,1,1)="Y",37,If(Mid(containerid,1,1)="Z",38,FALSE)))))))))))))))))))))))))))+(If(Mid(containerid,2,1)="A",10,If(Mid(containerid,2,1)="B",12,If(Mid(containerid,2,1)="C",13,If(Mid(containerid,2,1)="D",14,If(Mid(containerid,2,1)="E",15,

If(Mid(containerid,2,1)="F",16,If(Mid(containerid,2,1)="G",17,If(Mid(containerid,2,1)="H",18,If(Mid(containerid,2,1)="I",19,If(Mid(containerid,2,1)="J",20,

If(Mid(containerid,2,1)="K",21,If(Mid(containerid,2,1)="L",23,If(Mid(containerid,2,1)="M",24,If(Mid(containerid,2,1)="N",25,If(Mid(containerid,2,1)="O",26,

If(Mid(containerid,2,1)="P",27,If(Mid(containerid,2,1)="Q",28,If(Mid(containerid,2,1)="R",29,If(Mid(containerid,2,1)="S",30,If(Mid(containerid,2,1)="T",31,

If(Mid(containerid,2,1)="U",32,If(Mid(containerid,2,1)="V",34,If(Mid(containerid,2,1)="W",35,If(Mid(containerid,2,1)="X",36,If(Mid(containerid,2,1)="Y",37,

If(Mid(containerid,2,1)="Z",38,FALSE))))))))))))))))))))))))))*2)+(If(Mid(containerid,3,1)="A",10,If(Mid(containerid,3,1)="B",12,If(Mid(containerid,3,1)="C",13,If(Mid(containerid,3,1)="D",14,If(Mid(containerid,3,1)="E",15,

If(Mid(containerid,3,1)="F",16,If(Mid(containerid,3,1)="G",17,If(Mid(containerid,3,1)="H",18,If(Mid(containerid,3,1)="I",19,If(Mid(containerid,3,1)="J",20,

If(Mid(containerid,3,1)="K",21,If(Mid(containerid,3,1)="L",23,If(Mid(containerid,3,1)="M",24,If(Mid(containerid,3,1)="N",25,If(Mid(containerid,3,1)="O",26,

If(Mid(containerid,3,1)="P",27,If(Mid(containerid,3,1)="Q",28,If(Mid(containerid,3,1)="R",29,If(Mid(containerid,3,1)="S",30,If(Mid(containerid,3,1)="T",31,

If(Mid(containerid,3,1)="U",32,If(Mid(containerid,3,1)="V",34,If(Mid(containerid,3,1)="W",35,If(Mid(containerid,3,1)="X",36,If(Mid(containerid,3,1)="Y",37,

If(Mid(containerid,3,1)="Z",38,FALSE))))))))))))))))))))))))))*4)+(If(Mid(containerid,4,1)="A",10,If(Mid(containerid,4,1)="B",12,If(Mid(containerid,4,1)="C",13,If(Mid(containerid,4,1)="D",14,If(Mid(containerid,4,1)="E",15,

If(Mid(containerid,4,1)="F",16,If(Mid(containerid,4,1)="G",17,If(Mid(containerid,4,1)="H",18,If(Mid(containerid,4,1)="I",19,If(Mid(containerid,4,1)="J",20,

If(Mid(containerid,4,1)="K",21,If(Mid(containerid,4,1)="L",23,If(Mid(containerid,4,1)="M",24,If(Mid(containerid,4,1)="N",25,If(Mid(containerid,4,1)="O",26,

If(Mid(containerid,4,1)="P",27,If(Mid(containerid,4,1)="Q",28,If(Mid(containerid,4,1)="R",29,If(Mid(containerid,4,1)="S",30,If(Mid(containerid,4,1)="T",31,

If(Mid(containerid,4,1)="U",32,If(Mid(containerid,4,1)="V",34,If(Mid(containerid,4,1)="W",35,If(Mid(containerid,4,1)="X",36,If(Mid(containerid,4,1)="Y",37,

If(Mid(containerid,4,1)="Z",38,FALSE))))))))))))))))))))))))))*8)+(Mid(containerid,5,1)*16)+(Mid(containerid,6,1)*32)+(Mid(containerid,7,1)*64)+(Mid(containerid,8,1)*128)+(Mid(containerid,9,1)*256)+(Mid(containerid,10,1)*512))/11),0),10,0)),”VALID”,”NOT_VALID”),"")

Categories:
Building Power Apps
I have the same question (0)
  • Verified answer
    Meneghino Profile Picture
    Meneghino 6,949 on at

    Hi @ATL_COM_LAB

     

    It can be done much more succinctly, and the Excel formula you want to emulate is this (from Wikipedia)

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

    In PowerApps, unfortunately there in no equivalent to CODE, but you can reporduce it by storing a local static table with two columns: letter and number and then use the LookUp function to get the number.  The SUMPRODUCT you can reproduce using the PowerApps Table function with AddColumns and then Sum.

     

    Please try it on your own, and if you do not manage, I can have a look again next week.

     

     

  • ATL_COM_LAB Profile Picture
    ATL_COM_LAB 11 on at

    i figured out another way. it may not be as elegant, but it works so far.

    i used quite a few different fields and broke the container number down and then proceeded to do the math on them. then tool the group of fields and set visable to false. using the "VALID" result i triggered a message in the screen of my app.

     

    i appreciate the ideas.

    CHECKID.jpg

  • Brian Dang Profile Picture
    Brian Dang 3,976 on at

    +1 to what @Meneghino and @ATL_COM_LAB suggested.

     

    In PowerApps, you no longer have to nest IF statements--you can just add another comma, condition, and result:

    If(condition1,result1,condition2,result2,conditionX,resultX,else value)

    I also recommend using a datasource as a lookup table so you can simplify your formula.

  • WillPage Profile Picture
    WillPage 2,337 Super User 2026 Season 1 on at

    I came across this thread looking for the same thing. Since 2017 we now have the Sequence() function that can be used to quickly create a collection of text characters against ASCII codes like this:

    AddColumns(Sequence(128,0),"Char",Char(Value))

    So to find the ASCII code for the letter G for example, go

    LookUp(AddColumns(Sequence(128,0),"Char",Char(Value)),Char = "G").Value

     

    WillPage_0-1618875560510.png

    Hope this helps someone else who might come across this 4 year old thread!

  • Verified answer
    WillPage Profile Picture
    WillPage 2,337 Super User 2026 Season 1 on at

    OK so here's the solution in PowerApps. The input is a label that converts a text input to upper case. The label which contains the upper case container code is called lblUpperContainerCode. This formula returns the check digit which you can compare to Right(lblUpperContainerCode.Text,1) to get a boolean true/false.

    Sum(
 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,
 Value(Character)
 )
 ),
 "Calc",
 Code2 * Sum1
 ),
 Calc
) - RoundDown(
 Sum(
 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,
 Value(Character)
 )
 ),
 "Calc",
 Code2 * Sum1
 ),
 Calc
 ) / 11,
 0
) *11

     

  • ATL_COM_LAB Profile Picture
    ATL_COM_LAB 11 on at

    WillPage Thanks. your solution does work with one adjustment and I'm just noting that here like you said incase anyone else comes across this old thread. You have to take into account that 10 and 0 are the same for check digits. So I would compare Right(lblUpperContainerCode.Text,1) to Right(YourSolution_lbl.Text,1) and that seems to match the alternate method we have been using for 4 years now. Thanks again for a more elegant solution.

  • ATL_COM_LAB Profile Picture
    ATL_COM_LAB 11 on at

    I may have spoken too soon, Try this container TCLU4312577. I'm getting different results than your formula. 

    Check-Digit-Explanation-Screen.PNG
  • WillPage Profile Picture
    WillPage 2,337 Super User 2026 Season 1 on at

    You are correct. My formula is picking the the wrong character to number mapping for the letter L. I get 22, correct number is 23:

    WillPage_0-1618952599534.png

    WillPage_1-1618952638214.png

     

    The error is actually in that excel formula I copied from earlier in this thread:

    WillPage_2-1618952752724.png

     

    I'll see if I can correct this and update the formula.

  • WillPage Profile Picture
    WillPage 2,337 Super User 2026 Season 1 on at

    Ok @ATL_COM_LAB I had a look at this. Trying to clever and actually figure out a formula to get the right number is hard. I'm no mathematician, so I hacked it like this:

    Find this portion of the code:

     

     If(
 Value <= 3,
 Code1 + RoundDown(
 Code1 / 11,
 0
 ) - 60,
 Value(Character)
 )

     

    and update it like this:

     

     If(
 Value <= 3,
 If(Code1 = 76 || Code1 = 86 || Code1 = 87, 
 (Code1 + RoundDown(
 Code1 / 11,
 0
 ) - 60) + 1,Code1 + RoundDown(
 Code1 / 11,
 0
 ) - 60),
 Value(Character)
 )

    It actually appears twice in the code I pasted above, but as I'm sure you guessed you can have a label with the first bit of the code (the non-rounded sum before the subtract) then use its Text property to continue with the calculation in other labels so you don't repeat the formula.

     

  • WillPage Profile Picture
    WillPage 2,337 Super User 2026 Season 1 on at

    Slightly cleaner version:

    If(
 Value <= 3,
 Code1 + RoundDown(
 Code1 / 11,
 0
 ) - 60 + If(
 Code1 in [76,86,87],
 1,
 0
 ),
 Value(Character)
 )

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities
🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro - Series II

Quick Links

Season of Sharing Community Challenge Launch!

Jump in, show your community spirit, and win prizes!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the May Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
Valantis Profile Picture

Valantis 483

#2
WarrenBelz Profile Picture

WarrenBelz 399 Most Valuable Professional

#3
11manish Profile Picture

11manish 327

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans