Select to view content in your preferred language

Survey123 Report - the CreationTime field incorrectly adjusts the time in a Report.

676
2
Jump to solution
11-03-2023 11:02 AM
DarrenLloyd__DOIT
Regular Contributor

I am working on a Survey123 custom report that uses the default {CreationDate} field. When the CreationDate field is used in the report it automatically adjusts the time. For example, In Survey123 the submitted time is 13:09 but when the {CreationDate} field is used in a report, the time is adjusted to 5:09 PM. Basically it changes it to a 12-hour format and adds 4 hours. Do I need to adjust the time in Survey123 Connect prior to the report? or can I use an expression in the report? 

0 Kudos
1 Solution

Accepted Solutions
DarrenLloyd__DOIT
Regular Contributor

I don't want to take credit for this solution so here is the link to the original solution.

https://community.esri.com/t5/arcgis-survey123-videos/survey123-create-report-action-in-microsoft-po...

Here is my solution based on the US Eastern time zone.

1) In Survey123 Connect XLS add a text utcOffset field. Then insert the following into calculation field ---> if(pulldata("@property", 'utcOffset')='-4','-04',if(pulldata("@property", 'utcOffset')='-5','-05',pulldata("@property", 'utcOffset'))). Use "hidden" In the Appearance column.
2) In MS Power Automate under the Create Report step, place the newly created utcOffset feature attribute in the UTC offset field.

DarrenLloyd__DOIT_0-1699546486171.png

 

View solution in original post

0 Kudos
2 Replies
abureaux
MVP Frequent Contributor

This is likely converting to UTC. Feature Reports tend to do that to date fields that include time (but NOT to time only fields, as they are technically just "text").

I have a field in all my forms to pull the UTC offset:

abureaux_0-1699037441636.png

Then use that in the Feature Report:
${if asb_utc==”-3.5”}${asb_sample_start_time | utcOffset:”-0330”}${/}${if asb_utc==”-4”}${asb_sample_start_time | utcOffset:”-0400”}${/}${if asb_utc==”-5”}${asb_sample_start_time | utcOffset:”-0500”}${/}${if asb_utc==”-6”}${asb_sample_start_time | utcOffset:”-0600”}${/}${if asb_utc==”-7”}${asb_sample_start_time | utcOffset:”-0700”}${/}${if asb_utc==”-8”}${asb_sample_start_time | utcOffset:”-0800”}${/}

Essentially, you need to account for the time zones that you may cross (for me, North America). Basically, it looks like this:
${if asb_utc==”-3.5”}${asb_sample_start_time | utcOffset:”-0330”}${/}

asb_utc = A calculate in your survey, similar to what I show above.
-3.5 = A UTC offset, specific to you (-3.5 is Newfound Land)
asb_sample_start_time = A Date or DateTime question. For you, likely Createdate
-0330 = The correction to apply to your Date or DateTime question.

0 Kudos
DarrenLloyd__DOIT
Regular Contributor

I don't want to take credit for this solution so here is the link to the original solution.

https://community.esri.com/t5/arcgis-survey123-videos/survey123-create-report-action-in-microsoft-po...

Here is my solution based on the US Eastern time zone.

1) In Survey123 Connect XLS add a text utcOffset field. Then insert the following into calculation field ---> if(pulldata("@property", 'utcOffset')='-4','-04',if(pulldata("@property", 'utcOffset')='-5','-05',pulldata("@property", 'utcOffset'))). Use "hidden" In the Appearance column.
2) In MS Power Automate under the Create Report step, place the newly created utcOffset feature attribute in the UTC offset field.

DarrenLloyd__DOIT_0-1699546486171.png

 

0 Kudos