# Dynamic formulas

This topic is 6636 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

Hi,

I am newbie in FMP. Some fields on a record are about the rate from a service provider:

ex: hours: 20\$/hrs

week: =hours*40

month: =year/12

year=week*52

So i'm using formula for thoses fields except for hours. What I really what to do is to be able to punch a number in any of the "Rate fields" and them automaticly it will updats the 3 others.

EX: punch "400" in week, will update hours at "10", month at "1 733", and year at 20 800.

EX2: punch "26 000" in year will update automaticly hours to "12.5" week to "500" and moth to "2167"

What will be the best way to do that ? and what will be the formula?

Sorry if i'm not in the good forum.

guy

##### Share on other sites

You can make each field an auto-enter calculation with 'do not replace existing value for field' deselected.

Hours:

Let( F = Get(ActiveFieldName);

Case( F = "Week"; Week / 40; F = "Month"; 3 * Month / 520; F = "Year"; Year / 2080; Hours )

)

Week:

Let( F = Get(ActiveFieldName);

Case( F = "Hours"; Hours * 40; F = "Month"; Div(3 * Month; 13); F = "Year"; Div(Year; 52); Week )

)

Month:

Let( F = Get(ActiveFieldName);

Case( F = "Hours"; Div( 520 * Hours; 3 ); F = "Week"; Div( 13 * Week; 3 ); F = "Year"; Div( Year; 12 ); Month )

)

Year:

Let( F = Get(ActiveFieldName);

Case( F = "Hours"; 2080 * Hours; F = "Week"; 52 * Week; F = "Month"; 12 * Month; Year )

)

These assume that you only want an integer result for the Week and Month fields, as your example seems to demonstrate. But you also want to round the result under particular circumstances. I could provide more accurate formulas if I knew when you want it to round and when it should return an integer.

##### Share on other sites

Wow ...

It is exactly, what i was looking for. It just works perfectly.

Thanks a lot.

now i have to study that, to try to understand what's all thoses: " ; Get f= Case () ,etc means !

Do you have any idea from where i could teach myself the "calculations fonctions" in fmp7.

I'm new to script and/or programmation, so i need to start from scratch some wher some how.

guy

##### Share on other sites

Get(ActiveFieldName) returns the most recently modified field name. Let is used to treat a temporary variable as the result of a calculation. If I didn't use Let, "Get(ActiveFieldName)" would be used in place of every instance of "F". The Case performs tests and returns a particular result when one of the tests is true. For example,

Case(

F = "Week"; Week / 40;

F = "Month"; 3 * Month / 520;

F = "Year"; Year / 2080;

Hours )

If the most recently modified field is Week, it divides the value in Week by 40 to return the number of hours. If Month was most recently modified, the value of Month is multiplied by 3 and divided by 520. If Year was most recently modified, the value of Year is divided by 2080. Otherwise, the current value of Hours is returned to itself, meaning that either Hours or some irrelevant field was last modified and we want to retain Hours' current value.

There is much to be learned from FM's built-in help system. More can be gleaned from the forums. And there are a few FM 7 books that should get you going. Do a search for FileMaker 7 on Amazon.com and pick one (or all!) of them.

##### Share on other sites

This topic is 6636 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account