Multiplying fields together

[AJV]

I trying to write my first custom function that basically behaves similarly to the

Sum ( field {; field...} )

function; except rather than add the fields, they would get multiplied. The fields, in this case, would be in separate records.

Being new to this (but having read the help pages and many of the posts in this forum) I still can't figure out how such a function would get coded. My guess is that recursion would be used but it is not obvious how FM would know to do the math over the set of records.

Is there a tutorial on this somewhere? Or am I missing something completely obvious?

Thanks...Andrew.

[AJV]

I think I found the answer to my own question: the custom multiply function needs to be passed a value list (created by a a different calculation) that is parsed and operated on by the custom function.

If this is the simplest way to do it, it makes me wonder why FM does not allow a developer to write their own functions that operately similarly to aggegrate and stat functions.

Cheers...Andrew.

[Raybaudi]

Hi AJV

I haven't the 8 Advanced yet, but I think that that function will be easy to make by using the new GetNthRecord() function.

Give it a try !

[AJV]

Ray, great suggestion! Here it is (recursive)

Product ( fieldname; n_records) =

If ( n_records > 1; GetNthRecord ( fieldname ; n_records ) * Product (fieldname; n_records - 1); GetNthRecord ( fieldname ; n_records ) )

Thanks for the pointer.

Cheers...ajv

[Raybaudi]

I knew you are able to...

[Søren Dyhr]

Is it really worth a CF?? Aren't you forgetting your math??

Exp(Sum ( Ln ( field1 );Ln ( field2 );Ln ( field3 );Ln ( field4 )))

--sd

[Raybaudi]

Hi Soren

is your question directed to me ?

Because I know that formula (and if I didn't, that formula was mentioned by Comment some days before !)

The problem was that AJV wanted to make his first custom function... why not ?

[Søren Dyhr]

Well you, me ....anyone - I'm obviously spreading socratian noise here, to grasp the inner secrets of the tool at hand. Which isn't the same as brainstorming that requires of you to ignore a hierarchy, the socratian method is questioning authorities!

Well there might be a point in the CF, have I later discovered ...it won't need an index, aggregate functions would if handling a set of records ...or would they??

But if you use the carthesian product relation type, can they exist without indexed fields ...also a thing I've learned from Comment who have dwelved deeper in the Migration Foundation paper s than me!!!

--sd

[AJV]

As a newbie to the developer version of FileMaker, I'm more than open to learning different ways to accomplish the same result.

While it is true that I did forget my math, I will still need a custom function for my project that, rather than multiplying, concatenates strings together, which will ultimately produce a somewhat complex Excel formula. Unfortunately, "math" will not help with that problem, but the technique I learned from that "multiplication" exercise, will certainly come in useful.

Thanks again, everyone, for all the help...ajv

[Raybaudi]

Hi Soren

I don't know why I ever can't understand you !

You said:

"Well you, me ....anyone - I'm obviously spreading socratian noise here"

but that isn't so obvious to me, because upper you said:

"Is it really worth a CF?? Aren't you forgetting your math??"

and not "Aren't WE forgetting OUR math ?"

I think that this isn't a good approach to a discussion.

[SlimJim]

Aren't you forgetting your math??

Exp(Sum ( Ln ( field1 );Ln ( field2 );Ln ( field3 );Ln ( field4 )))

--sd

It depends on where the numbers are used. This method although very accurate is not 100% accurate.

For example

30*50*70*110 = 11550000

but

Exp(Sum(Ln(30);Ln(50);Ln(70);Ln(110))) = 11549999.9999999999972827

(as calculated by FMP)

A very tiny error and probably acceptable. If you go for 10 decimal place rounding then you will get

11550000.

But is this accurate enough? It depends on how the error propagates through the remainder of your calculations.

For example if we multiply each of the initial numbers by 10 then in the direct calculation we get an additional four 0's on the end but the FMP calculation is still running to 16 decimal places

115499999999.9999999645146429

and the final digits have changed. If you decided that 10 decimal place rounding was sufficent then you now have

115499999999.9999999645

and although tiny this is still an error. Can you be sure that if you use this type of calculation over several thousand records that the answer will be accurate?

If calculations like these are used at the beginning or even in the middle of a complex engineering solution (as they generally are) then a proper error analysis is required to ensure that the tail does not wag the dog.

Incidentally because 16 decimal places are not accurate in the first place I don't think that direct multiplication is any less prone to error.

For example use FMP to evaluate the following calculation

(1/3)*(1/3)*(1/3)*27

by the direct method

.9999999999999996

and the exp ln method

.9999999999999997

Both methods are inaccurate and differ from each other in the 16th decimal place part of the reason being the fact that 1/3 cannot be represented by a finite decimal. Doing this calculation

(1/2)*(1/2)*(1/2)*8

however gives an answer of 1 by each method. (accurate to within the 16 decimal place accuracy at which FMP is working)