# Extract field and concatenate

432
3
04-13-2022 09:43 AM
Occasional Contributor

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.

Tags (2)
1 Solution

Accepted Solutions
MVP Regular Contributor

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!

Best,
Katherine
3 Replies
MVP Regular Contributor

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!

Best,
Katherine
Occasional Contributor

Thanks Katherine!

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

MVP Regular Contributor

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