Conditional calculation based on another field?

[Katherine_Rudzki]

Hello, I'm working on a form to calculate total cost based on two fields - both a work order activity and quantity. There are about 50 line items on the work order, and each has a different cost associated with it. If I select A from the dropdown and type in a quantity of 2, it will reach into the code to pull the cost for A and multiply that by the quantity. 

I've started the code process but I'm not sure if I'm going in the right direction:

If client selects '1. Emergency Response' and inputs a quantity of 1, multiply 1 by 750. If client selects '2....' etc. Else calculate 0/null.

if(${work_order_activity_codename_1},'1. EMERGENCY RESPONSE - EA'),int(${line_item_quantity_1}*750), if(${work_order_activity_codename_1},...etc,0))

I've attached the form spreadsheet to show what I'm talking about. Is this possible?

Thanks in advance!

[ChipMorgan65]

Katherine,

   I'm on day two of Survey 123 Connect, so take my recommendations with a grain of salt.

   First, I'd define a separate variable for the cost of each activity on a series of separate "calculate" lines. That'll make administration of those costs much easier to deal with.

   Second, I think there's a better way to structure the list of activities as well. I would suggest that you utilize the same list for each line item instead of repeating the activity list. This could simplify your cost computations as well.

   About the cost calculations - I'm hoping there's a better way than a long series of "if" statements as that will be quite difficult to troubleshoot. But I don't have an answer at this point. I'm just too green on this system. 

   I replied to give you as much as I could, knowing that I've got my own ESRI questions posted on other forums that no one has replied to me about.

Good luck,

   Chip 

[TylerGraham2]

Take a look at how to use CSV files with survey123. There's a few different ways to access data which I think will work better for what you have in mind.  Using a CSV file will let you store your work order activity choices separate from the form, include additional data, and you can update list (like if the price of an item goes up, or a new activity needs added) without the need to republish the form if the CSV is setup as linked content.  

https://community.esri.com/t5/arcgis-survey123-blog/work-with-csv-data-in-arcgis-survey123/ba-p/1157...

You'd want a CSV file (we'll call it WO_List.csv for this example) with at least 3 column headers. Something like "WOname", "WOlabel", "itemcost" to start with.  Populate the list with your work activity code, description, and whatever the price of items are. Save the csv and drop it in your survey's media folder. I use 1 csv for each choice list.  

I would use search appearance() to populate the choice list and eliminate the need to manage an extra choice list.  To do that, in the choices tab of the spreadsheet you would replace the entire list of choices  in list_work_order_activity_1_work_order_activity_codename_1 with:

list_name	name	label
list_work_order_activity_1_work_order_activity_codename_1	WOname	WOlabel

That will tell the list to populate the list with data under those headers in the CSV.  Then go to the survey tab of your form spreadsheet and under the appearance column enter autocomplete search('WO_List') which tells the survey to search the csv file without using any filter criteria.  

Then you will want to create a total cost field for the work activity, setting the type to decimal. in the calculation column you would want your calculation to be something like:

${line_item_quantity_1} * pulldata('WO_List',  'itemcost', 'WOname', ${work_order_activity_1}) 

This will multiply the line item quantity by the itemcost in the WO_List.csv that is associated with the WOname value that matches what was selected in the work_order_activity1 question.  

One thing you should think about is if those Work Order activity groups are all the same, that making the first one a repeat rather than a group and deleting the other three groups. It'll save you some headaches with running out of fields (what happens if 5 things need fixed) and will let you have the responses as required (if only 3 things need fixed and there are groups for 4 things, what does the person do to populate the last group so they can submit the form?)