General

Re: Text & Number Calculation in DropDown getting NaN error

luke20 — Wed, 14 Nov 2012 16:09:34 GMT

I've just tried a few ways of doing this and found one that works without the NaN error.. I set up a couple of drop down lists all with n/a, 0, 1 & 2 - i put the following rule on the last drop down list that the user will select from:

condition: Rule runs when field changes

Rule type: action

set a fields value: (the text box that you want to have the answer in) to dropdownlist1 + dropdownlist2 + dropdownlist3

hope this helps

Re: Text & Number Calculation in DropDown getting NaN error

spjeff — Tue, 13 Nov 2012 03:46:19 GMT

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.

Re: Text & Number Calculation in DropDown getting NaN error

Joey_86 — Mon, 14 Nov 2011 01:13:59 GMT

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.

Re: Text & Number Calculation in DropDown getting NaN error

Jimmy — Fri, 11 Nov 2011 12:09:28 GMT

This should work:

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

Re: Text & Number Calculation in DropDown getting NaN error

Joey_86 — Fri, 11 Nov 2011 06:34:43 GMT

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']))

Re: Text & Number Calculation in DropDown getting NaN error

Scott Rooke — Fri, 11 Nov 2011 02:22:05 GMT

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

Re: Text & Number Calculation in DropDown getting NaN error

Joey_86 — Thu, 10 Nov 2011 23:44:18 GMT

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

Re: Text & Number Calculation in DropDown getting NaN error

Jimmy — Thu, 10 Nov 2011 09:29:49 GMT

You should be able to accomplish this with:

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

Re: Text & Number Calculation in DropDown getting NaN error

Joey_86 — Thu, 10 Nov 2011 06:50:20 GMT

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

Re: Text & Number Calculation in DropDown getting NaN error

Jimmy — Thu, 10 Nov 2011 06:39:47 GMT

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.

Re: Text & Number Calculation in DropDown getting NaN error

Scott Rooke — Thu, 10 Nov 2011 06:17:00 GMT

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

Re: Text & Number Calculation in DropDown getting NaN error

Joey_86 — Thu, 10 Nov 2011 05:32:06 GMT

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.

Re: Text & Number Calculation in DropDown getting NaN error

Scott Rooke — Thu, 10 Nov 2011 04:03:56 GMT

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

Re: Text & Number Calculation in DropDown getting NaN error

Joey_86 — Thu, 10 Nov 2011 03:35:16 GMT

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

Re: Text & Number Calculation in DropDown getting NaN error

Scott Rooke — Wed, 09 Nov 2011 11:55:08 GMT

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