Troubleshooting Unexpected Flow Behavior

(0) Share

Report

Posted on by Community Power Pla...

Good morning,

I'm trying to troubleshoot a flow that's not behaving quite as intended.

Background:

The purpose of this flow is to capture data from a daily email and add it to a Dataverse table.

Issues:

1. The flow will sometimes fail, but there is no explanation of what specifically went wrong when this happens:

2. Some data from the source do not make it into the Dataverse table most days

Flow:

Flow Steps:

The flow is triggered every morning when the source excel file is added to a designated folder on SharePoint. The file is placed there by another flow. No issues with this part.

Since the source file does not have the data in a table, I am using an Office Script to add one. This is what the source file looks like after the script has run:

There is a Compose action for each of the 5 data elements (columns.) The expressions within these are:

items('Apply_to_each_2')?['ColumnName']

Within the 'Add a new row' action, the expression used for each column is this:

if(equals(outputs('Compose_ColumnName'),''),null,if(equals(outputs('Compose_ColumnName'),'#MULTIVALUE'),null,outputs('Compose_ColumnName')))

I'm using this expression because my source data sometimes has blank fields where a date/time is expected, and (for whatever reason) sometimes arrives with #MULTIVALUE in one or more cells.

The end result, for the data source pictured above, was this:

As you can see, the second row was not added to the table, and the row that did get added is missing its "Awaiting Bed" and "Departure" date/time.

Any thoughts regarding the issue would be very much appreciated.

Thanks!

I have the same question (0)

All responses (6)

Answers (0)

Community Power Pla... on at

Like (0)

Report

Re: Troubleshooting Unexpected Flow Behavior

