Calculation based on select-one results

11-25-2019 02:24 PM
New Contributor

I would like to use Survey123 to track activity at a recycling center. I have set up a form with a few groups to separate the type of recycling (Electronics, Metals, Bulky Items, and Construction Debris). Each of the groups contain a repeat section to add multiple items. I would like to auto calculate the total fee based on the items selected and am having a rough go at it. I want to avoid using the pull-data function from a csv file to look up the disposal fee. At first I used the fee amount as the name but that created problems because multiple items have the same fee and thus the same name so my select-one question would have multiple items selected because the names were the same (it did not throw an error). So I guess my real question is:

Is there a way to use a select-one field result (Item Drop Down) to match a second choices list and then pull the label from that second list and report it back to the Survey (Invoice Amount)? I would like to note that multiple class of items can be disposed of at a time so the Invoice Amount would need to look into every repeat and all table entries of that repeat for the specific survey instance. 

Can I create my own custom column in the choices tab of the excel sheet and somehow just reference a custom column to do calculations?

My alternative is to have the workers manually enter a price for each item and then run a calculation to populate Invoice Amount, I did not want to create too many field in the survey.

Tags (1)
0 Kudos
1 Reply
Esri Esteemed Contributor

Hi Bill,

In general, I think it would be more efficient/sustainable to do this with a pulldata() function using a csv lookup.  That being said, you may be able to do this if you place the value at a consistent place in the label (either the beginning or the end).

I would place the price at the end of the label, with a consistent amount of characters (for example, if the highest price was $50.00, I would write something that was $5.00 as $ 5.00 - with a space or 0 between the dollar sign and the number).  What you can then do is get the choice's label using the jr:choice-name() function; that can go in a subset() function to extract the last characters (for example, 5 - 50.00 is 5 characters) of the label; finally, use an number() function to change that from text to a number.  The final formula looks like:

number(substr(jr:choice-name(${item_drop_down}, '${item_drop_down}'), -5))

That can then be used in calculations.  I've attached a sample order form displaying this.

0 Kudos