updating Date Field not working

[DominicRoberge2]

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!

[DominicRoberge2 · Accepted solution]

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  😁

[LiamHarrington-Missin]

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

[erica_poisson]

@DominicRoberge2  @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,

[DominicRoberge2]

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

[erica_poisson]

@DominicRoberge2 @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!

[DominicRoberge2]

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...

[erica_poisson]

@DominicRoberge2 @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)

[DominicRoberge2]

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  😁)

[erica_poisson]

@DominicRoberge2  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!