I'm looking to write a recursive function interpole some values with a
table. I put the table values in multi-fields, but now I need to find an
easy way to interpole some values. To clarfy wath I mean, I've two
multiple field contaning this values:
Field A         Field B
5000            16,22
7500            14,96
10000           14,12
15000           13,03
20000           12,29
25000           11,76
35000           10,99
50000           10,23
75000           9,43
100000          8,91
150000          8,22
200000          7,76
250000          7,41
350000          6,93
500000          6,46

The function I need shold be somthing like this:

Iterpole (value; field A; field B)

and if for example, value is 170000 the result should be:

(200000-150000)/(170000-150000)*(7,76-8,22)+8,22=8,032

Can someone figure if a function like this can be done with filemaker
(Advanced 9)?

-- 
Per rispondere, togliere -NOSPAM- dall'indirizzo.

You can try one of these custom functions:
http://www.briandunning.com/cf/108
http://www.briandunning.com/cf/844


Aladino wrote:
> I'm looking to write a recursive function interpole some values with a
> table. 
 >
> Can someone figure if a function like this can be done with filemaker
> (Advanced 9)?
> 

Howard Schlossberg <howard@nospam.fmprosolutions.com> wrote:

> You can try one of these custom functions:
> http://www.briandunning.com/cf/108
> http://www.briandunning.com/cf/844
I found it too, thanks... but they are not recursive. They work only on
one interpolation. I need a function that can be used on a indefinite
number of values...
I think it can be done with filemaker, but until now I wasn't able to do
it.
-- 
Per rispondere, togliere -NOSPAM- dall'indirizzo.

In article <1jfvo8j.dnx2oe1dxqo21N%borgobello-NO@SPAM-alice.it>,
borgobello-NO@SPAM-alice.it (Aladino) wrote:

> Howard Schlossberg <howard@nospam.fmprosolutions.com> wrote:
> 
> > You can try one of these custom functions:
> > http://www.briandunning.com/cf/108
> > http://www.briandunning.com/cf/844
> 
> I found it too, thanks... but they are not recursive. They work only on
> one interpolation. I need a function that can be used on a indefinite
> number of values...
> I think it can be done with filemaker, but until now I wasn't able to do
> it.

You can set-up a Script to keep calling itself until it meets a particular
"stop" value, although I'm not sure how many times it will do that.

I did do a quick test FileMaker database for something like this a while
back when someone else asked a similar question, but I can't find it now
so I don't think I kept it.

Helpful Harry  :o)

"Your Name" <your.name@isp.com> schreef in bericht 
news:your.name-2503101622520001@203-109-167-143.dial.dyn.ihug.co.nz...
> In article <1jfvo8j.dnx2oe1dxqo21N%borgobello-NO@SPAM-alice.it>,
> borgobello-NO@SPAM-alice.it (Aladino) wrote:
>
>> Howard Schlossberg <howard@nospam.fmprosolutions.com> wrote:
>>
>> > You can try one of these custom functions:
>> > http://www.briandunning.com/cf/108
>> > http://www.briandunning.com/cf/844
>>
>> I found it too, thanks... but they are not recursive. They work only on
>> one interpolation. I need a function that can be used on a indefinite
>> number of values...
>> I think it can be done with filemaker, but until now I wasn't able to do
>> it.
>
> You can set-up a Script to keep calling itself until it meets a particular
> "stop" value, although I'm not sure how many times it will do that.
>
> I did do a quick test FileMaker database for something like this a while
> back when someone else asked a similar question, but I can't find it now
> so I don't think I kept it.
>
> Helpful Harry  :o)

Such a loop would start at the lowest value. Loops until lower nearest value 
is reached, do some calculations, move on one record and calc again. So if 
you have n records it will do a maximum of n loops for a valid value and a 
maximum of n+1 loops to discover a value is out of bounds. With large lists 
this could result in a long waiting time.

I would probably try something along the following line. Create a 
realationship where  FieldA <= Value. Sort on FieldA will always yield the 
first lower valueA to be the first value. The relationship  in reverse Value 
< FieldA would yield the first value following the given. (Omitted the = on 
purpose!)

I haven't tried and tested, but this might be my starting point.

-- 
Keep well / Hou je goed

Ursus 

Ursus <ursus.kirk@ziggo.nl> wrote:

