cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sam4
Resolver I
Resolver I

Identify record failing in a Select Action (Error due to conversion from number to string)

Hi, I have a flow that extract the records from a table in Excel and then, using a Select, applies some formulas to format the data. However, when there is some bad data, the Select fails. I would like to get which record (row or column has been impacted by this)

 

For example, I have the following flow. The cells in yell

Sam4_1-1612885748004.png

Select Conversion has these formulas:

 

{
  "ID_New": @{item()?['ID']},
  "DateIn_New": @{int(item()?['DateIn'])},
  "MoneyIn_New": @{float(item()?['MoneyIn'])}
}

 

 

If the data from the table is correct, this flow works fine. However, the issue is when the user insert some bad data. For example, the records in yellow as below

 

Sam4_0-1612885689715.png

  In this case, [Select Conversion] Action will fail because it is trying to convert some data as int("TBC") and, as expected, failing.

For those cases I'm configuring a Run After and sending an email depending if [Select Conversion] was a success or a failure

 

Screenshot 2021-02-09 155717.jpg

The issue is I want to include in the Error email more details about the error. Ideally which cells failed (C4, E4, D6, F6, F9) and if not possible at least the Record or the Column. Something like:

- There was an error in Record 4, 6, 9

- There was an error in Column DateIn, DateOut, MoneyIn and MoneyOut

 

Worst case scenario I could manipulate the error message from the Select and include it in the email, however, this doesn't seems to be possible.

 

InvalidTemplate. The execution of template action 'Select_Conversion' failed: The evaluation of 'query' action 'where' expression '{ "ID_New": "@item()?['ID']", "DateIn_New": "@int(item()?['DateIn'])", "MoneyIn_New": "@float(item()?['MoneyIn'])" }' failed: 'The template language function 'float' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

 

 

Also, and to simplify things, I don't want to use "Apply to Each" as final solution will have more columns and rows and the performance will be impacted.

 

Does anyone know how to put more details on the record that failed or how to extract the error message from the Select?

Any help is more than welcome

 

Kind Regards

S

 

1 REPLY 1
ManishJain
Solution Sage
Solution Sage

HI @Sam4 ,

 

While you are doing select to get the data from excel and process it , before you do this action please use filter action to filter out bad data i.e. Date IN is not empty , Date OUT is TBC . This filter action can be used to filter both ways i.e. bad data and good data and store it in array for further processing to get rows with bad data.

 

Thanks

Helpful resources

Announcements
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

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

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

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Users online (77,606)