Case Statement - only last statement not working

[dschaal]

I have a database for calculating benefits for each of our employees. The benefit is based on what job classification someone has and how many years they have worked. I created a Case statement which computes how much vacation time they get in hours each pay day.

All lines work fine and I have tried this on numerous examples with the exception of the last line. Perhaps I didn't end the calculation correctly??? It didn't say I was missing a ")" or ";" so I thought I was ok.

But what happens if I pick "union" and the person has 10 or more years of service; it returns nothing. But if they are 1-9 years it returns the right hours.

What am I missing???

Donna

If ( JobClassification = "P40"; Case ( HowManyYearsWorked = "1"; "6.154";

HowManyYearsWorked = "2"; "6.154";

HowManyYearsWorked = "3"; "6.154";

HowManyYearsWorked = "4"; "6.154";

HowManyYearsWorked = "5"; "6.154";

HowManyYearsWorked = "6"; "6.154";

HowManyYearsWorked = "7"; "6.154";

HowManyYearsWorked = "8"; "6.154";

HowManyYearsWorked = "9"; "6.154";

HowManyYearsWorked = "10"; "6.154";

HowManyYearsWorked = "11"; "6.154";

HowManyYearsWorked = "12"; "6.154";

HowManyYearsWorked = "13"; "6.154";

HowManyYearsWorked = "14"; "6.154";

HowManyYearsWorked = "15"; "6.154";

HowManyYearsWorked = "16"; "6.462";

HowManyYearsWorked = "17"; "6.769";

HowManyYearsWorked = "18"; "7.077";

HowManyYearsWorked = "19"; "7.385";

HowManyYearsWorked ≥ "20"; "7.692"

);

If ( JobClassification = "S40"; Case ( HowManyYearsWorked = "1"; "3.077";

HowManyYearsWorked = "2"; "3.385";

HowManyYearsWorked = "3"; "3.692";

HowManyYearsWorked = "4"; "4";

HowManyYearsWorked = "5"; "4.308";

HowManyYearsWorked = "6"; "4.615";

HowManyYearsWorked = "7"; "4.923";

HowManyYearsWorked = "8"; "5.231";

HowManyYearsWorked = "9"; "5.538";

HowManyYearsWorked = "10"; "5.846";

HowManyYearsWorked = "11"; "6.154";

HowManyYearsWorked = "12"; "6.154";

HowManyYearsWorked = "13"; "6.154";

HowManyYearsWorked = "14"; "6.154";

HowManyYearsWorked = "15"; "6.154";

HowManyYearsWorked = "16"; "6.154";

HowManyYearsWorked = "17"; "6.154";

HowManyYearsWorked = "18"; "6.154";

HowManyYearsWorked = "19"; "6.154";

HowManyYearsWorked ≥ "20"; "7.692"

);

If ( JobClassification = "S37.5"; Case ( HowManyYearsWorked = "1"; "2.885";

HowManyYearsWorked = "2"; "3.173";

HowManyYearsWorked = "3"; "3.462";

HowManyYearsWorked = "4"; "3.750";

HowManyYearsWorked = "5"; "4.038";

HowManyYearsWorked = "6"; "4.327";

HowManyYearsWorked = "7"; "4.615";

HowManyYearsWorked = "8"; "4.904";

HowManyYearsWorked = "9"; "5.192";

HowManyYearsWorked = "10"; "5.481";

HowManyYearsWorked = "11"; "5.769";

HowManyYearsWorked = "12"; "5.769";

HowManyYearsWorked = "13"; "5.769";

HowManyYearsWorked = "14"; "5.769";

HowManyYearsWorked = "15"; "5.769";

HowManyYearsWorked = "16"; "5.769";

HowManyYearsWorked = "17"; "5.769";

HowManyYearsWorked = "18"; "5.769";

HowManyYearsWorked = "19"; "5.769";

HowManyYearsWorked ≥ "20"; "7.212"

);

If ( JobClassification = "S30"; Case ( HowManyYearsWorked = "1"; "2.308";

HowManyYearsWorked = "2"; "2.538";

HowManyYearsWorked = "3"; "2.769";

HowManyYearsWorked = "4"; "3";

HowManyYearsWorked = "5"; "3.231";

HowManyYearsWorked = "6"; "3.462";

HowManyYearsWorked = "7"; "3.692";

HowManyYearsWorked = "8"; "3.923";

HowManyYearsWorked = "9"; "4.154";

HowManyYearsWorked = "10"; "4.385";

HowManyYearsWorked = "11"; "4.615";

HowManyYearsWorked = "12"; "4.615";

HowManyYearsWorked = "13"; "4.615";

HowManyYearsWorked = "14"; "4.615";

HowManyYearsWorked = "15"; "4.615";

HowManyYearsWorked = "16"; "4.615";

HowManyYearsWorked = "17"; "4.615";

HowManyYearsWorked = "18"; "4.615";

HowManyYearsWorked = "19"; "4.615";

HowManyYearsWorked ≥ "20"; "5.769"

);

If ( JobClassification = "Union"; Case ( HowManyYearsWorked = "1"; "4.326923076923077";

HowManyYearsWorked = "2"; "4.326923076923077";

HowManyYearsWorked = "3"; "4.326923076923077";

HowManyYearsWorked = "4"; "4.326923076923077";

HowManyYearsWorked = "5"; "4.326923076923077";

HowManyYearsWorked ≥ "6"; "5.769230769230769"

))))))

