Survey123 Connect concatenate date and pulldata result

[Elizabeth_Burniston]

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!

[ZachBodenner]

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?

[Elizabeth_Burniston]

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.  

[ZachBodenner]

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

[Elizabeth_Burniston]

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?

[DuncanC · Accepted solution]

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

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.

[ZachBodenner]

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

[Elizabeth_Burniston]

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

[Elizabeth_Burniston]

That worked! Thank you!

[Elizabeth_Burniston]

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