Getting error: "Input string was not in a correct format"


I am using an if activity to run a test on an excel table. The code I am using is: Row(“Expense Type”).ToString = “Hotel” And Row(“City/Location”).ToString = “Boston” And Convert.ToInt32(regex.Replace(“ExpenseAmount(rpt)”,"[,]","").ToString) >Boston

The Boston variable at the end has been declared as 300. I also show that System.Text.RegularExpressions has been imported so I can use Regex. I’m not sure why this is not working. Any ideas?

What is ExpenseAmount(rpt) ?

I am posting a sample data sheet below this that closely resembles the one I am using. Expense Amount (rpt) is a numerical column in the data.
dummy variables.xlsx (10.5 KB)

row(“ExpenseType”).ToString = “Hotel” And Row(“City_Location”).ToString = “Boston” And Convert.ToDouble(row(“ExpenseAmount (rpt)”).ToString) > 300.00

Try this please.

I tweaked it a little to match my column headers in my actual data. row(“Expense Type”).ToString = “Hotel” And Row(“City/Location”).ToString = “Boston” And Convert.ToDouble(row(“Expense Amount (rpt)”).ToString) > 300.00

When trying the code, it gave me the same error. “Input string was not in a correct format”.

I tried removing the decimals on 300.00 and changing it to Convert.ToInt32 but the problem persists.

Main.xaml (9.3 KB)

1 Like

Thank you so much, it works but would you be able to tell me what exactly it is doing?

Why are there two if statements? The second if statement is the actual test, what is the function of the first statement?

The first one is a tryParse which converts the string representation of a number to its double-precision floating-point number equivalent. A return value indicates whether the conversion succeeded or failed. If it fails to return an equivalent double value, the else block is executed.

The second If is the actual condition

I’ve been trying to incorporate this code into my main project. The script runs without errors but the way the script is set up is that it pulls aside the code with an error and feeds it into a new table (TestDT).

The new test found at If 8.4 runs without errors but does not flag the errors out of the table. Do you have any ideas?
Copy of TE YTD 2019.xlsx (16.9 KB)
ExpenseApproval fixed.xaml (109.6 KB)
TestDT.xlsx (8.8 KB)

Copy of TE YTD 2019 is my data file.

Expense Approval Fixed is my main project file

TestDT is a file filled with flagged violations.

That is because the tryparse returns false we set the value to 0.
If you need to capture errors in a different table … in the first try parse else section set the getExpense to -1.
You can then update the error s table if getExpense is -1

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.