Calculation based on select-one results

BillKrukowski — Mon, 25 Nov 2019 22:24:13 GMT

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.

Re: Calculation based on select-one results

JamesTedrick — Tue, 26 Nov 2019 18:58:40 GMT

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.

topic Re: Calculation based on select-one results in ArcGIS Survey123 Questions

Calculation based on select-one results

Re: Calculation based on select-one results