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!

workorder_form.xlsx

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/1157642

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?)