create a unique calculation for each record

[daveinc]

Hi,

I would like to be able to calculate the sizes of the parts of an assembly based on the width,height and depth of the finished product. There are 43 different assemblies with differing part lists for each. For example, each assembly has a back but the size of the back is calculated differently based on which assembly is being built. One assembly may call for a back that is (width - 3) by (height - 1) and another may specify 2 pieces (width - 2) by (height - 1)/2. I would like to save each formula with a record describing the assembly type and part name so that the part size will be calculated when the finished width, height and depth are entered for the assembly. Is there any way that you know of to save a unique calculation with each record of a database? I know I could make a horrendously long Case statement for each dimension of each part but as you might guess I would like to avoid that to make future changes to part size formulas infinitely easier. There are anywhere from 15 to 45 parts in an assembly. It seems this may be an instance where Filemaker comes up short I dare say. Thanks in advance for any ideas.

[Raybaudi]

Hi

what about a table of parts ?

[comment]

You can do this very easily in version 7 and higher, using the Evaluate() function.

It MAY not be too difficult in earlier versions, too - if all calculations follow a certain pattern. To take your 2 examples, they are both built on a pattern of:

k * ( width + w ) * ( height + h )

In the first example, (width - 3) by (height - 1)

k = 1

w = -3

h = -1

In the second example, (width - 2) by (height - 1)/2:

k = 0.5

w = -2

h = -1

So with 3 fields to hold the constants k, w, and h, both situations can be handled by a single calculation field.

[daveinc]

There is currently a parts table for common sizes. The trouble is, the assemblies can be ordered in any size above 9" cubed. This includes fractions down to 1/16". This situation makes for about 45% of orders being "special" where a person has to calculate sizes the old fashioned way.

[daveinc]

Yes, I am looking for a way to implement the evaluate function in FM5&6. I may have to set up a standalone FM7 station to do the calcs, then send the resulting parts back to the FM5&6 solution. Thanks for the idea comment!

[comment]

I still think you can do this in version 5/6 quite easily. All you need to do is standardize the calculations to a common form. I would have to see all your 45 formulae to be sure, but I have good reasons to believe they can all be transformed to something simple like:

a*width + b*height + c

where a, b and c are constants that are specific to the formula. You would have 45 records for the formulae, each storing the constants only. Once you select the formula to use, the constants are looked up, and the correct calculation is performed.

To return to one of your examples, (height - 1)/2 can be written as:

0.5*height - 0.5

So you would just plug in [a = 0 ; b = 0.5 ; c = -0.5] and that's it.

[daveinc]

I've kicked your ideas around while looking at the whole library of assemblies, and I think what I need to minimize the labor involved is to either upgrade to fm7,8,8.5 or find a plugin that will perform the evaluate function in fm5,6. There are so many different sets of parts with differing methods of determining their size that I could spend a week just typing them as they are into fields(we have the formulas listed with each assembly) and that's about how long I have to get it done and reliable. Do you know of any plugins that might fit the bill? Thanks for all your thoughts.

[Raybaudi]

Hi

the lonely I know for FM5 is here:

http://www.kamar.co.nz/plugins/

[comment]

I don't know what to say. I think it takes less than an hour to type 45 formulas. Add a couple of hours for testing and proofing. I don't know how long it would take to convert them all to the form I suggested, because I haven't seen them. The ones I did see took about 15 seconds each - because I am slow.

But hey, this is as good reason to upgrade as any...

[daveinc]

There are many other factors in the assemblies. The number of parts is dependent on any one of the three dimensions. If the height of assembly is more than 3 but less than 5, qty1 part x, and qty3 part y. Height more than 5 but less than 9, qty2 part x and qty 7 part y. There are many such variables to each of the 45 assemblies(not always similar in nature and for many different part names). The people who would have to modify these formulas are not FM users. If I were the one maintaining this database forever, I might go the route you suggest. Your idea for the part sizes would be fine(and I began to think my problem was solved) until I delved deeper into the assembly parameters. I could personally have this done in no time(the part sizes are already done in 5,6) but I am looking ahead toward ease of use for others. There is an established formula building method already being used to calculate part sizes and quantities that is virtually identical to that of FM with a few minor differences. It will be an easy transition for my client if I can use typed formulas as that is what they are used to now. If these were relatively static parameters, that would make it easier as well. These are custom assemblies with some default parameters, so each time I think I have a universal calculation to handle some variable, I find another assembly that throws a wrench in the plan. They can also have a $10.00 an hour employee enter the formulas with confidence rather than myself if the formula builder is familiar to them.

[comment]

It's OK, I am not here to convince you either way - only to point out some options.

Speaking of that, you said something interesting:

There is an established formula building method

I am wondering if this method could perhaps be turned into an algorithm by itself - so that neither you nor a $10.00 an hour employee would have to enter anything?