Select to view content in your preferred language

updating Date Field not working

2422
10
Jump to solution
12-22-2022 12:51 PM
DominicRoberge2
Frequent Contributor

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

 

DominicRoberge2_0-1671741995548.png

 

DominicRoberge2_1-1671742084166.png

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!

0 Kudos
1 Solution

Accepted Solutions
DominicRoberge2
Frequent Contributor

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  😁
 
 

View solution in original post

10 Replies
DominicRoberge2
Frequent Contributor

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
Frequent Contributor

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

Liam
0 Kudos
erica_poisson
Frequent Contributor

@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? 

erica_poisson_0-1702327422508.png

erica_poisson_1-1702327662522.png

Thank you,

 

 

 

Erica
0 Kudos
DominicRoberge2
Frequent Contributor

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

0 Kudos
erica_poisson
Frequent Contributor

@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!
 
Erica
0 Kudos
DominicRoberge2
Frequent Contributor

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

0 Kudos
erica_poisson
Frequent Contributor

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

 

Erica
0 Kudos
DominicRoberge2
Frequent Contributor

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

DominicRoberge2_6-1702492794406.png

 

below is another example I have:

DominicRoberge2_1-1702492569267.png

DominicRoberge2_2-1702492579217.png

DominicRoberge2_3-1702492593624.png

DominicRoberge2_4-1702492611016.png

 

DominicRoberge2_5-1702492628352.png

hope this help (and not confuse you more  😁)

 

0 Kudos
erica_poisson
Frequent Contributor

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