1 2 Previous Next 17 Replies Latest reply on May 8, 2017 4:10 PM by howardh

# Calculations based on field's contents

Hello All,

I need to create a total based on other fields.

Ex.

If Field "Serviced By" = John Smith

then Multiply Field "hours worked" *150

Else Field "Serviced By" = "(anyone else) "

then Multiply Field "hours worked" *125

I thought it would be as simple as

If(Invoices::Serviced By =”John Smith”;Invoices::TotalHours * 150)

Each time it's saying that an operator is expected.

• ###### 1. Re: Calculations based on field's contents

In a calculation, IF takes both what to do if true and what to do if false:

If ( test ; resultOne ; resultTwo )

Your example, If(Invoices::Serviced By =”John Smith”;Invoices::TotalHours * 150), only has "resultOne". Thus you need to supply the "resultTwo" after adding a ";".

--

In a script, you can optionally just have and IF block without an else. If you find you need to embed multiple IF levels, don't use a calculation's IF unless you absolutely have to -- as it will be ugly and unwieldy. Use the much cleaner IF in a script:

HOPE THIS HELPS.

• ###### 2. Re: Calculations based on field's contents

The If function does not throw an error if you leave out the second result expression.

If(Invoices::Serviced By =”John Smith”;Invoices::TotalHours * 150)

appears to have perfectly correct syntax for a calculation field or other calculated value (but won't produce the desired results as it will return null if it's not "John Smith".)

As you have suggested, perhaps macfiles is trying to set this up as the IF script step?

• ###### 3. Re: Calculations based on field's contents

As a side note, you may want to add a billable rate field to the table with the employee info. That way, you end up with a calculation that is both simpler and won't go stale:

Employees::BillableRate * Invoices::TotalHours

You could either set a value for every employee, or set a default and make the calculation conditional on that field being empty:

If ( IsEmpty ( Employees::BillableRate ); 125 * Invoices::TotalHours ; Employees::BillableRate * Invoices::TotalHours )

--Ann

1 of 1 people found this helpful
• ###### 4. Re: Calculations based on field's contents

For the sake of completeness -- the If() function did in the past require a default result, but that changed some time ago. Sorry but I can't find a reference for this at the moment.

• ###### 5. Re: Calculations based on field's contents

Perhaps you've typed the correct calculation here, and you've made a typo in FMP?

This should work.

If ( Invoices::Serviced By =”John Smith”; 150 ; 125 ) * Invoices::TotalHours

Malcolm

• ###### 6. Re: Calculations based on field's contents

+1 on the reply by annr

Employees::BillableRate * Invoices::TotalHours

Is a much better approach. The people in your organization and their rates will change in the future. Your original calculation and the variations that we are creating by fixing it are examples of "brittle code". They break when simple and foreseeable changes take place. By using a table of employees and assigning a rate to each, you can manage change by updating the data in this table rather than by rewriting your calculation each time.

• ###### 7. Re: Calculations based on field's contents

Programming 101: NEVER EVER have hard-coded constants (so-called magic numbers) in formulas.

This is the kind of issue that would get flagged in any code review and, fortunately, as Phil suggested, is easy to fix.

• ###### 8. Re: Calculations based on field's contents

I freely admit that I'm nitpicking, but I'd change that statement to read:

NEVER EVER Have hard coded constants that are not true constants. After all, I might need to increment by 1 or use one of the many scientific or mathematical constants in my calculation.

• ###### 9. Re: Calculations based on field's contents

In the example above, I'm assuming we agree then that 150 and 125 should be named variables, not hard-coded values, right?

Thanks Phil.

• ###### 10. Re: Calculations based on field's contents

Either variables or values from the correct record/field.

The way that I stated this point in a presentation of a few months ago was:

Never treat values as constants when they aren't.

• ###### 11. Re: Calculations based on field's contents

Code Complete 2, MS Press, discusses Magic Numbers too. The first time Code Complete 2 discusses magic numbers is in a "Coding Horror" example on page 162. That routine includes several magic numbers including undefined for loop constraints.

For example there is a for loop like this:

for (int i = 0; i < 100; i++)

{...}

Here 100 is a magic number. Why 100? Not defined.

Also this particular example is flagged since it doesn't have a single purpose and has other problems.

for (int i = 0; i < MAX_ELEMENTS; i++)

{...}

would be better (and then pass any code review I've been in).

--

As you said, doing things like total = total + 1 is fine.

----

Page 292 lists three benefits of avoiding magic numbers.

----

Thanks Phil.

• ###### 12. Re: Calculations based on field's contents

The OP was having trouble with an IF statement. So, let's take baby steps. If we can get the IF statement working by providing clear, stable code, then @macFiles can get to work while we chew on our cigars and discuss the semantics of code.

malcolm

• ###### 13. Re: Calculations based on field's contents

Sounds good, Malcolm! Go for it.

beverly

• ###### 14. Re: Calculations based on field's contents

Sometimes you have to walk and chew gum at the same time.

1 2 Previous Next