cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ATL_COM_LAB
Level: Powered On

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
Meneghino
Level 10

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
Meneghino
Level 10

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

ATL_COM_LAB
Level: Powered On

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

mr-dang
Level 10

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
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (10,440)