8 Replies Latest reply on Oct 14, 2010 8:11 AM by philmodjunk

# Script to make calculation show \$0, if result negative

### Title

Script to make calculation show \$0, if result negative

### Post

How do i write a script to do the following:

1. Perform a specific calculation

2. If the calculation result is negative, just show \$0.00 as the result

3. if the calculation is positive, show the result.

I'm on FMP 11.

Thanks.

• ###### 1. Re: Script to make calculation show \$0, if result negative

You don't really need a script for this in most cases.

This calculation will replace negative values with zero:

max ( //put your calculation here ; 0 )

• ###### 2. Re: Script to make calculation show \$0, if result negative

that worked perfectly, thanks!

• ###### 3. Re: Script to make calculation show \$0, if result negative

Update:

The calculation worked in that when the result was negative, it showed \$0.

However, when i used that field in a sum calculation, it seems that the sum calculation is interpreting the field with a negative number as negative, instead of zero.  How do i fix that?

• ###### 4. Re: Script to make calculation show \$0, if result negative

Please give an example of how you are using the sum calculation. What you are describing should not be the case, but there are a number of different ways to calculate a "sum" so I need to see exactly what you've done before I can offer a fix to you.

• ###### 5. Re: Script to make calculation show \$0, if result negative

i actually got it to work by using a '+' sign, instead of doing 'sum (field 1, field 2 etc...).

Why did it work w/ the + sign but not the sum calculation?  Do they mean different things?

• ###### 6. Re: Script to make calculation show \$0, if result negative

Sum ( field 1 ; field 2 ; field 3 ) and Field 1 + field 2 + field 3 will give you exactly the same results.

Sum ( relatedTable::Field ) on the other hand, adds up the values of Field from all related records.

• ###### 7. Re: Script to make calculation show \$0, if result negative
The fields that were part of the calculation were all related fields.  does that mean I should use the + sign for all related field calculations in which I need a sum?
• ###### 8. Re: Script to make calculation show \$0, if result negative

That's not a question I can answer fully without knowing the structure of your database.

If you have a calculation such as RelatedTable::Field1 + RelatedTable::field2, you are adding data the "first" such related record--something that is controlled by what options you've selected in your relationship. Whether the first related record is the correct one for the results that you want is the part I can't tell you.

The Sum function, on the other hand, adds up all the related records--so this at least explains why you were getting different results.

Note: if all the fields you are adding together come from the same related table, you should define this calculation in the related table instead of where you have defined it now.