Managing Time and Date from Survey123 in Microsoft Flow

[KhairulAmri2]

Webhook capability in Survey123 is very helpful to automate process without make a code. you can follow this link for the details : https://community.esri.com/groups/survey123/blog/2018/07/19/automating-workflows-with-survey123-and-... 

I got some challenges when use it in Microsoft Flow especially about time and date. 

1. originally time and date from survey123 is EPOCH format.
2. by default time zone form survey123 is UTC.
3. I have needs to display time and date in proper string format.

Convert EPOCH to YYYY/MM/DD HH:mm:ss

if you access time and date from field directly from dynamic content, it will display epoch time. to show time and date as YYYY/MM/DD HH:mm:sss you can use expression instead of dynamic content.

 

expression syntax :

addseconds('1970-1-1', Div(triggerBody()?['feature']?['attributes']?['dateTime'],1000) , 'HH:mm:ss')

this part ['feature']?['attributes']?['dateTime'] is time field in your featureservice.

it will return time and date like this : 2019-01-22 02:00:00 (please bear in mind this is UTC time zone)

Convert from UTC to My City Time Zone :

as you can see on the picture above, I added my time expression into add to time plugin. this plugin is default from Microsoft Flow to add interval time into your exisiting time. my city time zone is UTC +7. so I added 7 in interval form and select hour in time unit options.

the result will be like this : 2019-01-16T13:00:00.0000000

its return time and date with full string format. 

Reformat time and date string:

in my case, I want to extract time from converted time and display it as short time pattern.I added Convert time zone plugin provided by microsoft flow. basically this plugin used for converting time zone, however there is an options to display format time and date format string. so I used "short time pattern" in format string options then convert time zone to same time zone due to my time has been converted in previous step by adding interval manually. 

then I can use time in email body like this :

or use converted time in event calendar like this :

here is the whole diagram :

Any inputs are welcome

Regards

Amri

[Anonymous User]

Nice work Amri, looks great. Thanks for sharing.

[JamesLong]

It would seem this section is the critical syntax portion that I am lacking.  

"

expression syntax :

addseconds('1970-1-1', Div(triggerBody()?['feature']?['attributes']?['dateTime'],1000) , 'HH:mm:ss')

this part ['feature']?['attributes']?['dateTime'] is time field in your featureservice."

Here are my questions, I hope you can help me.  

I do get the epoch time integers that will pass through Microsoft low to an email.    I would like to format these as Date time.  

>is the '1970-1-1' just a place holder is is that something specific needed to be entered? 

>is the ['feature'] the feature service URL from Survey123 or the text name of the feature service, or is it the feature service at all. 

>I'm having trouble differentiating ['attributes'] and ['dateTime'] (Which I'm assuming is your field name). How does 'attribute' differ from 'field'?  

If you could be more specific about what get's entered, and the syntax, I would be very appreciative.  

Thank you

[JamesTedrick]

Hi James,

In practice, it's a bit easier to set up than the written out text suggests

'1970-1-1' is the timestamp equivalent of 0 - Unix timestamps originate at January 1st, 1970 00:00 UTC.  By using it with AddSeconds, you are essentially saying 'Add the timestamp in seconds to 0 to generate a new date-time object'

triggerBody()?['feature']?['attributes']?['dateTime'] is the formal version of selecting the question named 'dateTime' from the survey response.  triggerBody() refers to the incoming webhook submission, and in that you are retrieving feature.attributes.dateTime (if you think of the response as JSON).  You generally will input this not by writing it out but by selecting the appropriate value from the webhook values list.

[JamesLong]

OK, so this is nice but it doesn’t really answer my question. Input what? Input where?

I found your Geonet post https://community.esri.com/thread/232692-date-from-survey123-in-microsoft-flow

So some clarifying questions: You posted

addSeconds('1970-01-01', div(, 1000))

Is this the correct Syntax? addSeconds('1970-01-01', div(incident_date, 1000))

Or is it addSeconds('1970-01-01', div(feature incident_date, 1000))

Or addSeconds('1970-01-01',div(triggerBody()?['feature']?['attributes']?['incident_date'],1000))

JIM LONG

Senior GIS Analyst

DIRECT 520-887-1010 Ext. 2917

jlong@northwestfire.org

NORTHWEST FIRE DISTRICT

5225 W. Massingale Rd. ​Tucson, AZ 85743

www.northwestfire.org

ISO CLASS 2 - INTERNATIONALLY ACCREDITED

/NorthwestFireDistrict

@NorthwestFire

Confidentiality Notice: This e-mail is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Any review, dissemination, copying, printing or other use of this e-mail by persons or entities other than the addressee is prohibited. If you have received this e-mail in error please delete the material immediately.

[JamesLong]

I’m going to try the third option because flow didn’t give me an error

{"source":"la-draft-clipboard","value":[{"tokenKey":"2C9FC210-9471-4AA5-9F6B-486CE6C021B5","type":"literal","tokenExpression":"addSeconds('1970-01-01',div(triggerBody()?['feature']?['attributes']?['incident_date'],1000))"}]}

JIM LONG

Senior GIS Analyst

DIRECT 520-887-1010 Ext. 2917

jlong@northwestfire.org

NORTHWEST FIRE DISTRICT

5225 W. Massingale Rd. ​Tucson, AZ 85743

www.northwestfire.org

ISO CLASS 2 - INTERNATIONALLY ACCREDITED

/NorthwestFireDistrict

@NorthwestFire

Confidentiality Notice: This e-mail is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Any review, dissemination, copying, printing or other use of this e-mail by persons or entities other than the addressee is prohibited. If you have received this e-mail in error please delete the material immediately.

