How to do a lookup on a portal for a calculation

[Joseph31]

Hello everyone...

ok here it goes...

For this example I have two tables..."Policies" and "Premiums"

On the Policies table I have a Fields called "Escrow Amount" and "Escrow Factor"

On the Premium table (Viewed and enterd through a portal on the Policies Table) I have row numbers which will be years (1,2,3,4...etc.) and Amounts for each of the years..

What I need is help comming up with an equation that can calculate Escrow Amount:

Take the escrow factor (1.25) and multiply it by a Number (will be a field) Lets say the value is 6 so the answer will be 7.5

Now I need an equation that can look up on the premium list that I have entered...Look up year 7 (which will be row 7) and then take .5 of the next year wich in this case will be year 8 and add both numbers together...

Sounds easy..but I have no Idea how to do a lookup by years and return an amount...

Help

Joseph

[dbruggmann]

I don't see your conflict? Your

[Joseph31]

How do I tell it to take a number lets say 5.6...break it down so it looks up year 5 and does a calculation then it looks up year 6 (b/c the number was bigger that 5) and does a calculation on the number next to 6..?

[The Shadow]

There are a variety of built-ins to do the math:

Int(5.6) = 5

Mod(5.6; 1) = .6

[Joseph31]

I am not sure I understand...int(5.6)=5 so now I have the number how do I make a script that can look that number up on my premium table and pull off the coresponding value?

Example:

Year Premium

1 200

2 300

3 350

4 350

How do I pull off the value off of the number?

Joseph

[-Queue-]

Int(x) = integer portion of x.

Mod(x, y) = remainder of x/y.

You can use a global to year relationship. Set the global with the Int result, Commit Record, then set the appropriate amount with the related Premium. Do the same with Int + 1.

[Joseph31]

Can you give me an example...so I can study it?

Thank you

Joseph