topic Re: updating Date Field not working in ArcGIS Connectors for Power Automate Questions

updating Date Field not working

DominicRobergeIADOT — Thu, 22 Dec 2022 20:51:45 GMT

Hello

I have a flow in Power Automate that pull data from a Dataverse table, and update a hosted feature layer on AGOL. Everything seems to be working fine EXCEPT for my Date fields.

below is what is the payload form Dataverse

{

"@odata.editLink": "cr3c3_coa_projectses(a85d55b7-1f82-ed11-81ac-000d3a37d89e)",
"cr3c3_coaprjstartdate@OData.Community.Display.V1.FormattedValue": "12/22/2022 6:00 AM",
"cr3c3_coaprjstartdate@odata.type": "#DateTimeOffset",
"cr3c3_coaprjstartdate": "2022-12-22T06:00:00Z",
"_cr3c3_coaemployee_value@OData.Community.Display.V1.FormattedValue": "Dominic Roberge",
"_cr3c3_coaemployee_value@Microsoft.Dynamics.CRM.associatednavigationproperty": "cr3c3_coaEmployee",
"_cr3c3_coaemployee_value@Microsoft.Dynamics.CRM.lookuplogicalname": "cr3c3_coa_stafflist",
"_cr3c3_coaemployee_value@odata.type": "#Guid",
"_cr3c3_coaemployee_value": "b302e0c1-2082-ed11-81ac-000d3a37dbf1",
"cr3c3_coa_projectsid@odata.type": "#Guid",
"cr3c3_coa_projectsid": "a85d55b7-1f82-ed11-81ac-000d3a37d89e",
"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": "Dominic Roberge",
"_createdby_value@Microsoft.Dynamics.CRM.lookuplogicalname": "systemuser",
"_createdby_value@odata.type": "#Guid",
"_createdby_value": "14855649-5267-ed11-9561-000d3a37d89e",
"cr3c3_coaprjname": "test revised no staff",
"timezoneruleversionnumber@OData.Community.Display.V1.FormattedValue": "4",
"timezoneruleversionnumber": 4,
"createdon@OData.Community.Display.V1.FormattedValue": "12/22/2022 5:40 PM",
"createdon@odata.type": "#DateTimeOffset",
"createdon": "2022-12-22T17:40:26Z",
"cr3c3_prjuid": "7",
}

the 4th line has the data I am looking for to update my date field in AGOL. below is the error I am getting

I have tried adding aFormatDateExpression (FormatDateTime(outputs('Get_a_row_by_ID')?['body/cr3c3_coaprjstartdate'],'MM-dd-yyyy')) but still no success.

Any help would be much appreciated.

Thanks!

Re: updating Date Field not working

DominicRobergeIADOT — Tue, 31 Jan 2023 15:23:25 GMT

I solved the problem. I had to convert the INPUT date value to EPOCH using the following expression

div(sub(ticks(outputs('Get_a_row_by_ID')?['body/cr3c3_coaprjenddate']),ticks('1970-01-01Z00:00:00')),10000)

This ArcGIS Connector is a game changer 😁

Re: updating Date Field not working

LiamHarrington-Missin — Tue, 21 Mar 2023 15:06:01 GMT

Thank you @DominicRobergeIADOT, It's taken me about 3 hours to pin down this little bit of code to fix my problem.

Re: updating Date Field not working

SeanKMcGinnis — Mon, 27 Mar 2023 17:20:51 GMT

For others that might be encountering this issue, here is a Quick Tip article on converting the date to something that can be human readable.

Re: updating Date Field not working

erica_poisson — Mon, 11 Dec 2023 20:48:11 GMT

@DominicRobergeIADOT @SeanKMcGinnis -

This is, I think, exactly what I need, however I am having a hard time applying it. Would either of you be able to help?

The initial trigger of this flow is a Survey123 submission. When certain criteria are met, then I get the matching parent record from a feature layer and convert the inspection date from the Survey123 form. Syntax for this conversion is working well: addSeconds('1970-1-1', Div(triggerBody()?['feature']?['attributes']?['INSPEC_DATE'], 1000), 'yyyy/MM/ddTHH:mm:ss')

I then use Compose to addDays to my converted dateTime. I am getting the interval from my feature layer. Again, this is workign well: addDays(body('Convert_Inspection_Date'),int(items('Apply_to_each')?['Attributes/PHASE_I_INTERVAL']),'yyyy/MM/ddTHH:mm:ss')

I was initially trying to write the outputs from Compose directly to my date attribute field in the bottom "update a record in a feature layer", however I am getting an error when I try this. I stumbled across this post and I think the conversion back to Epoch time is exactly what I need, but can't get it to implement. Here is what I am trying: div(sub(ticks(outputs('Compose')?['body/Compose']),ticks('1970-01-01Z00:00:00')),10000)

