InfoPath Dev

Hello

I have a huge table from which I need to calculate a fields value depending on the user selection/input. Basically it is a 6 column table. Here is a general look of the table

HP              1200 RPM        1800 RPM        3600 RPM                    NEw Rebate                Old Rebate

1                    82.5                   85.5               77.0                              30                                 60

1.5                  86.5                  86.5               84.0                              55                               110

2                  87.5                    86.5               86.5                              65                               120

Now if the user selects that it is a new motor (From Option Buttons), and the Motor speed is 1200RPM (From Drop Down Box), the HP of motor is 1-5 (from Text Box) and the efficiency (comes from text box and is the value under the speeds like 1200, 1800, 3600) is 86.5% then the customer gets a rebate of $55

Now my question is how can I do it simply without making ton of rules as there are about 30 rows in the table and thus 30 rows X 3 Speeds X 2 Motor Types = 180 rules overall

Please help, this is a very important form and there is another table of similar format in this form for which I have same question

Do you have any ability to modify the structure of the table with the data displayed above? Because if the table was normalized, you could use a default value to get what you want.

And so on. Then, you could use a default value to set your field to NewRebate where HP = the value in the main data source, RPM = the value in the main data source and Efficiency = the value in the main data source. You may want to use rules instead of a default (but you'd only need 3 - on your main data source fields) if you don't want your field in your form to change if your secondary data source values change at a later date.

Actually the tableis not presented to the filler of the form. The table is just for the reference. The person filling the form selects the options for Speed (RPM, Drop Down), New or Old Motor(Option Button), and types in the Efficiency and HP in Text Box. The form needs to calculate the actual amount of rebate automatically for the given values of all other fields.

So any form of the table should work. Just that we need to check these 4 fields to make sure they get the right rebate.

Also if the efficiency in the table dows not match for the given HP and Speed of the motor, then the rebate should automatically be 0. For example if speed was 1200 RPM, HP is 1 but the efficiency if lets say 75.0 for the new/old motor then they should get a zero rebate.

Actually, I was assuming the table was a secondary data source most likely not exposed to the user. However, I didn't want to assume that you had control over the design of the table - for all I know it is some SQL table from some legacy app that you can't change. If you can change it, however, and normalize it in the way I indicated with my sample table, you should be able to use either a default or a rule for this.

Hilary

Actually the form I am making has been used in Paper format all these years long and this is the first time I am creating this. It is not connected to any kind of database, it is basically a application form for rebate in which the customer specifies what kind of motor they installed and we as a utility company provide them with rebates in thier bills according to the company and state laws. Also this is not the final task in the whole rebate process. This is just the first step.

The customer fills the form with most accurate description. We get the form and see what kind of rebate the person has applied for, then a customer rep goes to the customer location and checks out all the detail for accuracy. If all the details are right, then they approve the form and the rebate is then applied to their bill or checks ar sent. After the customer rep, there are a couple more things done in the whole process before the rebate is applied though.

So I want to know how to automate the calculation in this case, as lot of times the customers enter the right info, but they end up putting wrong rebates when tey do it themselve. I think the normalization thing you are talking about may be the right answer. But I have very little idea about it. Could you please elaborate on how to do this.

Thanks for all the time and effort.

I'll get you a sample form later today with an XML data source for the data you've shown that should give you an idea of what I'm talking about - you could use a SQL table or a SharePoint list, but if I use XML you'll be able to preview the form.

I've attached a simple sample form. First, right click the link in the heading of this post, and save the file locally to your computer. Then, right click on your locally saved copy, and select Design to open the form in design mode. From there, you can preview and try it.

I made an assumption that I'd get a rebate if my efficiency was equal to or greater than that in the table. You can change that. The Rebate field in the main data source has a default value. Clicking fx opens the formula editor. Highlight the field path in the formula editor and click Insert field or group to open the field picker. From there, click Filter Data and you can see where I set up the filter to select the correct NewRebate.

Hope this helps!

This does help me, but it is probably not the whole answer. Filtering the data is fine and I can make a list in SP to do that and pull data from, but how do I filter it according to if the motor is old or new because the user will select this option from two choice buttons and depending on the option they chose they get a different rebate.

 Also in extension to the whole situation, I have a different form with the same kind of work. The only difference is that the user first selects if the motor for that form is Explosion proof or not. If it is, then the user gets rebates on a different criteria, but if it is not explosion proof then they get the same kind of rebates as in the original form. How do I route my orm to decide which table to select from.

 I am sorry for being such a pain, but I have been very sick for last 5 days and I am working from home to get this done. This is very important and I started working on InfoPath just like a month back. Once again Sorry and Thanks for all the help.

Sorry - I overlooked that new / old was something your user would enter. The source data needs to be further normalized. I should be able to get you an updated sample tomorrow. As to your "explosion proof" question, you may want to add that data to the same table, maybe another column that is true / false for explosion proof.

Here's another sample. Hopefully you'll be able to extend it from here.