5 Replies Latest reply on Feb 7, 2014 8:21 AM by philmodjunk

Enter calculation into a field based on a number.

Title

Enter calculation into a field based on a number.

Post

We have a contract that pulls in tuition amount based on age group.  The initial enrollment deposit is based on the tuition and minus any scholarship they may receive.  So there is a tuition field, scholarship field and the tuition minus scholarship field,  we just need a field with a look up for the deposit.  Thanks

\$30,001 - 40,000 = \$4000 deposit

\$20,001 - 30,000 = \$3000 deposit

\$10,001 - 30,000 = \$2,000 deposit

\$1.00 - 10,000 = \$1,000 deposit

• 1. Re: Enter calculation into a field based on a number.

\$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.

• 2. Re: Enter calculation into a field based on a number.

Hi Phil,

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.

• 3. Re: Enter calculation into a field based on a number.

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.

• 4. Re: Enter calculation into a field based on a number.

Hi Phil,

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

• 5. Re: Enter calculation into a field based on a number.

"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 <.