Autofill Contact Info From CSV

759
4
Jump to solution
10-04-2021 02:43 PM
JaredPilbeam2
MVP Regular Contributor

It looks like what I want to achieve can be done, maybe. I have a CSV with company names and addresses. I'm looking for a way to pre-populate the section of my survey where it asks for contact info. The end users will generally be the same companies year after year. I'm designing the survey in Connect and will ultimately publish it as a Web App (v. 3.13).

Here's an example of what I mean:

(1) The user starts typing the name of the company the begins with "a". A list of those companies show up.

con1.png

(2) When the user selects a company all contact boxes are filled.

con2.png

According to this help doc, you won't be able to fill multiple boxes at one time:

The pulldata() function is best kept alone in the calculation column. Do not be tempted to combine multiple pulldata() calls within the same calculation. It will work in Survey123 Connect, but the Survey123 web app will not be as permissive.

I've also been referencing this help doc, but I don't quite understand it.

pulldata('info','email','name', ${previous_question})

  1. The name of the CSV file that contains the list of values. The name does not include the .csv file name suffix.
  2. The name of the column in the CSV file that contains the value you want to return.
  3. The name of the key field in the CSV file that you will use to look up the value.
  4. The key value to look up in the key field.

Looking at the parameters here, I understand what the first two are. What exactly are numbers 3 and 4? I've attached my XLSF and a shortened version of the CSV file (contacts_short.csv) that has the contact info.

 

0 Kudos
1 Solution

Accepted Solutions
JaredPilbeam2
MVP Regular Contributor

Using this video I was able to get what I wanted. https://www.youtube.com/watch?v=CTq-ux2v5ds&list=PLGZUzt4E4O2L7h2PdpL7st93nURZAW58d

One thing to note, that I haven't seen in any of the help docs, but was mentioned briefly in the video (but not shown) is that you need to separate columns in your CSV by commas. It may be intuitive to someone who uses Survey123 all the time, but I wasn't familiar with it. And the survey wouldn't work without doing this.

In the Data tab of your CSV --> From Text/CSV --> navigate to your CSV in your media folder and select it --> Transform Data --> Close and load. Note: you will have a new header column, labeled as Column1, Column2, etc... I had to use these new column headers in the pulldata() function.

View solution in original post

0 Kudos
4 Replies
ZacharySutherby
Esri Regular Contributor

Hello @JaredPilbeam2

Step 3 is going to be the column in your CSV that matches the value you will be typing in your survey. In your case it will be the Company column as that is the column you are searching for your value. Step 4 is where the pulldata function is going to obtain that lookup value. In your case its going to be ${company} as you want to pull the company that was entered in the Survey123 survey and using dollar sign curly bracket notation is how you reference other Survey123 questions. 

Please use this link for more information on the pulldata() function. 

As a side note if you would like a drop down of choices based on what people type in, instead of using a text question I would suggest making all the companies a choice list and use a select_one question type with the autocomplete appearance to search for a choice and then let the user select their choice. 

Thank you,
Zach
JaredPilbeam2
MVP Regular Contributor

@ZacharySutherby,

Thanks for the reply. I read fields are not supposed to refer to themselves, so I'm a little confused as to where to put the calculation. Right now I have everything in the same row (highlighted in yellow), but I get an error in Connect.
form1.png

er1.png

 

0 Kudos
JaredPilbeam2
MVP Regular Contributor

EDIT:

I have the company set to autocomplete in the appearance field and no calculation, and that seems to be working okay. But, I'm still wondering how to use pulldata() to autofill the rest of the contact fields based on the CSV.

I'd imagine each of the contact fields would be based off of the company field and would need a calculation something like this: pulldata("contacts_short", " Address", "Address",${company})

 

0 Kudos
JaredPilbeam2
MVP Regular Contributor

Using this video I was able to get what I wanted. https://www.youtube.com/watch?v=CTq-ux2v5ds&list=PLGZUzt4E4O2L7h2PdpL7st93nURZAW58d

One thing to note, that I haven't seen in any of the help docs, but was mentioned briefly in the video (but not shown) is that you need to separate columns in your CSV by commas. It may be intuitive to someone who uses Survey123 all the time, but I wasn't familiar with it. And the survey wouldn't work without doing this.

In the Data tab of your CSV --> From Text/CSV --> navigate to your CSV in your media folder and select it --> Transform Data --> Close and load. Note: you will have a new header column, labeled as Column1, Column2, etc... I had to use these new column headers in the pulldata() function.

0 Kudos