Extract field and concatenate

[AliciaShyu]

Survery123 Connect 3.13

I have a text field (RollCallNbr, row17) in the following format xx-xxxx. The first 2 characters represent the year. I need to extract those 2 characters, convert them into 4 characters for year (RollCallYear, row 18), and use the new year field as part of a concatenated URL (RollCallURL, row 19).

For example:

A roll call number can be 22-0501, the roll call year would be 2022 and the roll call URL would be https://councildocs.dsm.city/rollcalls/2022/22-0501.pdf 

I'm attaching the XLS form.

[Katie_Clark · Accepted solution]

Sounds like you should be able to use the substr() function to extract the first two digits. Here's how I would go about it.

1. Add a new row with the substr() function in the calculate column to populate this field with the first two digits of the roll call number. 
   1. substr(${RollCallNbr}, 0, 2)
2. Create the four digit year by adding concat("20", ${twoDigitNum}) to the calculate column of the ${RollCallYear} row
3. Create the URL by using concat("https://councildocs.dsm.city/rollcalls/", ${RollCallYear}, "/", ${RollCallNbr}, ".pdf")

Hope that works for you!

[AliciaShyu]

Thanks Katherine! 

That worked, I only had to make a minor change in step 1, substr(${RollCallNbr},0,2)

[Katie_Clark]

Ah yes, I couldn't remember if the indexes started at 0 or 1 with Survey123. I updated my original response.

Glad it worked! 🙂