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:

Howard Schlossberg <howard (AT) nospam (DOT) fmprosolutions.com> wrote:

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 (AT) SPAM-alice (DOT) it>,
borgobello-NO (AT) SPAM-alice (DOT) it (Aladino) wrote:

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 )

"Your Name" <your.name (AT) isp (DOT) com> schreef in bericht
news:your.name-2503101622520001 (AT) 203-109-167-143 (DOT) dial.dyn.ihug.co.nz...
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 (AT) ziggo (DOT) nl> wrote:

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 (AT) SPAM-alice (DOT) 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 (AT) SPAM-alice (DOT) it> schreef in bericht
news:1jfx6i4.q5mkb15v03yzN%borgobello-NO (AT) SPAM-alice (DOT) it...

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/downloa.../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 (AT) ziggo (DOT) 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 (AT) SPAM-alice (DOT) it> schreef in bericht
news:1jfzb5w.15pobdb1mzm4gN%borgobello-NO (AT) SPAM-alice (DOT) it...