Extract field and concatenate

442
3
Jump to solution
04-13-2022 09:43 AM
AliciaShyu
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.

 

0 Kudos
1 Solution

Accepted Solutions
Katherine_Clark
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

View solution in original post

0 Kudos
3 Replies
Katherine_Clark
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
0 Kudos
AliciaShyu
Occasional Contributor

Thanks Katherine! 

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

Katherine_Clark
MVP Regular Contributor

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

Glad it worked! 🙂

Best,
Katherine
0 Kudos