topic Re: Survey123 Connect concatenate date and pulldata result in ArcGIS Survey123 Questions

Survey123 Connect concatenate date and pulldata result

Elizabeth_Burniston — Thu, 15 Aug 2024 18:45:03 GMT

This is probably much easier than I am making it. I need to concatenate the year with a number generated from a pulldata calculation. It needs to read like this 24-xyzx. I know this is not the right syntax but essentially like this; %y-${formno}. I believe this needs to be in the constraint field... where I have the question marks. I receive the correct result in ${formno}, I just need to join this to the year. Let me know if you need more information! Thank you!

Re: Survey123 Connect concatenate date and pulldata result

ZachBodenner — Thu, 15 Aug 2024 18:54:14 GMT

It looks like your pulldata is working, yes? If that's the case, it must be in the calculation column - is your concatenate in the calculation column as well?

Re: Survey123 Connect concatenate date and pulldata result

Elizabeth_Burniston — Thu, 15 Aug 2024 19:16:59 GMT

Yes... my pulldata is working and yes... it is in the calculation column. Is concatenate actually what I need to give me the result I want? I just assumed it was, but I was putting it in the constraint column.

Re: Survey123 Connect concatenate date and pulldata result

ZachBodenner — Thu, 15 Aug 2024 19:22:40 GMT

Constraints will essentially make it so that any entry outside of the specified format wont be allowed. I think it should work if you put it in the calculation column and format it something like this:

concat(%y, '-', ${formno})

Re: Survey123 Connect concatenate date and pulldata result

Elizabeth_Burniston — Thu, 15 Aug 2024 19:40:54 GMT

I am getting an error... couldn't understand the expression. I am concatenating a date with an integer into a text field. Does that matter?

Re: Survey123 Connect concatenate date and pulldata result

DuncanC — Thu, 15 Aug 2024 20:05:44 GMT

Yeah, you can't just throw a date format string into a concat like that. Make a hidden datatime field if you don't already have a date field to grab the year from, set it's default to now(). Then a hidden field that uses format-date as the calculation so it's value will be 24. Then concat that with your pulldata value.

https://doc.arcgis.com/en/survey123/desktop/create-surveys/xlsformformulas.htm#ESRI_SECTION2_AF58C0297155474F993CEA115682CED3

It should be possible to do it all in one line, but the XLSX parser in Survey123 connect is kind of broken, when I try that it never works, so I just use lots of hidden lines and do one thing per line.

Re: Survey123 Connect concatenate date and pulldata result

ZachBodenner — Thu, 15 Aug 2024 20:06:07 GMT

Mm, that's a good catch. I routinely overestimate what 123 Connect can do in those kinds of calculations.

Re: Survey123 Connect concatenate date and pulldata result

Elizabeth_Burniston — Thu, 15 Aug 2024 20:14:22 GMT

That worked! Thank you!

Re: Survey123 Connect concatenate date and pulldata result

Elizabeth_Burniston — Thu, 15 Aug 2024 20:15:20 GMT

Thank you for getting me on the right track though! I appreciate your super quick responses! 🙂

Re: Survey123 Connect concatenate date and pulldata result

Elizabeth_Burniston — Thu, 22 Aug 2024 16:30:38 GMT

@DuncanC - Hi again! I just noticed that in Connect the solution works exactly as it should, but when I look at it in the webform, it gives me "Invalid Date". I could have SWORN it worked before. By chance, is this a Connect version problem? Since we worked on this, I upgraded to the most recent version of Connect.

Re: Survey123 Connect concatenate date and pulldata result

DuncanC — Thu, 22 Aug 2024 16:54:02 GMT

Looks like the datetime now() isn't working in that context. Sorry, but I have no idea why. If it works in connect I think it's likely a ESRI bug and not a issue with your code. I haven't ran into a similar issue but I don't really use ESRI's web forms. To debug I'd turn your hidden fields visible to confirm what is going on. If you can't find a way to default a datetime on the the webform you may have to file a bug report with ESRI. Personally I'd just give up on trying to make ESRI stuff work and roll my own web page to handle the work flow. Shouldn't be easier, but to build something I find acceptable, it ends up being easier.

Re: Survey123 Connect concatenate date and pulldata result

Elizabeth_Burniston — Thu, 22 Aug 2024 18:25:18 GMT

@DuncanC UPDATE: I found the problem and turning off the hidden fields worked in finding it. Using now() assumes I have a dateTime field. I don't. Only date. So, I changed now() to today() and it fixed the problem. Thanks!

Thanks for your response! I'll try turning off the hidden fields to see if I discover anything. It feels like to make "Esri stuff work" it is always one step forward, two steps back. Ugh! Thank you again!