[JamesLong]

Just FYI, the last option worked. Thanks for your help.

JIM LONG

Senior GIS Analyst

DIRECT 520-887-1010 Ext. 2917

jlong@northwestfire.org

NORTHWEST FIRE DISTRICT

5225 W. Massingale Rd. ​Tucson, AZ 85743

www.northwestfire.org

ISO CLASS 2 - INTERNATIONALLY ACCREDITED

/NorthwestFireDistrict

@NorthwestFire

Confidentiality Notice: This e-mail is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Any review, dissemination, copying, printing or other use of this e-mail by persons or entities other than the addressee is prohibited. If you have received this e-mail in error please delete the material immediately.

[JeffEvans1]

I had a similar problem, but with multiple date fields and found this expression worked best without having to Add Time or Convert Time Zone within Power Automate:

convertTimeZone(addSeconds('1970-01-01',div(triggerBody()?['feature']?['attributes']?['dateTimeSubmited'],1000)),'UTC','Pacific Standard Time','g')

[LindseyStone]

In your syntax is the 'dateTimeSubmitted' a field that you created in your survey123 or is that being pulled from somewhere else?  I would like to send the the "CreationDate" field that is automatically created and put this date in the email, however that doesn't show as a field to add under the dynamic content.  The problem is when I put a date field in the survey123 the survey taker can change the date and the we don't want that so I was trying to base the submission date of the creation date.

[MichelleDuncan2]

I have a similar question about the EditDate field. When an edited survey is submitted a new webhook is generated. Our users want to see that Edit Date field in the subject line of the webhook so they know it's an edited survey and not a new submission. If I use the same syntax as the rest of my date/time conversions but just substitute the Edit Date field I get an error. Is there some other way to do this?

addseconds('1970-1-1', Div(triggerBody()?['feature']?['attributes']?['EditDate'],1000) , 'yyyy-MM-dd HH:mm:ss')

[JeffLegato1]

I was having issues with null values in my date fields using Survey123 with webhooks and Microsoft Power Automate. I am using an if statement in combination with the formatting like so...

if(equals(triggerOutputs()?['body/feature/attributes/InspDate'],null), 'No inspection date recorded', convertTimeZone(addSeconds('1970-01-01',div(triggerOutputs()?['body/feature/attributes/InspDate'],1000)),'UTC','Pacific Standard Time','D'))

[Anonymous User]

Hi @JeffLegato1,

What version of the field app are you using? We are aware of an issue with "null" values with the latest 3.14 Feb/March update. We are working on a fix and it will be available in the April update. This fix should be available to test with the beta builds on the Early Adopter Community later today. If you can test and let us know if that fixes the issue, that would be great.

Regards,

Phil.

[JeffLegato1]

@Anonymous User I am using Survey123 version 3.14.242 loaded from a direct link from a feature popup in Field maps. 

My issue is resolved using the if statement in my earlier post. 

[VanessaSimps]

@JeffLegato1 Quick question for you- and thank you for posting your if statement! This is super helpful. I was able to mimic what you used in my work.

1. Were you still using the Convert Time Zone step in your workflow as shown in this screenshot, or were you doing something else?
2. what is the 'D' used for in the last part of your if statement? 

I was able to get what you shared to work, except that the date that is returning to me now is a just under a day (about 22hrs) behind what is in the survey? wondering if I missed something in the steps?? 

thanks in advance!

Vanessa

[JeffLegato1]

@VanessaSimps The 'D' is for the Long Date Pattern see: https://learn.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-strings#R... 

I am not using the Convert Time Zone step. The if statement is contained in the email body to include specific text if the date is null or the date if a date was recorded in the form. 

[VanessaSimps]

I think I figured it out. Thank you again for posting your solution. I don't think I need the null part right now, but it is really useful to keep in the back pocket!
Best,
Vanessa

[Sietse_de_Haan]

Thanks for this topic.

I am also trying to implement this within my flow, except I have 2 feld swith date and time which needs to be converted. How do I do this? I have tried to use different expresions but they all fail. Next to this I tried 2 conversions for both fields but that the flow also fails. 

I always receive this error:

InvalidTemplate. Unable to process template language expressions in action 'Een_rij_toevoegen_aan_een_tabel' inputs at line '0' and column '0': 'The template language function 'div' expects its first parameter to be an integer or a decimal number. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#mul for usage details.'.

I need conversions for "Meldingsdatum" and for "** Implementatie" (not shown in the screenshot, its further below in de excel). They both need to be in UTC +1 timezone. What am I doing wrong here? And how can I fix this?

[VanessaSimps]

@Sietse_de_Haan 

Where is the date converter step? I don't see it in the screen shot you shared? I think you need to do the conversion right after you pull in the survey, then add your next steps. 

[Sietse_de_Haan]

@VanessaSimps 

Okay, so I was just typing an answer and while typing I tested the flow again. Now it does work with these expressions for the fields that needed to be calculated..

addSeconds('1970-1-1',Div(triggerOutputs()?['body/feature/attributes/Meldingsdatum'],1000),'yyyy-MM-dd') 

So for Meldingsdatum and the other one they both work and the flow does not fail. So strange 😄

Since we are writing days and do not include the time I dont mind the conversion since its only 1 hour difference (UTC+1 is our timezone). 

If I do want the time included, in the correct timezone, what do I need to add? Cause if I create a timezone conversion between the trigger and the excel and email, I have to choose a field for the conversion. Will this affect the other date/time field as well for the calculation? Or only for example the "Meldingsdatum"? 

Thanks in advance