Best way to handle customizable formulas

[CrashDome]

I am developing an application for estimating manufacturing costs and the end-users would like to define there own formulas and processes at any time.

This means they want to create a master list of formulas set to calculate specific fields from specific tables in the database and insert the result into a cost field. This formula is called upon by the estimator when they "add" the process to an estimate, but the master list must be editable by them from within the application should they desire to change a field or calculation.
The idea behind their logic is to allow the expansion of their plant (new machines, new products, processes, etc..) to be less cumbersome to program into their estimating software.
Anyone had experience with this? What is the best way to handle this?

Any advice/help appreciated.

 

[benlinkknilneb]

I think I'm understanding you correctly; correct me if I'm wrong. You want to set up a pre-defined formula for something (ex. Area = Side * Side). You want the user to have the capability to redefine your formula (perhaps Area = Pi * R * R), or add new formulae (like Circumference = 2 * Pi * R).

Is that the basic idea?

If it is, try searching the web for code that will parse a string into an arithmetic expression; we had a project my junior year of college in which we had to design a small spreadsheet with formula capability, and we found several parsers on the web. Then store the strings in a file (or a small database). Then when the user changes it, you can just edit the file and add the formula to the list.

It will take a little extra work if you want to do a formula like this:

CylindricalVolume = Area * Height

The guy in our group that handled the code for that ended up writing a "pre-parser" function that determined if there was anything within the string that wasn't in simplest form, worked it out, and then replaced "Area" with the result.

I know it's not much, but I hope it can get you started. If you have any more questions, ask away and I'll help you as best I can.

Ben

PS: I'm not sure what parsers are available in VB; our project was in C++. Try these sites:

http://www.programmersheaven.com

and

http://www.pscode.com

Both of them have plenty of VB code.

 

[CrashDome]

You are absolutely correct, that is what I am looking for. Thanks for the tip, I will look. Personally it can be in VB or C# (I am not picky).

I also want to see if anyone else has had this task. I am curious to see what others are currently using and how well it works.

 

[Custom24]

Hi
I've never had to do this, but I find it an interesting area.
You may decide to go with the hand-rolled parser idea, and there is an excellent example in Francesco Balena's book "Programming Visual Basic.net", using regular expressions (chapter 12)

But why bother? See a previous thread thread796-447477 where I suggested using the Windows script control. Read to the end of the thread - the final post might work for you.

If you want to stick with managed code rather than a COM component, and this is admirable, then check out this

http://dotnet247.com/247reference/a.aspx?u=http://www.codeproject.com/csharp/runtime_eval.asp

It uses system.reflection to load an assembly into memory on the fly. The only thing I am not sure about is the security implications. .net is quite granular about this, so I should expect that any assembly being loaded on the fly can be set so that it has minimal security priviledges.

Anyway, enjoy!


Mark