> I would probably try something along the following line. Create a 
> realationship where  FieldA <= Value
No, I need a function. I need it to permit to use it in editable
formulas inside textfields that I calc with Evaluate function inside
some scritp. I need users can modify this formulas that use the
interpolation command.
So as first step I wrote this function that returns the number of fields
of a multiple field (didn't find any other way):

NumeroValori (campoMultiplo; contatore) =
Case (
        GetRepetition ( campoMultiplo; contatore+1 )  =  "?"; contatore;
        GetRepetition ( campoMultiplo; contatore+1 )  =  0; contatore;
        NumeroValori (campoMultiplo; contatore+1)
)

Second GetRepetition can be omitted if you want to hold 0 values valid.
The question on this function definition is: is there a way to omitt the
counter in the function arguments? I tried to use a variable instead,
but wasn't able to make the function work. Global variable will not be
reseted, local variable can not been defined as incremental of it self
(parameter not found error).
The function above works just fine (you may use contatore=0 as first
call), but I'd like to know if there's a more elegant way (without
passing useless values)


-- 
Per rispondere, togliere -NOSPAM- dall'indirizzo.

Aladino <borgobello-NO@SPAM-alice.it> wrote:

This makes the job, but has the same "issue" as the prevouis:

Interpola ( numero ; campoMultiplo1 ; campoMultiplo2 ; contatore ) =
Let ( [
        contatore=contatore+1;
        x1=GetRepetition ( campoMultiplo1; contatore );
        y1=GetRepetition ( campoMultiplo2; contatore );
        x2=GetRepetition ( campoMultiplo1; contatore+1 );
        y2=GetRepetition ( campoMultiplo2; contatore+1 );
        x3=GetRepetition ( campoMultiplo1; contatore+2 )];
Case (
        x1="?" or x2="?" or y1="?" or y2="?";  "?";
        x1="" or x2="" or y1="" or y2="";  "";
        numero ≤ x1; y1;
        numero ≤ x2; (numero-x1)/(x2-x1)*(y2-y1)+y1;
        x3 = "?" or x3=""; y2;
        Interpola ( numero ; campoMultiplo1 ; campoMultiplo2 ; contatore
)
))


-- 
Per rispondere, togliere -NOSPAM- dall'indirizzo.

"Aladino" <borgobello-NO@SPAM-alice.it> schreef in bericht 
news:1jfx6i4.q5mkb15v03yzN%borgobello-NO@SPAM-alice.it...
> Aladino <borgobello-NO@SPAM-alice.it> wrote:
>
> This makes the job, but has the same "issue" as the prevouis:
>
> Interpola ( numero ; campoMultiplo1 ; campoMultiplo2 ; contatore ) =
> Let ( [
>        contatore=contatore+1;
>        x1=GetRepetition ( campoMultiplo1; contatore );
>        y1=GetRepetition ( campoMultiplo2; contatore );
>        x2=GetRepetition ( campoMultiplo1; contatore+1 );
>        y2=GetRepetition ( campoMultiplo2; contatore+1 );
>        x3=GetRepetition ( campoMultiplo1; contatore+2 )];
> Case (
>        x1="?" or x2="?" or y1="?" or y2="?";  "?";
>        x1="" or x2="" or y1="" or y2="";  "";
>        numero ? x1; y1;
>        numero ? x2; (numero-x1)/(x2-x1)*(y2-y1)+y1;
>        x3 = "?" or x3=""; y2;
>        Interpola ( numero ; campoMultiplo1 ; campoMultiplo2 ; contatore
> )
> ))
>
>
> -- 


Well at least I'm seeing what you have done and what you are trying to 
achieve. If I understood correctly you are using repeating fileds. These are 
very, very difficult to work with. Really you should NOT use them. They do 
have their use, but storing masses of data is not one of them. Really, I'm 
not joking: stop using them!

Then we have a look at your function. Without going into much detail I see a 
couple of things i didn't understand. Like the first line after the case. If 
all this is true it returns a "?" and exits the function completely. Same 
after the second line, same after the third line. it just puts the value of 
y1 as the result and exits.

You might want to read the white paper on recursions, found here: 
http://www.excelisys.com/web/downloads/files/Recursive.zip

Then rethink your complete filesetup to fit your needs. Never just start 
entering some data and then try to fil your solutions around them.

Keep well, Ursus

Ursus <ursus.kirk@ziggo.nl> wrote:

Unicode did some joke. Here is the corrected function:

Interpola ( numero ; campoMultiplo1 ; campoMultiplo2 ; contatore ) =
Let ( [
        contatore=contatore+1;
        x1=GetRepetition ( campoMultiplo1; contatore );
        y1=GetRepetition ( campoMultiplo2; contatore );
        x2=GetRepetition ( campoMultiplo1; contatore+1 );
        y2=GetRepetition ( campoMultiplo2; contatore+1 );
        x3=GetRepetition ( campoMultiplo1; contatore+2 )];
Case (
        x1="?" or x2="?" or y1="?" or y2="?";  "?";
        x1="" or x2="" or y1="" or y2="";  "";
        numero =< x1; y1;
        numero =< x2; (numero-x1)/(x2-x1)*(y2-y1)+y1;
        x3 = "?" or x3=""; y2;
        Interpola ( numero ; campoMultiplo1 ; campoMultiplo2 ; contatore
)
))

The first two case exit the script if repeating filed is ended (returns
"?") or if it has an empty cell.
To not use repeating fileds I tried to do the same with variables, it
works too, but I don't like do define previously the variables, without
giving the user the ability to change wich of them to use in
calculations he should be able to write in text fields and that my
script will evaluate. My idea is to give to users a reference with all
usable variables, so they can change calculation without touching
scripts or definitions.
Script using variables is this:

InterpolaV ( numero ; contatore ) =
Let ( [
        x1=$TabH1[contatore];
        y1=$TabH2[contatore];
        contatore=contatore+1;
        x2=$TabH1[contatore];
        y2=$TabH2[contatore];
        x3=$TabH1[contatore+1]];
Case (
        x1="?" or x2="?" or y1="?" or y2="?";  "?";
        x1="" or x2="" or y1="" or y2="";  "";
        numero =< x1; y1;
        numero =< x2; (numero-x1)/(x2-x1)*(y2-y1)+y1;
        x3 = "?" or x3=""; y2;
        InterpolaV ( numero ; contatore )
))

This second function has the disadvantage that user can't decide wich
will be second serie of values (y) inside the calculations, and I must
add a field in which they can select this serie of values...
Did you have any other idea how to make users able to input
calculations, some with interpolations, some other that will look
completly different? All of this calculation will be tha way user will
be payed for his job, and should be printed in a sort of "job
assignment"
-- 
Per rispondere, togliere -NOSPAM- dall'indirizzo.

I would create a couple of fields extra. So that each record will hold both 
min/max values and both 'b' values

Min MAX B1 B2
5000 7500 16,22 14,69
7501 10000 14,96 14,12

That way it would be much more easy to find the values you need.

Also I would keep the LET part of your function outside custom function and 
move it inside a normal calculation within the record. That way you only 
pass the correct values into the custom function.

AND

I would make sure the custom function would be called only when all values 
needed are correct. No more error correcting within the custom function. 
This should also happen in the record itself.

By this time you will have done so much cleaning of your data that a custom 
function is no longer needed and you could just append the final calc. There 
is no gain in using a recursing custom function any more.

You even could create calcs for every step. But by the time you are done, 
you could also cram them into one normal calculation.

-- 
Keep well / Hou je goed

Ursus


"Aladino" <borgobello-NO@SPAM-alice.it> schreef in bericht 
news:1jfzb5w.15pobdb1mzm4gN%borgobello-NO@SPAM-alice.it...
> Ursus <ursus.kirk@ziggo.nl> wrote:
>
> Unicode did some joke. Here is the corrected function:
>
> Interpola ( numero ; campoMultiplo1 ; campoMultiplo2 ; contatore ) =
> Let ( [
>        contatore=contatore+1;
>        x1=GetRepetition ( campoMultiplo1; contatore );
>        y1=GetRepetition ( campoMultiplo2; contatore );
>        x2=GetRepetition ( campoMultiplo1; contatore+1 );
>        y2=GetRepetition ( campoMultiplo2; contatore+1 );
>        x3=GetRepetition ( campoMultiplo1; contatore+2 )];
> Case (
>        x1="?" or x2="?" or y1="?" or y2="?";  "?";
>        x1="" or x2="" or y1="" or y2="";  "";
>        numero =< x1; y1;
>        numero =< x2; (numero-x1)/(x2-x1)*(y2-y1)+y1;
>        x3 = "?" or x3=""; y2;
>        Interpola ( numero ; campoMultiplo1 ; campoMultiplo2 ; contatore
> )
> ))
>
> The first two case exit the script if repeating filed is ended (returns
> "?") or if it has an empty cell.
> To not use repeating fileds I tried to do the same with variables, it
> works too, but I don't like do define previously the variables, without
> giving the user the ability to change wich of them to use in
> calculations he should be able to write in text fields and that my
> script will evaluate. My idea is to give to users a reference with all
> usable variables, so they can change calculation without touching
> scripts or definitions.
> Script using variables is this:
>
> InterpolaV ( numero ; contatore ) =
> Let ( [
>        x1=$TabH1[contatore];
>        y1=$TabH2[contatore];
>        contatore=contatore+1;
>        x2=$TabH1[contatore];
>        y2=$TabH2[contatore];
>        x3=$TabH1[contatore+1]];
> Case (
>        x1="?" or x2="?" or y1="?" or y2="?";  "?";
>        x1="" or x2="" or y1="" or y2="";  "";
>        numero =< x1; y1;
>        numero =< x2; (numero-x1)/(x2-x1)*(y2-y1)+y1;
>        x3 = "?" or x3=""; y2;
>        InterpolaV ( numero ; contatore )
> ))
>
> This second function has the disadvantage that user can't decide wich
> will be second serie of values (y) inside the calculations, and I must
> add a field in which they can select this serie of values...
> Did you have any other idea how to make users able to input
> calculations, some with interpolations, some other that will look
> completly different? All of this calculation will be tha way user will
> be payed for his job, and should be printed in a sort of "job
> assignment"
> -- 
> Per rispondere, togliere -NOSPAM- dall'indirizzo.