[LaRetta]

Hi Donna,

I highly suggest that you use a Rates table instead of hard-coding the values within a Case() statement. By using another table, it is 1) easier to update the values and 2) more efficient and flexible. If you would like assistance adding a table, we would be happy to assist. :^)

As an aside, an If() calculation only allows two choices. I didn't really look at your calculation because it is certainly not the best way to handle the situation but I suspect that is why it breaks.

[Vaughan]

Agree with LaRetta.

I've re-written the code. Notice that if JobClassification is not equal to P40, S40, S37.5, S30 or Union then the expression will not return anything. This is as you're coded it, not sure of that's what you intended.

Also note that "1" is strictly speaking not 1. Anything in quotes is literal text. FMP will perform as expected but best learn about it now.

Case (



JobClassification = "P40"; 

Case( 

HowManyYearsWorked = "1"; "6.154";

HowManyYearsWorked = "2"; "6.154";

HowManyYearsWorked = "3"; "6.154";

HowManyYearsWorked = "4"; "6.154";

HowManyYearsWorked = "5"; "6.154";

HowManyYearsWorked = "6"; "6.154";

HowManyYearsWorked = "7"; "6.154";

HowManyYearsWorked = "8"; "6.154";

HowManyYearsWorked = "9"; "6.154";

HowManyYearsWorked = "10"; "6.154";

HowManyYearsWorked = "11"; "6.154";

HowManyYearsWorked = "12"; "6.154";

HowManyYearsWorked = "13"; "6.154";

HowManyYearsWorked = "14"; "6.154";

HowManyYearsWorked = "15"; "6.154";

HowManyYearsWorked = "16"; "6.462";

HowManyYearsWorked = "17"; "6.769";

HowManyYearsWorked = "18"; "7.077";

HowManyYearsWorked = "19"; "7.385";

"7.692" );



JobClassification = "S40";

Case (

HowManyYearsWorked = "1"; "3.077";

HowManyYearsWorked = "2"; "3.385";

HowManyYearsWorked = "3"; "3.692";

HowManyYearsWorked = "4"; "4";

HowManyYearsWorked = "5"; "4.308";

HowManyYearsWorked = "6"; "4.615";

HowManyYearsWorked = "7"; "4.923";

HowManyYearsWorked = "8"; "5.231";

HowManyYearsWorked = "9"; "5.538";

HowManyYearsWorked = "10"; "5.846";

HowManyYearsWorked = "11"; "6.154";

HowManyYearsWorked = "12"; "6.154";

HowManyYearsWorked = "13"; "6.154";

HowManyYearsWorked = "14"; "6.154";

HowManyYearsWorked = "15"; "6.154";

HowManyYearsWorked = "16"; "6.154";

HowManyYearsWorked = "17"; "6.154";

HowManyYearsWorked = "18"; "6.154";

HowManyYearsWorked = "19"; "6.154";

"7.692" );



JobClassification = "S37.5";

Case (

HowManyYearsWorked = "1"; "2.885";

HowManyYearsWorked = "2"; "3.173";

HowManyYearsWorked = "3"; "3.462";

HowManyYearsWorked = "4"; "3.750";

HowManyYearsWorked = "5"; "4.038";

HowManyYearsWorked = "6"; "4.327";

HowManyYearsWorked = "7"; "4.615";

HowManyYearsWorked = "8"; "4.904";

HowManyYearsWorked = "9"; "5.192";

HowManyYearsWorked = "10"; "5.481";

HowManyYearsWorked = "11"; "5.769";

HowManyYearsWorked = "12"; "5.769";

HowManyYearsWorked = "13"; "5.769";

HowManyYearsWorked = "14"; "5.769";

HowManyYearsWorked = "15"; "5.769";

HowManyYearsWorked = "16"; "5.769";

HowManyYearsWorked = "17"; "5.769";

HowManyYearsWorked = "18"; "5.769";

HowManyYearsWorked = "19"; "5.769";

"7.212");



JobClassification = "S30";

Case (

HowManyYearsWorked = "1"; "2.308";

HowManyYearsWorked = "2"; "2.538";

HowManyYearsWorked = "3"; "2.769";

HowManyYearsWorked = "4"; "3";

HowManyYearsWorked = "5"; "3.231";

HowManyYearsWorked = "6"; "3.462";

HowManyYearsWorked = "7"; "3.692";

HowManyYearsWorked = "8"; "3.923";

HowManyYearsWorked = "9"; "4.154";

HowManyYearsWorked = "10"; "4.385";

HowManyYearsWorked = "11"; "4.615";

HowManyYearsWorked = "12"; "4.615";

HowManyYearsWorked = "13"; "4.615";

HowManyYearsWorked = "14"; "4.615";

HowManyYearsWorked = "15"; "4.615";

HowManyYearsWorked = "16"; "4.615";

HowManyYearsWorked = "17"; "4.615";

HowManyYearsWorked = "18"; "4.615";

HowManyYearsWorked = "19"; "4.615";

"5.769");



JobClassification = "Union";

Case (

HowManyYearsWorked = "1"; "4.326923076923077";

HowManyYearsWorked = "2"; "4.326923076923077";

HowManyYearsWorked = "3"; "4.326923076923077";

HowManyYearsWorked = "4"; "4.326923076923077";

HowManyYearsWorked = "5"; "4.326923076923077";

"5.769230769230769"

)



)

