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

Expression to error check user input value number text

Am using PA to collect KPI data for my department, and cannot get them to follow the rules consistently so trying to build out error checking.  Just cannot get this to work, and I think this will be quick work for some of you experts.

 

They enter their data into an excel file and PA takes those records and moves them to a SP List.  If their KPI is a percentage, they fill out the Num (Numerator) and Den (Denominator) column and the Act (Actual) column. All of these are supposed to be numbers. If it is just a value KPI, they fill out just the Act column. Sometimes they put a blank in the num and den columns and sometimes a zero. Sometimes they paste their formula into the Act column, or put in text like "NA".  I am trying to validate for all these cases.

 

ACT = 

Logic: If the Act value is blank or has a zero==>if the Den has a number > 0 calculate the percentage num/den

else be sure there is a zero in there if blank, or if the ACT has a valid number use that number.

 

 

 

if(or(empty(items('Apply_to_each_2')?['Act']),equals(items('Apply_to_each_2')?['Act'],float(0))),if(greater(items('Apply_to_each_2')?['Den'],float(0)),div(items('Apply_to_each_2')?['Num'],items('Apply_to_each_2')?['Den']),FLOAT(0)),items('Apply_to_each_2')?['Act'])

 

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Zed_Yer
Frequent Visitor

Found a solution and since I didn't find a good tutorial on this, am going to post it here including the execution I came up with.   The code snippet I can't take credit for--it came from ScottMoore in this post and uses xpath.  We'll look at an input value and determine if it has a value in it or not a value (NaN). From there, we can do any If statements we want to suit our purpose. For me, I am getting rid of unwanted text user input inside an apply to each loop.

 

  1. For my input, list rows present in a table is passing 'NUM' and has a row for blank, 0, 10, TEXT.
  2. Initialize a string variable at 0, which we will use to assign the input values to.
  3. Initialize a float variable at 0, which we will use to construct the xpath formula.

Capture1.JPGCapture3.JPG

 

In the loop, set the variable to they dynamic content of the value you are checking. This could be text, blank, number, etc.

 

In the float variable, this is where the magic happens.  After a quick look at xpath, I think I could have performed all the logic there, but I didn't spend enough time to figure it out. What this code does is return a float if the string input, "vNum", contained a number and a NaN if it contained a blank or text string.

 

xpath(xml(json(concat('{value:"',variables('vNum'),'"}'))),'number(*/text())')

 

I do the error correcting, in this case, when I am writing the value back out.  This is what is in the "expression" test output.

if(greaterOrEquals(variables('vNumtoFloat'),float(0)),items('Apply_to_each')?['Num'],float(0))

 

If xpath returns a float number, its TRUE and will simply use the original value, otherwise it will output a zero if the input was text or blank.  You have to setup a bunch of variables for this, but it seems much cleaner than many catch and isolate logic flows I've seen on here.

 

 

 

View solution in original post

3 REPLIES 3
GeoffRen
Microsoft
Microsoft

Have you considered using Office Scripts (specifically Office Scripts with Power Automate)? This is the 'Run Script' action on the Excel connector. With Office Scripts you can use the 'Run Script' action to execute javascript against a workbook. So you can write a script that literally does what you just stated in your pseudocode.

Zed_Yer
Frequent Visitor

Thanks for pointing me to something new.  I'd like to stick to expressions at the moment before going to learn javascript.  Thanks again.

 

Zed_Yer
Frequent Visitor

Found a solution and since I didn't find a good tutorial on this, am going to post it here including the execution I came up with.   The code snippet I can't take credit for--it came from ScottMoore in this post and uses xpath.  We'll look at an input value and determine if it has a value in it or not a value (NaN). From there, we can do any If statements we want to suit our purpose. For me, I am getting rid of unwanted text user input inside an apply to each loop.

 

  1. For my input, list rows present in a table is passing 'NUM' and has a row for blank, 0, 10, TEXT.
  2. Initialize a string variable at 0, which we will use to assign the input values to.
  3. Initialize a float variable at 0, which we will use to construct the xpath formula.

Capture1.JPGCapture3.JPG

 

In the loop, set the variable to they dynamic content of the value you are checking. This could be text, blank, number, etc.

 

In the float variable, this is where the magic happens.  After a quick look at xpath, I think I could have performed all the logic there, but I didn't spend enough time to figure it out. What this code does is return a float if the string input, "vNum", contained a number and a NaN if it contained a blank or text string.

 

xpath(xml(json(concat('{value:"',variables('vNum'),'"}'))),'number(*/text())')

 

I do the error correcting, in this case, when I am writing the value back out.  This is what is in the "expression" test output.

if(greaterOrEquals(variables('vNumtoFloat'),float(0)),items('Apply_to_each')?['Num'],float(0))

 

If xpath returns a float number, its TRUE and will simply use the original value, otherwise it will output a zero if the input was text or blank.  You have to setup a bunch of variables for this, but it seems much cleaner than many catch and isolate logic flows I've seen on here.

 

 

 

View solution in original post

Helpful resources

Announcements
MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (69,137)