cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

COMPLICATED IF FUNCTION.

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”),"")

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: COMPLICATED IF FUNCTION.

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.

 

 

View solution in original post

3 REPLIES 3
Highlighted
Community Champion
Community Champion

Re: COMPLICATED IF FUNCTION.

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.

 

 

View solution in original post

Highlighted
Frequent Visitor

Re: COMPLICATED IF FUNCTION.

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

Highlighted
Community Champion
Community Champion

Re: COMPLICATED IF FUNCTION.

+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.

Microsoft Employee
@8bitclassroom

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,313)