[dschaal]

Thank you both for the responses.....

This is an old database that I am trying to "refresh" with the new rates for vacation. Not my creation -- one that I inherited. It had "If" statements in it before but there were only 2 categories, so basically I took the simple way out and tried a case statement. A Rates table sounds like a good idea but I have questions (of course).

This database contains the following types of tables already.

Benefit Sheets (where the each employee has a record and each pay period their benefit is calculated along with any benefit that they might have taken).

Entry Date (in the past this was split out; not really sure why but it only contains the Entry Date - used in formula to calculate the longevity of an employee).

Fiscal Year (all benefits including carryover are based on fiscal year)

Personnel (this is a table that includes such things as the name, start date, percentage of work time, monthly vacation rate, etc.)

Perhaps the "Personnel" table can be used as the Rates table it already has the pertinent information in it. But here is where I question the usefulness of this, the calculation for each person would then have to be manually changed when they increase in years. And since no one started on the same date that seemed to me to be a lot more work for the payroll office.

Thoughts?

Thank you! Donna

[eos]

Here's a simple example of a Rates table, using a a relationship via jobClassification and a dynamic YearsWorked calculation to "look up” the matching value from the Personnel table.

RateLookup.fp7.zip

[flutegirl]

I agree with both other posters in that it would be better to create a new table to keep these rates in, rather than hard-coding them, however, as I was looking through this, I think I found the answer to the question you originally posed, and thought I'd take a shot at answering it in case it can help you in the future on a different problem.

From your initial question, you stated that when this breaks is when you have a union employee with 10 years of service, correct? It does work properly for 8 years, or 9 years?

I believe that's because you're referencing the years of service as a text field, not numeric.

So, when the classification is "Union", Filemaker will sort years worked in the following fashion:

1

10

11

12

13

14

15

16

17

18

19

2

20

3

4

5

6

7

8

9

The problem is, you're asking for ≥ "6", which only includes numbers beginning with 6, 7, 8, 9.

Since 10 begins with a 1, it - as a text string - is actually less than 6.

If you must keep things in this way, I'd suggest seeing if you can change the HowManyYearsWorked to a numeric field (and make sure it is an integer) then remove the quotes from your case statements so Filemaker will evaluate them as numerics. Then, 10 will (as expected) calculate to greater than 6.

[Fitch]

In answer to your initial question, I think flutegirl is on to something. It's those quotes around the numbers that are messing it up.

We all agree this belongs in a rate table, so in answer to your follow-up question, no, the rates would live in their own table, which you would relate to the personnel table via an unstored "years" calculation AND job class. You'd lookup the rate each month when you run payroll (by calculation or script, I mean, not manually). The example from eos should give you the general idea. He included a job class. table that is optional, but might make it easier to enter the rates.

[Vaughan]

I raised this in my first reply... :)

[dschaal]

Thank you everyone for the suggestions....I am trying it this week.... :twitch:

[dschaal]

Ok, so I have now added a rates table and I thought everything was calculating correctly, but I am having trouble with one of the formulas....in the zip file for the Rates table it was suggested I use this formula to calculate how long someone had been working for the company. I tweaked it for my field names only.

Let (

[ yearEntry = Year ( Start Date ) ;

YearNow = Year ( EntryDate2011::Date ) ;

dayEntry = DayOfYear ( Start Date ) ;

dayNow = DayOfYear ( EntryDate2011::Date ) ;

$$offset = Case ( dayNow < Start Date ; -1 ; 0 ) ;

result = yearNow - yearEntry + $$offset ] ;

Case ( result > 20 ; 20 ; result )

)

However, this isn't calculating correctly. I have an employee whose start date is: 5/11/2009. If I use an Entry Date of 5/31/2012, and I re-lookup the results of the calculation. Her result is 2 years.

But in reality she has worked for us for three years:

First year: 5/11/2009 - 5/11/2010

Second year: 5/11/2010 - 5/11/2011

Third year: 5/11/2011 - 5/11/2012

So, the Entry Date is after her third year anniversary, why doesn't the calculation give me that......then her Rate of vacation would go up as soon as her anniversary date hit....well, that is what I want anyway.

Any thoughts or suggestions?

Donna

[Tim W]

Hi Donna,

should not

$$offset = Case ( dayNow < Start Date ; -1 ; 0 ) ;

be 

$$offset = Case ( dayNow < dayEntry ; -1 ; 0 ) ;

HTH,

Tim

[dschaal]

That was it....thanks! Donna