Here is the most pertinent bit of the raw outputs from my 'List rows present in a table' action:

 "value": [
 {
 "@odata.etag": "",
 "ItemInternalId": "1b90a42a-61f2-4f0a-91f5-80554861ded3",
 "FIN": "564959947",
 "AwaitingBed": "2022-12-01T16:30:14.000Z",
 "ReadyForEval": "#MULTIVALUE",
 "XAcceptance": "2022-12-02T10:48:05.000Z",
 "Departure": "2022-12-02T12:10:25.000Z"
 },
 {
 "@odata.etag": "",
 "ItemInternalId": "d5a990ff-74d9-4c5b-9051-47f0d86b9e99",
 "FIN": "564960710",
 "AwaitingBed": "2022-12-02T02:08:06.000Z",
 "ReadyForEval": "44897.0808796296",
 "XAcceptance": "",
 "Departure": "2022-12-02T11:24:55.000Z"

And here is the raw output for my 'Add a new row' action:

{
 "statusCode": 201,
 "headers": {
 "x-ms-service-request-id": "ca800832-8f38-4cc0-be63-8959e27bce78,c36acffd-cf21-41db-8f12-80e4bd2beb88",
 "Cache-Control": "no-cache",
 "Set-Cookie": "ARRAffinity=b209f1c39b044c7e449babe3bcc430fa6676a1f5b89cf3b13329061ced9758b5eb29bb4b19df1459d2ea781b605f33064b9fea1f144b525b9d7676625c4ffb5408DAD5E1BDA7267D562618370; domain=X.crm.dynamics.com; path=/; secure; HttpOnly,ReqClientId=35a29542-747e-4e39-b689-ed77c337147e; expires=Sun, 04-Dec-2072 09:54:35 GMT; path=/; secure; HttpOnly,last_commit_time=2022-12-04 09:54:37Z; expires=Sun, 04-Dec-2022 09:55:37 GMT; path=/; secure; HttpOnly,ARRAffinity=b209f1c39b044c7e449babe3bcc430fa6676a1f5b89cf3b13329061ced9758b5eb29bb4b19df1459d2ea781b605f33064b9fea1f144b525b9d7676625c4ffb5408DAD5E1BDA7267D562618370; domain=X.crm.dynamics.com; path=/; secure; HttpOnly",
 "Strict-Transport-Security": "max-age=31536000; includeSubDomains",
 "REQ_ID": "c36acffd-cf21-41db-8f12-80e4bd2beb88",
 "AuthActivityId": "925979bf-1691-45b6-82ed-80ce6e517f9d",
 "x-ms-dop-hint": "8",
 "x-ms-ratelimit-time-remaining-xrm-requests": "1,200.00",
 "x-ms-ratelimit-burst-remaining-xrm-requests": "7999",
 "Preference-Applied": "return=representation,odata.include-annotations=\"*\"",
 "OData-Version": "4.0",
 "X-Source": "12211513816134211652351679622846466917069981651112223182051521257310213793138106,412252442315419920423710310512141108995214248956221419129512191702199911519292152",
 "Public": "OPTIONS,GET,HEAD,POST",
 "Date": "Sun, 04 Dec 2022 09:54:36 GMT",
 "Content-Length": "10615",
 "Allow": "OPTIONS,GET,HEAD,POST",
 "Content-Type": "application/json; odata.metadata=full",
 "Expires": "-1"
 },
 "body": {
 "@odata.context": "https://X.crm.dynamics.com/api/data/v9.1/$metadata#cr532_bhdatas/$entity",
 "@odata.type": "#Microsoft.Dynamics.CRM.cr532_bhdata",
 "@odata.id": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)",
 "@odata.etag": "W/\"2801199\"",
 "@odata.editLink": "cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)",
 "_owningbusinessunit_value@OData.Community.Display.V1.FormattedValue": "X",
 "_owningbusinessunit_value@Microsoft.Dynamics.CRM.associatednavigationproperty": "owningbusinessunit",
 "_owningbusinessunit_value@Microsoft.Dynamics.CRM.lookuplogicalname": "businessunit",
 "_owningbusinessunit_value@odata.type": "#Guid",
 "_owningbusinessunit_value": "f93262f7-ad1b-ed11-b83b-00224803cc07",
 "cr532_Xacceptance@OData.Community.Display.V1.FormattedValue": "12/2/2022 10:48 AM",
 "cr532_Xacceptance@odata.type": "#DateTimeOffset",
 "cr532_Xacceptance": "2022-12-02T10:48:05Z",
 "statecode@OData.Community.Display.V1.FormattedValue": "Active",
 "statecode": 0,
 "statuscode@OData.Community.Display.V1.FormattedValue": "Active",
 "statuscode": 1,
 "_createdby_value@OData.Community.Display.V1.FormattedValue": "E. Andrew Raposo",
 "_createdby_value@Microsoft.Dynamics.CRM.lookuplogicalname": "systemuser",
 "_createdby_value@odata.type": "#Guid",
 "_createdby_value": "1d7d5afd-ad1b-ed11-b83b-00224803cc07",
 "timezoneruleversionnumber@OData.Community.Display.V1.FormattedValue": "0",
 "timezoneruleversionnumber": 0,
 "cr532_name": "1354",
 "_ownerid_value@OData.Community.Display.V1.FormattedValue": "E. Andrew Raposo",
 "_ownerid_value@Microsoft.Dynamics.CRM.associatednavigationproperty": "ownerid",
 "_ownerid_value@Microsoft.Dynamics.CRM.lookuplogicalname": "systemuser",
 "_ownerid_value@odata.type": "#Guid",
 "_ownerid_value": "1d7d5afd-ad1b-ed11-b83b-00224803cc07",
 "modifiedon@OData.Community.Display.V1.FormattedValue": "12/4/2022 4:54 AM",
 "modifiedon@odata.type": "#DateTimeOffset",
 "modifiedon": "2022-12-04T09:54:36Z",
 "_modifiedby_value@OData.Community.Display.V1.FormattedValue": "E. Andrew Raposo",
 "_modifiedby_value@Microsoft.Dynamics.CRM.lookuplogicalname": "systemuser",
 "_modifiedby_value@odata.type": "#Guid",
 "_modifiedby_value": "1d7d5afd-ad1b-ed11-b83b-00224803cc07",
 "_owninguser_value@Microsoft.Dynamics.CRM.lookuplogicalname": "systemuser",
 "_owninguser_value@odata.type": "#Guid",
 "_owninguser_value": "1d7d5afd-ad1b-ed11-b83b-00224803cc07",
 "cr532_bhdataid@odata.type": "#Guid",
 "cr532_bhdataid": "7f0687a8-b973-ed11-81aa-000d3a5cc79b",
 "versionnumber@OData.Community.Display.V1.FormattedValue": "2,801,199",
 "versionnumber@odata.type": "#Int64",
 "versionnumber": 2801199,
 "cr532_FIN@OData.Community.Display.V1.FormattedValue": "564,959,947",
 "cr532_FIN": 564959947,
 "createdon@OData.Community.Display.V1.FormattedValue": "12/4/2022 4:54 AM",
 "createdon@odata.type": "#DateTimeOffset",
 "createdon": "2022-12-04T09:54:36Z",
 "owningbusinessunit@odata.associationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/owningbusinessunit/$ref",
 "owningbusinessunit@odata.navigationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/owningbusinessunit",
 "ownerid@odata.associationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/ownerid/$ref",
 "ownerid@odata.navigationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/ownerid",
 "createdby@odata.associationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/createdby/$ref",
 "createdby@odata.navigationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/createdby",
 "createdonbehalfby@odata.associationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/createdonbehalfby/$ref",
 "createdonbehalfby@odata.navigationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/createdonbehalfby",
 "modifiedby@odata.associationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/modifiedby/$ref",
 "modifiedby@odata.navigationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/modifiedby",
 "modifiedonbehalfby@odata.associationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/modifiedonbehalfby/$ref",
 "modifiedonbehalfby@odata.navigationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/modifiedonbehalfby",
 "owninguser@odata.associationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/owninguser/$ref",
 "owninguser@odata.navigationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/owninguser",
 "owningteam@odata.associationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/owningteam/$ref",
 "owningteam@odata.navigationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/owningteam",
 "cr532_bhdata_SyncErrors@odata.associationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/cr532_bhdata_SyncErrors/$ref",
 "cr532_bhdata_SyncErrors@odata.navigationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/cr532_bhdata_SyncErrors",
 "cr532_bhdata_AsyncOperations@odata.associationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/cr532_bhdata_AsyncOperations/$ref",
 "cr532_bhdata_AsyncOperations@odata.navigationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/cr532_bhdata_AsyncOperations",
 "cr532_bhdata_MailboxTrackingFolders@odata.associationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/cr532_bhdata_MailboxTrackingFolders/$ref",
 "cr532_bhdata_MailboxTrackingFolders@odata.navigationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/cr532_bhdata_MailboxTrackingFolders",
 "cr532_bhdata_UserEntityInstanceDatas@odata.associationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/cr532_bhdata_UserEntityInstanceDatas/$ref",
 "cr532_bhdata_UserEntityInstanceDatas@odata.navigationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/cr532_bhdata_UserEntityInstanceDatas",
 "cr532_bhdata_ProcessSession@odata.associationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/cr532_bhdata_ProcessSession/$ref",
 "cr532_bhdata_ProcessSession@odata.navigationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/cr532_bhdata_ProcessSession",
 "cr532_bhdata_BulkDeleteFailures@odata.associationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/cr532_bhdata_BulkDeleteFailures/$ref",
 "cr532_bhdata_BulkDeleteFailures@odata.navigationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/cr532_bhdata_BulkDeleteFailures",
 "cr532_bhdata_PrincipalObjectAttributeAccesses@odata.associationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/cr532_bhdata_PrincipalObjectAttributeAccesses/$ref",
 "cr532_bhdata_PrincipalObjectAttributeAccesses@odata.navigationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/cr532_bhdata_PrincipalObjectAttributeAccesses",
 "cr532_bhdata_DuplicateMatchingRecord@odata.associationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/cr532_bhdata_DuplicateMatchingRecord/$ref",
 "cr532_bhdata_DuplicateMatchingRecord@odata.navigationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/cr532_bhdata_DuplicateMatchingRecord",
 "cr532_bhdata_DuplicateBaseRecord@odata.associationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/cr532_bhdata_DuplicateBaseRecord/$ref",
 "cr532_bhdata_DuplicateBaseRecord@odata.navigationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/cr532_bhdata_DuplicateBaseRecord",
 "cr532_bhdata_connections1@odata.associationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/cr532_bhdata_connections1/$ref",
 "cr532_bhdata_connections1@odata.navigationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/cr532_bhdata_connections1",
 "cr532_bhdata_connections2@odata.associationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/cr532_bhdata_connections2/$ref",
 "cr532_bhdata_connections2@odata.navigationLink": "https://X.crm.dynamics.com/api/data/v9.1/cr532_bhdatas(7f0687a8-b973-ed11-81aa-000d3a5cc79b)/cr532_bhdata_connections2"
 }
}

Was this reply helpful? Yes No

grantjenkins 11,059 Moderator on at

Like (1)

Report

Re: Troubleshooting Unexpected Flow Behavior

Can you click on Next failed in your Apply to each 2 and see if you can isolate the actual error?

Also, not sure why you're using Get tables here. I'd just manually add the Table name into my List rows present in a table and just have the single Apply to each (although the first loop only runs once anyway).

When you find one of the records that failed, I'd suggest looking at the Compose actions to see what the output of each is.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Re: Troubleshooting Unexpected Flow Behavior

That helped me find the error message, thanks!

OpenApiOperationParameterTypeConversionFailed. The 'inputs.parameters' of workflow operation 'Add_a_new_row' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'item/cr532_bhreadyforevaldatetimerequested' is required to be of type 'String/date-time'. The runtime value '"44897.0808796296"' to be converted doesn't have the expected format 'String/date-time'.

It seems the formatting of one of the date/time strings is to blame (formatted as a numeric value rather than standard date/time.) I'm a bit perplexed though, since all 4 of these columns are formatted the same way in the source document. I also have the 'Date Time Format' setting within 'List rows present in a table' set to ISO 8601.

Was this reply helpful? Yes No
grantjenkins 11,059 Moderator on at

Like (1)

Report

Re: Troubleshooting Unexpected Flow Behavior

That is a bit bizarre - I would have through all or none in that instance. You can convert the numeric date to a valid date/time format. If you don't know how to do this, then let me know.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
Re: Troubleshooting Unexpected Flow Behavior

I have to say, this has been an extremely difficult nut to crack! This morning's results are similar.

The source file prior to the script running looked like this:

...and after the script ran:

The flow ran successfully (I removed the unnecessary 'Get tables' step too, thanks for pointing that out!)

And yet, the end result in Dataverse is missing multiple items from the source:

The Outputs from 'List rows present in a table' contain all the data and everything is formatted correctly:
"value": [ { "@odata.etag": "", "ItemInternalId": "0e3822de-b3e2-4b81-8bdb-a53ebefa3862", "FIN": "564957309", "AwaitingBed": "2022-11-30T20:17:15.000Z", "ReadyForEval": "2022-11-30T19:09:00.000Z", "XAcceptance": "", "Departure": "2022-12-04T10:04:56.000Z" }, { "@odata.etag": "", "ItemInternalId": "3e16797a-18e0-4514-b930-517b9961b517", "FIN": "564962651", "AwaitingBed": "2022-12-03T11:19:44.000Z", "ReadyForEval": "2022-12-03T09:03:43.000Z", "XAcceptance": "", "Departure": "2022-12-04T09:49:41.000Z" }, { "@odata.etag": "", "ItemInternalId": "87ad7ee0-8e0e-4248-a53e-cc632f061a30", "FIN": "564962669", "AwaitingBed": "2022-12-03T14:41:48.000Z", "ReadyForEval": "2022-12-03T11:54:33.000Z", "XAcceptance": "", "Departure": "2022-12-04T20:24:57.000Z" }, { "@odata.etag": "", "ItemInternalId": "d8b7c503-5cf5-4b22-ab82-a67c41737800", "FIN": "564962910", "AwaitingBed": "2022-12-04T06:19:10.000Z", "ReadyForEval": "2022-12-04T05:58:27.000Z", "XAcceptance": "", "Departure": "2022-12-04T11:29:57.000Z"

And yet the inputs for 'Add a new row' look like this:
{ "host": { "connectionReferenceName": "shared_commondataserviceforapps", "operationId": "CreateRecord" }, "parameters": { "entityName": "cr532_bhdatas", "item/cr532_bhawaitingbeddatetimerequested": null, "item/cr532_bhreadyforevaldatetimerequested": "2022-11-30T19:09:00.000Z", "item/cr532_Xacceptance": null, "item/cr532_departdatetime": null, "item/cr532_FIN": "564957309" } }

Upon closer review of the 'Compose' steps, I see that the ones for 'FIN' and 'ReadyForEval' show the intended output, as I would expect. Oddly enough, though, the remaining 'Compose' actions (which aren't properly depositing their respective columns into Dataverse) all have this when I look at their inputs and outputs:

When I click the download link, I get nothing.

All of the non-functioning 'Compose' steps are written the same way as the ones that are working correctly:
items('Apply_to_each_2')?['FIN']
items('Apply_to_each_2')?['ReadyForEval']

items('Apply_to_each_2')?['AwaitBed']
items('Apply_to_each_2')?['XAcceptance']
items('Apply_to_each_2')?['Depart']

I'm hoping these details might point more experienced eyes towards the solution. I'm stumped!

Was this reply helpful? Yes No
grantjenkins 11,059 Moderator on at

Like (0)

Report
Re: Troubleshooting Unexpected Flow Behavior

Try the following as the ones you currently have don't match the field names.

items('Apply_to_each_2')?['AwaitingBed'] items('Apply_to_each_2')?['Departure'] //The one below just doesn't have any data so should be empty in that case items('Apply_to_each_2')?['XAcceptance']

Was this reply helpful? Yes No