topic Re: autopopulating date in Survey123 subtracts 1 day in ArcGIS Survey123 Questions

autopopulating date in Survey123 subtracts 1 day

JamesKelly4 — Sun, 12 Dec 2021 09:37:55 GMT

I am using the pulldata() function in the calculation field to autopopulate several survey questions based on the answer to a question regarding a confirmation number. It works great except for the date field I need populated. It will populate a date, but it is a day earlier than the value provided in the .csv file (confrimations.csv).

Here is the formula I used in the calculation field:

pulldata('confirmations','harv_date','conf', ${conf_no})‍‍

Here is an example of the confirmations.csv with only 1 row of data.

id,conf,county,harv_date,sex
10168797,14400001,Davidson,09/26/2018,male‍‍‍‍

When I enter the above confirmation number, this is how it populates the date.

Any ideas? Survey123 for ArcGIS‌ James Tedrick‌ Ismael Chivite‌ #pulldata()‌

Re: autopopulating date in Survey123 subtracts 1 day

JamesTedrick — Mon, 19 Aug 2019 16:26:00 GMT

Hi James,

Are you in the western hemisphere? If so, a likely reason you are seeing this behavior is that the date string, with no other information, is being read as a date/time combination in UTC time. If you change the date to a datetime, you should see the time register as your UTC offset in hours before midnight (as a date is being interpreted as occurring at midnight UTC).

One way to compensate for this is to add subtract the UTC offset from the number as interpreted as a datetime - see the attached sample.

Re: autopopulating date in Survey123 subtracts 1 day

JamesKelly4 — Tue, 20 Aug 2019 20:43:03 GMT

That worked! Thanks again James Tedrick‌!

Re: autopopulating date in Survey123 subtracts 1 day

DougBrowning — Wed, 01 Jul 2020 15:58:32 GMT

I am having this issue but when passing from Collector to 123.

I have a date of 8/20/2019 in Collector but when it passes to 123 it turns into 12/31/1969.

Then I removed the time and the date works but is giving 1 day before now. So 8/19/2010.

Then I followed this post and made the form datetime. But now it is back to 1969 if a have time on in the web map and Aug 19 if I leave the time off still.

Any ideas?

thanks

Re: autopopulating date in Survey123 subtracts 1 day

JamesKelly4 — Mon, 17 Aug 2020 16:33:50 GMT

James Tedrick‌ I am updating this form (the one mentioned in the original question) for this year's field season, and I was wondering if there is a way to default the harvest date to blank instead of Wednesday, December 31, 1969. When there is a match via pulldata function, it is not an issue. However, when there isn't a match and staff have to enter data by hand, the auto-populated 12/31/1969 often gets over-looked. How might I correct this? Thanks in advance!

Re: autopopulating date in Survey123 subtracts 1 day

JamesKelly4 — Wed, 19 Aug 2020 13:34:27 GMT

Doug Browning‌ might you have any insight on my question above to James Tedrick‌? Any help is much appreciated!

Re: autopopulating date in Survey123 subtracts 1 day

DougBrowning — Wed, 19 Aug 2020 14:17:56 GMT

No I never did get it to work and never heard from anyone as to why. I had to just give up and take it out of my project.

From my research it may have something to do with the timezone of the service when you publish it. I could not republish since it was already being used so I did not test that. Also this dataset is nationwide so I was unsure which timezone to pick.

For you one idea is to pass everything as text to a text field then calculate that text into a date field behind the scenes if you need it to be date type.

Hope that helps.

Re: autopopulating date in Survey123 subtracts 1 day

JamesTedrick — Fri, 21 Aug 2020 00:17:07 GMT

Hi James,

I would need to see the details of your form - are you using a an if() statement with the pulldata to determine if a value is blank?

The obvious method to catch this would be to place a constraint in the question to check if the value is realistic.

Re: autopopulating date in Survey123 subtracts 1 day

JamesKelly4 — Mon, 24 Aug 2020 15:16:04 GMT

Hi James Tedrick thanks for getting back to me on this. I did the other day think to add a constraint so this shouldn't be an issue as far as the value passing. Is there a way to make the default value be something different than 12/31/1969? No worries if not. This one is low priority for me at this point. Thanks again!

Re: autopopulating date in Survey123 subtracts 1 day

JamesKelly4 — Mon, 24 Aug 2020 15:29:42 GMT

James Tedrick‌ please disregard above question. Brain fart. Obviously I could set default date today() or something like that. What I meant was, is there a way to make it default to blank? Sorry for confusion. Thanks again!

Re: autopopulating date in Survey123 subtracts 1 day

JamesTedrick — Tue, 25 Aug 2020 17:27:44 GMT

Hi James,

Yes, you should be able to use '' as the 'blank':

if(${q1}='yes',today(),'')

Re: autopopulating date in Survey123 subtracts 1 day

JamesKelly4 — Wed, 26 Aug 2020 12:06:37 GMT

Ah I did not know about that hack. Thanks so much!

Re: autopopulating date in Survey123 subtracts 1 day

erica_poisson — Tue, 12 Apr 2022 13:16:45 GMT

Hi @JamesTedrick -

I know this post is years old, but hoping you see this. I am having the exact date issue as @JamesKelly4 in my Survey123 form - a DOB pulled in from a CSV file is 1 day off (always the day before). I used your XLSForm above as an example but this did not work - the incorrect date is still being pulled in. Do you have any suggestions? I am really struggling with this one.

After adding all of these new fields/calculations into my XLSForm, this is what happens when I test. The date should be November 10, 1960.

Thank you,

Re: autopopulating date in Survey123 subtracts 1 day

KristySnyd — Thu, 28 Jul 2022 22:55:28 GMT

Hi @erica_poisson, did you ever find a solution to this problem? I am running into the same issue. The date on the csv is always one day before but it is correct on the tablet.

Thank you!

Re: autopopulating date in Survey123 subtracts 1 day

erica_poisson — Fri, 29 Jul 2022 15:55:53 GMT

Hi @KristySnyd - unfortunately no. I stepped away from this and have not had a chance to go back to it yet.

Sorry! 😞