8 Replies Latest reply on May 2, 2013 2:06 PM by brian.curran

# Calculating Holiday Entitlement, summing between two dates...

Hi,

I'm working on an Employees database and want to calculate holiday entitlement.

In the "Employees" table, I specify the holiday year (01 May to 30 Apr) and the number of days holiday (28)

In the "Absence" table, which will appear as a portal on the Employees layout, I want to record the start and end dates of a holiday and have the fields below calculated:

_cElapsedDays Holiday start date (31 Aug) - Holiday Year start date (01 May) = 122 days elapsed

_cElapsedMonths _cElapsedDays / 30.33 = 4 months elapsed

Days Accrued 28 days / 12 months X _cElapsedMonths = 9.33 days accrued

Taken 2

Requested 6

Balance Days Accrued - Taken - Requested = 1.33

The calculation for the "Taken" field is where I'm struggling, I want to add up all previously "Requested" days but only from the start of the Holiday year up until the current Start date. Does that make sense?

Thanks

Brian.

• ###### 1. Re: Calculating Holiday Entitlement, summing between two dates...

The issue you're running into is that you're trying to count two different kinds of records (requested and taken) from one table (absences). To do so correctly, you need an indicator of "status" (requested/taken), and then the calculations required to add up based on that indicator.

If you're on version 12, ExecuteSQL would make this easy:

For taken:

ExecuteSQL (

"

SELECT COUNT(anyfield)

FROM absences

WHERE userid = ?

AND indicator = 'taken'

" ; "" ; "" ; userid )

For requested:

ExecuteSQL (

"

SELECT COUNT(anyfield)

FROM absences

WHERE userid = ?

AND indicator = 'requested'

" ; "" ; "" ; userid )

If you have version 11 or later, it's a little more complex.

• ###### 2. Re: Calculating Holiday Entitlement, summing between two dates...

Hi Mike,

I haven't used ExecuteSQL before so I need a little help if possible...

I've added the 'Indicator' field but where does the SQL details go?

Thanks

Brian.

• ###### 3. Re: Calculating Holiday Entitlement, summing between two dates...

Just create an unstored calculation field that has my above code as the calculation, replacing the lower case bits with the corresponding fields in your table.

ExecuteSQL() is extremely useful and I'd recommend getting a good handle on using it right away.

Beverly Voth's guide is IMHO the defacto complete guide to ExecuteSQL(), and covers almost everything that it can do (and the related syntax, and examples)

http://www.filemakerhacks.com/?p=6406

You can also find some stuff on youtube by searching "ExecuteSQL Filemaker"

• ###### 4. Re: Calculating Holiday Entitlement, summing between two dates...

Thanks Mike, I'll take a look at Beverley's guide.

Is it one unstored calculation for Taken and another for Requested or do I combine both ExecuteSQL's in the same calculation field?

• ###### 5. Re: Calculating Holiday Entitlement, summing between two dates...

separate calc field for each so you can use it as part of your higher level calculations you outlined in your original post.

Although you could do everything you want in a single, complex calculation field ( recommend you learn the Let([];) function before that though)

• ###### 6. Re: Calculating Holiday Entitlement, summing between two dates...

Ok, seperate calcs done and I've changed COUNT to SUM as I want the total number of days and not the number of holidays. Unfortunately, the dreaded "?" appears for an answer.

However, if I delete the AND indicator = 'taken' line, the field correctly returns 10, which is the total of two records. How do I add this back in without breaking the SQL answer?

• ###### 7. Re: Calculating Holiday Entitlement, summing between two dates...

Do you have a sample/sandbox file I can poke around and look at your tables/calcs/fields? Might be easier. (feel free to send me it via private message)

I'd imagine it's breaking because you're trying to SUM a field that can't be summarized. Take a look at the field inside SUM( ___here___ ) and try and figure out why it wouldn't be allowed to be added up.

• ###### 8. Re: Calculating Holiday Entitlement, summing between two dates...

PM sent, thanks Mike