This results in this error: InvalidTemplate. Unable to process template language expressions in action 'Update_a_record_in_a_feature_layer' inputs at line '0' and column '0': 'The template language expression 'div(sub(ticks(outputs('Compose')?['body/Compose']),ticks('1970-01-01Z00:00:00')),10000)' cannot be evaluated because property 'body/Compose' cannot be selected. Property selection is not supported on values of type 'String'. Please see https://aka.ms/logicexpressions for usage details.'.

Any advice?

Thank you,

Re: updating Date Field not working

DominicRobergeIADOT — Tue, 12 Dec 2023 20:04:42 GMT

Hi Erica, can you show us what you are getting as output from the Apply Each and Compose when you run the flow?

Re: updating Date Field not working

erica_poisson — Tue, 12 Dec 2023 20:38:42 GMT

@DominicRobergeIADOT @SeanKMcGinnis -

Sure, here are the outputs. First is that output from ArcGIS Connector "Get Phase 1 Inspection Interval from Feature Layer".

{ "statusCode": 200, "headers": { "offset": 1, "hasMoreData": "no", "Timing-Allow-Origin": "*", "x-ms-apihub-cached-response": "false", "x-ms-apihub-obo": "false", "Date": "Mon, 11 Dec 2023 20:38:00 GMT", "ETag": "W/\"342-jBGiEo2buRqe7BgtiCHbyLsmGbM\"", "X-Powered-By": "Express", "Content-Length": "834", "Content-Type": "application/json; charset=utf-8" }, "body": { "data": [ { "attributes": { "OBJECTID": 45, "NAT_ID": "MA99999", "DAM_NAME": "Erica's Dam", "DAM_LOCATION": "Hubbardston", "DAM_TYPE": "Concrete", "DAM_OTH": null, "YEAR_BUILT": 2500, "WATERSHED": "Quabbin", "OWNER": null, "CARETAKER": null, "JURISDICTIONAL": "Y", "PHASE_I_INTERVAL": 730, "LAST_PHASE_I": 1639242000000, "SCHED_PHASE_I_INSPEC": 1702314000000, "SIZE_CLASS": "Small", "HAZARD_CLASS": "Class I", "HEIGHT_FT": null, "LENGTH_FT": null, "CAPACITY_AcFt": null, "CREST_ELEV_FT": null, "SPILLWAY_ELEV_FT": null, "SPILLWAY_WIDTH_FT": null, "ELEV_REFERENCE": null, "OUTLET_TYPE": null, "ROUTINE_INSPEC_INTERVAL": null, "LAST_ROUTINE_INSPEC": null, "ADD_COMMENTS": "TEST ONLY ERICA WILL DELETE WHEN DONE", "DAM_FILES": null, "GlobalID": "24b4e810-f399-4146-88cd-919c82b2a459", "CreationDate": 1702320093451, "Creator": "DWSP_erica.tefft", "EditDate": 1702320597270, "Editor": "DWSP_erica.tefft" } } ] } }

Next, here is the output from "Compose":

"2025/12/10T18:47:00"

Please let me know if that helps/is what you were looking for.

Thank you for your help!

Re: updating Date Field not working

DominicRobergeIADOT — Tue, 12 Dec 2023 20:51:52 GMT

Could you try to add another Compose and put the expression to convert to epoch to see if that works. Then you would put the result of the new Compose into the 'Update_a_record_in_a_feature_layer'. Just to make sure you are getting the correct epoch value. Just an idea...

Re: updating Date Field not working

erica_poisson — Wed, 13 Dec 2023 17:49:55 GMT

@DominicRobergeIADOT @SeanKMcGinnis

Thanks for the suggestion. When I try either of these in a new Compose, I get "Invalid statement" and can't apply the expression:

div(sub(ticks(outputs('Get_Phase_1_Inspection_Interval_from_Feature_Layer')?['body/data'],ticks('1970-01-01Z00:00:00')),10000)

div(sub(ticks(outputs('Compose'),ticks('1970-01-01Z00:00:00')),10000)

Re: updating Date Field not working

DominicRobergeIADOT — Wed, 13 Dec 2023 18:44:47 GMT

I think you might be missing something in your first expression (you are not referring the attribute section). In your case it's probably: Body/data/attributes/FIELDNAME

below is a sample of a breakdown

below is another example I have:

hope this help (and not confuse you more 😁)

Re: updating Date Field not working

erica_poisson — Wed, 13 Dec 2023 20:47:36 GMT

@DominicRobergeIADOT that was so helpful! Thank you for those detailed images and also your patience with me! Here was what I ended up doing:

div(sub(ticks(addDays(body('Convert_Inspection_Date'),int(items('Apply_to_each')?['Attributes/PHASE_I_INTERVAL']),'yyyy/MM/ddTHH:mm:ss')),ticks('1970-01-01Z00:00:00')),10000)

Instead of adding a second Compose, I just updated my original Compose which was using the "addDays" function and nested that inside your EPOCH conversion. The I used outputs from Compose within my ArcGIS "Update a record in a feature layer". It worked perfectly when I tested!

Thank you so much!