Good afternoon everyone, I'm a new to using this forum so apologies if I make any unintentional mistakes.
So I'm going to start off explaining my problem and hopefully someone here will be able to aid me, or at least point me in the right direction. Since I feel like I'm just missing a few lines of code.
I am attempting to make a kind of invoice sheet on Microsoft Excel that I will then us the XLSform Online page to transform into an ODK. Within the invoice I'm attempting to have a grid that houses within it a drop-down multiple choice question where they then chose one of the three options (In this case a style of available vehicle). Upon selecting their vehicle, the cost of the vehicle is then multiplied by how many of the vehicles the user is using. EX: If they select a service truck and only use one, then the cost is $44. If they select two then the price is $88.
I then have this question grid copied twice more since we need at least three options in case they need to use all three types of vehicles at the same time. However within that is where my current problem appears. After the question grid I need a kind of final total that calculates and shows all of the costs for far. Without writing 200+ IF/THEN statements to cover all of the potential outcomes these options could give, I can't think of a way to have this final total only add up the results of what has been given and ignore any potential yet unused options. I'll supply the file of the Excel sheet and code I'm using so far. This problem is with the information within group3
Thank you very much for any assistance you could give me with this problem.
within the repeat group, for each selected style:
-- ask for the number of vehicles - ${num}
-- calculate the partial cost for that vehicle style - ${cost}
then after the repeat, sum up all the partial costs using sum(${cost}) to get total
Hopefully that'll give you enough hints to point you in right directions...
Okay @Xiphware First off, thank you again for being willing to help me. I have been following the steps you have given me and once completed they seem like they should work. However there are a few things I'm still being hung up on.
What did you mean by "each style option value could even be the cost?" And if so how would I assign a calculate-able value to the text of the select-multiple?
With this repeat group style how would the user know which "Number of vehicles" is refereeing to which option they selected in the Select multiple? I could create multiple versions that only come up with the relevant column but then I would be in the same problem I was in before of having three different decimals which would then need multiple calculates which in turn wouldn't work properly with the sum(${cost})
I'll include the file and a screenshot of what it looks like so far. Thank you.
If you only have 3 styles, and they all have a different cost, you could do a shortcut and use the cost as the choices' value (!). eg name = 40 label = "truck". Otherwise, you'll need an if(...) to lookup the cost associated with each, but that's only 3 if's.
See choice-name() to map the currently selected choice value back to its corresponding label.
@Xiphware I feel like I'm right there! But I'm still tripping at the finish line. I have done what you said and changed the three choices item's names into their costs. Then I added the choice-name(), cost and total calculations. Everything seems to work fine, if only one option of the select_multiple styles is chosen. But the moment multiple are selected the ${cost} calculation errors out. Am I missing a way to stop the ${cost} from breaking when it is repeated or something similar? Maybe I'm pulling the unites to calculate in it from the wrong place. I'm just not sure. I would attach multiple screenshots of how it looks so far and a copy of the document it'self; but unfortunately it seems as though newer members can only send one picture in a reply. So I'll just send the document and a picture of the survey page. Once again, thank you so much for helping me with this project.
I think in line 31 you need to be using a selected-at() to pull in the appropriate selected style for that iteration. ${styles} will pull in the entire space separated list (eg "40 30 20"), whereas selected-at(${styles}, position(..)) will pull in just the value for that iteration. See https://docs.opendatakit.org/form-operators-functions/#path-operators for how to use position(..) inside a repeat group to get the current index; its commonly used when iterating thru a multiselect with a repeat group.
Unfortunately, unless I'm missing something that didn't seem to work. Both just the selected-at(${styles} * ${num} and the selected-at(${styles}, position(..)) * ${num}. Though for some reason when I tested the later it would implement the multiplication of a different choice. So for example selecting truck 0 and then truck 2 would show the total for truck 1 under both.
OKAY! It worked! Thank you so much for helping me with this whole thing. Our difference in time-zones didn't make it the easiest for communication but still thank you so much for staying with me.
I'll mark your last response as the solution but also add screen shots of the completed form to the bottom of this in case someone else in the future has a similar question.
hi @Xiphware and @Samitaire , hoping you can also help me with a similar problem (i.e., instead of one repeat, it is a nested repeat case). let's say, there are multiple invoices involved. and from this example, i also need to come up with the totals per vehicle aside from the overall/final total.
):
It worked! Thank you so much for helping me with this whole thing. Our difference in time-zones didn't make it the easiest for communication but still thank you so much for staying with me.