I think your example should read:
$30,001 - 40,000 = $4000 deposit
$20,001 - 30,000 = $3000 deposit
$10,001 - 20,000 = $2,000 deposit
$1.00 - 10,000 = $1,000 deposit
Ceiling ( Amount / 10000 ) * 1000
will compute your deposits, but you may want to use a look up table instead of a formula to make it easier to restructure how your deposit is calculated in the future without needing to redesign a calculation to do so.
Thanks for your help again. Look up table may be better. Can you provide with both options (or how to) so I can try both in the system?
Also, do you know what this kind of formula writing/language (I know I am calling it by the incorrect name) is called? And if there is a book I could buy? I am old and these online tutorial don't let you do hands on work. You have to watch then switch to the program and try it out then switch back....frustrating and not efficient for me to learn.
This expression is just a typical calculation expression with syntax common to many programming languages and database systems. The names of the functions may very and may not be available in all, but the basic notation syntax is largely the same.
Functions such as Ceiling can be looked up in FileMaker Help. And you can look up the term function to get a complete list of all FileMaker built in functions.
With a look up table, you'd define a table with at least two fields:
MaxAmount ; Deposit
In your above example, you'd define 4 records:
40000 ; 4000
30000 ; 3000
20000 ; 2000
10000 ; 1000
You can then define a relationship such as:
MainTable::Amount < DepositTable::MaxAmount
And you'd specify a sort order that would sort the related records in descending order by MaxAmount.
Then an auto-enter field option such as looked up value or calculation can copy over the value of DepositTable::Deposit.
The advantage to this table based method is that you can choose at any time to modify the data in deposit and/or MaxAmount to start looking up new Deposits for different value ranges than used previously just be editing data in this table instead of calling in the FIleMaker Developer to pop the hood and redefine the values used in a calculation.
Sorry for not acknowledging your response sooner. This looks great but I do not have the slightest idea of how to go about setting it up in our system. If it is not to much trouble could you do a step by step of the fields and scripts I would have to create. If I could see an example of how it looks then I could plug my own fields in to make it work.
We have a field "Family Contribution" which is the amount the deposit it based on. And the "con_reservation_fee" is the field where the deposit amount shows up.
Create a look up table: How to define the relationship? Do you put the "MainTable::Amount < DepositTable::MaxAmount" phrase in the same lookup table as the amounts
"MainTable::Amount < DepositTable::MaxAmount" IS the relationship. To set up, you open Manage | Database | Relationships and drag from Amount to MaxAmount. Then double click the relationship line to open a dialog where you can change the = operator to <.