Calculation that changes based on 2 fields

[ambweb]

Happy Holidays to everyone. Working on a contract database for a client and I'm having a problem with a deposit calculation. The way the system works is when a deposit is received for a contract, the date and amount are entered into two seperate fields. The agency gets a % of the deposit which is taken off the top, the rest going into an escrow account. I was able to set up a calc which compared the deposit to the agency's % and then split it accordingly into an agency % field and an escrow field. The problem I have is this. About 5% of the time, the agency will receive deposits in two shifts. There is a separate date and amount field for the instance of a second deposit. How do I alter my current calculation, so that it will calculate based on the first deposit being entered, but will then alter that info if a second deposit comes into play. I'll give an example:

Let's say the total amount of the contract is $5000.00, the deposit should be 10% of that or $500, and the agency's % is 15% or $750. A deposit comes in for $450 on Jan 1. Because it is less than 15% of the contract, all of that goes directly into the agency % field. On Jan 15, a second deposit of $450 comes in. The two should be added and compared to the %, and then 750 goes into the % field and 150 into escrow...This is where my skills are failing me... Should I be using an OR, an If... I can't figure it out. If there is only one deposit it works great. The calc is something like this: If (Deposit Amount) is less than or equal to Contract Amount*Agency%, Deposit Amount, Contract Amount*Agency%)... How do I factor in the second variable, so that it takes a second deposit into account and changes the results? All help is welcome. Thanks!

[andygaunt]

Can't you just create a calculation that is deposit 1 + deposit 2, then use this field in your current calculation?

Then it won't care where the deposit is entered. If only $750 in deposit 1, then it shows $750. If $750 in both, then it shows the $1500.

HTH

[kennedy]

There's numerous ways to do the math... which is best depends somewhat on what fields you have set up in what records.

For example, assuming MRMR is the most recent matching record prior to this record, and in each record you have computed fields:

total_deposit = deposit + MRMR::total_deposit

total_agency = MIN(contract * 15%, total_deposit)

total_escrow = total_deposit - total_agency

That's just one possibility... others could involve getting all the related records and summing.

HTH.