# Assign a numerical value to a text answer

1354
9
08-02-2018 01:30 PM
Occasional Contributor II

Hi,

I am wondering if there is a way to assign a numerical value to a text answer. Here is an example of what I am trying to do:

A survey is designed for inspectors to fill out. As they answer questions like,

"What is the distance to the nearest fire extinguisher?"

1: 10 ft.

2: 50 ft.

3: 100 ft.

I would like to assign a numerical value to those answers so 1 point for 10 ft., 5 points for 50 ft., and 10 points for 100 ft. At the end, I would like to be able to tally up the results of my survey with a total score for every answer to a question and be able to assign a risk value that the inspector can both see and submit in the form.

Is this possible?

THANKS!

Tags (2)
9 Replies
Esri Esteemed Contributor

Hi Robert,

Yes, you can use numeric values for the names of a list.  For use in a  formula, you will need to convert from string/text (which is how it will be stored) to integers using the int() function in the formula.

Occasional Contributor II

James,

Thanks for responding. I should have clarified in my original post that I had already read through the threads for the https://community.esri.com/groups/survey123/blog/2015/08/24/using-formulas-in-survey123 post, specifically the one from Thierry Gregorius which outlines the approach you described. My issue with this approach is that in the back-end I would like the question field to be populated with the actual text from the Name field and not a numeric value like 1, 2, 3, 4, 5.

Example:

Q1: What is your favorite animal?

Q2: What is your favorite color?

Q3: What is your favorite shape?

I would like to keep the name field as a text (on the left) and not have to use a numerical value (on the right) just to do the calculation at the end. Is there another approach I can do that would allow me to do this?

Thanks!

Esri Esteemed Contributor

Hi Robert,

You could use a series of if statements or a pulldata function to translate the text to an appropriate numerical value in your formula.

Occasional Contributor II

James,

I followed your advice and have been attempting to use the pulldata function to achieve my desired result however I am having an issue where the risk value isn't populating in my survey under my note question. I created an external xls form (moved it to my media folder) called "Risk" where I have the following:

Then in my main xls form I have the following pushdata function:

My choices tab has the following (matching the name column to the external name column):

Is there something I am missing?

Thanks!

Esri Esteemed Contributor

Hi Robert,

Is your external file an Excel file or CSV?  For pulldata() to function, it must be a CSV (text with comma separated values).

Occasional Contributor II

James,

You are good my friend! I had it saved as an excel file and not a CSV. Once I saved it as a CSV it worked like a charm. I will let you know if I get my survey to work how I want it to now that I have the pulldata function working!

Occasional Contributor II

James,

One more question to help me take this home. In order to add up all of my values from the various questions into one total number, do all my rows where I do a pulldata calculation need to be set to integer? Right now when I calculate all of the rows they just put them together (i.e. 5+1 is 51 not 6).

Thanks

Occasional Contributor II

James,

Never mind, I figured it out. Thanks again!

New Contributor

What was the solution to this last piece?  That is where I am currently stuck.  I have 2+2+2 = 222 instead of 6.