InfoPath Dev

Hi, just wondering if anyone has any idea's for the following issue.

I have 5 drop-down fields each with the following: n/a, 0, 1 & 2. I then have a text box calculating these drop-downs and because the text box is trying to calculate numbers, the 'n/a' is just throwing out the calculation, does anyone have an idea how I would be able to make the calculation on the 5 fields? So even if someone enters a 'n/a' it still continues to calculate and shows the total value. Also to keep in mind that this is a web based form and I'd like to try and calculate this without the need for coding.

Thanks in advance.

Have you considered creating a hidden field with a data type of Whole Number or Decimal. Then set a rule on each dropdown that if NOT "N/A" set the value of a field. Your new number field --> numberField = number(.) + numberField

Essentially insert a function of number(field name) and add it to the existing numberField value? The period in the example is a reference to the field itself. So you would add this rule to each drop down. Would that get you what you are looking for?

Let me know.

Scott

Hi Scott,

Thanks so much for your prompt reply and yes it did work to provide me what I needed although now it has another problem, the score in the new numberField won't refresh, it simply keeps adding onto the previous calculation if the dropdowns are changed more than once. Also on the new numberField, I've set it as a Decimal data type to get my answer as a percentage but the 'Refresh value when formula is recalculated' is grey'd out.

 Thanks in advance :)

Ok. Let's see.... this is going to get a little tricky so I'll to keep it simple.

You might need to create a second hidden field for each of the dropdowns to store the value when it is first set. So your rules will get more complicated but this is how I would do it.

If the second field is blank then set that field = the dropdown value.
If it is not blank then subtract that value from the numberField, add the new dropdown value to the nubmerField, and then overwrite the existing second hidden field for the dropdown with the new value so that it it changes again you'll be able to update the numberField accordingly.

Of course you'll need to add these rules to each of the dropdown fields.

Glad the first solution got you this far, let's see if this gets you your desired output.

Best!

Scott

Speaking of getting a little tricky, its managed to get me confused in the process. So now there are 3 fields:

1) The dropdown with options (n/a, 0, 1 & 2)

2) The numberField

3) hiddenField

Rules:

1) Dropdown not equal to "n/a" (set field value: numberField = number(.) + numberField)

2) hiddenField is blank (set field value: hiddenField = Dropdown)

3) hiddenField is not blank (set field value: hiddenField - numberField, set field value: numberField = Dropdown, set field value: hiddenField = Dropdown)

I hope I've set the rules correctly to how you've explained it but it didn't work, its also pulling across the 'n/a' selection that we didn't want in the calculation.

 

Bummer....

Any chance you can attach the form to this post? Save a copy and and delete all non related fields. I'll take a look at it.

Let me know

Scott

Can you show us the formula you're using to do this calculation?  There may be a simpler way without using a bunch of hidden fields.

The attached is basically what I'm trying to do, so basically even if a user selects a 'n/a' it still keeps calculating, so for example, if you have 3 questions and the user selects a score of 2 for 2 of the questions and a n/a for the 3rd the score total still equals 100% this will be the same if all 3 questions are n/a, etc.

 Thanks so much for your help Scott

Joey :)

You should be able to accomplish this with:

sum((field1 | field2 | field3)[. != 'n/a']) div (2 * count((field1 | field2 | field3)[. != 'n/a']))

Thanks Jimmy, that formula works great, also thanks Scott for your help in this issue too.

You are welcome Joey. And Jimmy, I ended up using a variation of what you sent today to solve a new need for a new form I was building today. Very timely!

Best!

Scott

Jimmy/Scott, just a final quick question that I've come across, using this attached form as an example. For instance I will have 2 critical questions with a score of either '0' or '2', would it be possible to use that formula in the total? So basically, the user would go through and select the values from the other dropdowns but if they select one or either of the criticals and set them to '0' then the total is an automatic '0' score, but if they don't then the total keeps calculating to get the total score of the dropdown fields.

Jimmy:

You should be able to accomplish this with:

sum((field1 | field2 | field3)[. != 'n/a']) div (2 * count((field1 | field2 | field3)[. != 'n/a']))

This should work:

(original formula) * not((criticalField1 | criticalField2)[. = 0])

Thanks Jimmy, it worked like a charm, although for some reason now my web form is lagging a bit when a user makes a dropdown selection.

I had a similar issue with NaN headaches. Essentially I wanted to show a warning message (section with red background color) when the percentage exceeds 100%. The solution for me was a simple translate() function I found at

http://braintrove.com/id/71

translate(../my:Dividend div ../my:Divisor, "aN", "0")

This effectively turns "NaN" into "0" so the conditional formatting rule can work correctly.