General

Re: Need help with assigning numbers based on percentages

JoeInFL — Mon, 18 Jan 2010 14:54:53 GMT

You're absolutely correct, I copied yours and it worked perfectly. I must have had the Rules logic wrong. The negative numbers were confusing me, possibly. As you can see, I'm not terribly "math-y" either!

Thanks a lot, you saved me much frustration!

Re: Need help with assigning numbers based on percentages

Hilary Stoupa — Sun, 17 Jan 2010 23:33:55 GMT

I don't believe you are running into an InfoPath limitation. I put together a quick sample based on my interpretation of what you are trying to do -- it seemed to work correctly to me (then again, I'm not terribly 'math-y', so I may be imagining that). Right click the link in the header of this post and use Save As to save the xsn locally. Then right click and select Design to open in design mode so you can preview.

Need help with assigning numbers based on percentages

JoeInFL — Fri, 15 Jan 2010 20:48:44 GMT

I have a form with fields named "Sales Plan" and "Sales Actual". The user enters the Sales Plan figures, as well as the actual figures for the past month. I use the following to get the percentage of the difference between the two. This can be a positive, or a negative number. I use the following to get the result in the "Value" field of another text box named "Plan_Calc":
((Sales_Plan - Sales_Actual) / Sales_Plan)

This seems to work fine, and the number returned seems to make sense.I have yet another text box that I need to display a score in, named "Plan_Score", depending on this percentage. I've tried setting up Rules, such as "If Plan_Calc is less than 0, Score = 1" and "If Plan_Calc is greater than 0, Score = 2". This works fine.

The problem is when I try to apply my actual formula, it doesn't seem to work. Here's what I'd like to happen:

If the result is greater than/equal +4%, put a 4 in the "Plan_Score" text box.

If the result is 1% to +4% over Plan, put a 3 in the "Plan_Score" text box.

If the result is -1% to Plan, put a 2 in the "Plan_Score" text box.

If the result is less than -1%, put a 1 in the "Plan_Score" text box.

Anytime I add a third Rule, it either just displays the same number (i.e.; 4) no matter what's entered, or it diplays nothing at all. I've tried every combination of "If greater than this number, but less than another number" I can think of. Does InfoPath limit Rules from doing this kind of calculation?

Any ideas, or am I going about this all wrong??