SumIf calculation

[toby162]

Here is what I am trying to do but cannot seem to figure out:

I have a sub-contractor that does work for me and enters job details into Filemaker so we can pay him. He gets paid a different hourly amount depending on what type of work he does. So I have a portal where he enters the work he did and the amount of time he spent doing it. He also selects a value from a drop down list of the TYPE of work he did.

I would like to have a calculation that will add all the hours based on the TYPE of work he did.

For instance:

He does 2 hours of TYPE A work and 1 hour of TYPE B work on Monday. He then does 3 hours of TYPE A work and 2 hours of TYPE B work on Tuesday.

I would like a field to calculate 5 total hours of TYPE A work and 3 hours of TYPE B work.

This would allow me to use FileMaker to calculate the total amounts of different types of work he does and to easily calcualate the amount of money he needs to be paid.

Any ideas how I can do this?

[dreamingmind]

You should be able to do this with the Case statement:

Case (evaluate_this_boolean ; do_this_on_true ; do_this_on_false)

Case (type = "A" ; hours * 60 ; hours * 90)

Case can deal with multiple tests too for more complicated situations.

Regards,

Don

[David Jondreau]

Here's a custom function I came up with and others have improved that may help....

This can only be installed using FM9 Advanced. Unfortunately the forum is having difficulty showing the version of FM a poster uses.

*Problems with linking?

http://fmforums.com/forum/showtopic.php?tid/185593/

Edited September 22, 2008 by Guest

[comment]

In general, it's not good practice to store data in calculation formulae. Make yourself a table of Rates, and create a record there for each type of work, with its associated rate.

Use a relationship matching on type to get the rate. Usually, this would be done by a lookup, so that rates can be changed without affecting previously created records.

[mr_vodka]

DJ, just as an FYI, your link seems broken. Not sure if this due to the forums issues or what.

[sky]

Hi there,

You might want to have a look at this file I just knocked together... might help....

oops - download link for Zip file doesn't seem to be working... you can email me at [email protected] and I'll send it by return email...

Edited September 22, 2008 by Guest

[toby162]

I was able to use the custom function from D J and it works perfectly. Thanks for the help!!