writing a calculation

[Jenna]

Hello everyone,

I have a calculation field called "Total Payment" that I want to do two things:

1) Sum all the values of a certain field

2) Take that result and, if the result is less than 40, override the calculation and set the "Total Payment" field to 40.

Is there a way to do this in the calculation field without having to write a script? Here is what I have as a calculation for the TotalPayable field. I don't know how to "attach" the two parts of the calculation:

Sum(table:field); Case(TotalPayable < 40; “40”)

Do I need to use more parentheses? Brackets? What do I use instead of the semicolon?

Thank you!

[comment]

Sum all the values of a certain field

In the same table or in a related table?

[Jenna]

Well, I think it's a related table. (I'm sorry, I started this project last year and it's hard to remember what I did.) It has the double colon before the field name, so it must be a related table.

[comment]

Well, then =

Let (

total = Sum ( table::field )

;

Case ( total < 40 ; 40 ; total )

)

or simply = 

Max ( Sum ( table::field ) ; 40 )

should work fine.

[Jenna]

Thank you, it does work fine. There is one thing I didn't mention, because I thought it would be easy for me to add once I knew the basic calculation--but now it is making me lose my mind.

I'd like the total to be set to 40 if the total is less than 40 *and* if a certain value is selected from the drop-down menu. What I am doing is entering poems, essays, and short stories into a magazine's database to see how much we should pay each author, based on the length of their piece. If a poem is too short for the author to make $40 (at $1.50 per line), then we pay them the minimum $40. If an essay or story is too short for the author to make $100 (.08 cents a word), then we pay them the minimum $100.

I've already written the individual payment calculation scripts (to calculate the $1.50 per line and .08 cents a word).

Now, I need FMP to put a "40" in the total payment field only if "poetry" is selected from the drop-down menu (and the total is less than 40). Likewise, it should put a "100" if "not poetry" is selected from the drop-down (which contains a lot of "not poetry" value list items) and if that total is less than $100.

I tried adding to your calculation,

& table:genre_field = "Poetry"

But it didn't work. I'm sorry for being such a pain. You're so kind to help me!

Edited July 26, 2010 by Guest

[comment]

Use the logical operator "and" instead of the text operator "&".

[Jenna]

Somehow I keep messing up! Here is my calculation for the Total Payable field. When the total for a poem is less than $40 (and the "piece_payment2" field reads, say "35"), the Total Payable returns "4035." Why would that be, do you think?

Let (

total = Sum ( Con_ArticlessameIssue_Author::piece_payment2 )

;

Case ( total < 40 and genre = "Poetry" ; 40)

& Case ( total < 100 and genre = "Fiction" or "Essay" or "Interview" or "Review" or "Non-classifiable" or "Play" or "Translation" ; 100)

)

[comment]

the Total Payable returns "4035."

I don't think that's possible with the calculation you have posted. The result can be only one of these: "40", "100", "40100" or "" (empty). That's because you are concatenating the results of two Case() statements. You also have other syntax issues, for example:

genre = "Fiction" or "Essay" or "Interview" ...

instead of:

genre = "Fiction" or genre = "Essay" or genre = "Interview" ...

I *think* you want something like:

Max ( Sum ( Con_ArticlessameIssue_Author::piece_payment2 ) ; Case ( genre = "Poetry" ; 40 ; 100 ) ) 

but I am not sure I understand the requirements fully.

[Jenna]

Oh my goodness, you've done it! Thank you a thousand times.

If there were some way to mail you cookies without you having to post your address here, I totally would.

Yay!