Field definition using a time calculation

[Hoggy]

Hi there,

First post on here for quite a while, and I'm hopeful I won't embarrass myself too much with this fairly simple question. I've done a couple of searches but can't seem to turn anything up.

I'm building a database that will track tasks for employees, a cross between a scheduler and a to-do list. I need a field with today's date in it, so that portals with tasks display appropriately. The problem is that it's quite commonplace for someone to still be working on today's tasks after midnight, and Filemaker thinks that it's tomorrow.

So my solution was to use a calculated field, but I'm having trouble with the syntax. The logic I want to use is this:

If (time is greater than 6.00am)

Field is today's date

Otherwise

Field is today's date - 1

So that any time up until 5.59am the field is still displaying the previous day's date.

The nearest I got was this:

If ( Get ( CurrentTime ) ≥ Time ( 6 ; 0 ; 0 );

Get ( CurrentDate );

(Get ( CurrentDate)-1))

But it seems to always display the previous day's date, no matter what.

I think my problem is adding and subtracting from dates and times, can anyone shed any light on this?

Thanks!

Stuart

[Tim W]

Hi Stuart,

Instead, use the case statement as follows:

Case(Get ( CurrentTime ) ≥ Time ( 6 ; 0 ; 0 );

Get ( CurrentDate );

(Get ( CurrentDate)-1))

See if that gets better results.

Tim

[comment]

There's no difference between Case() and If() here. But any calculation that uses Get (CurrentTime) or Get (CurrentDate) needs to be unstored in order to update (and it will only update when the screen is refreshed).

BTW, the last pair of parentheses is redundant.

[Hoggy]

Works!

I copied the Case( code in, but also changed the storage options so the result isn't stored and that's it working.

One little thing that's bugging me now - I wanted this calculated date field to be a global field - there's no point in having it recalculated for every record. However it seems if I want to have it global it has to be stored. When I check one box the other one unchecks.

Any way round this? It's not a big deal but I'm interested...

[comment]

An unstored calculation is evaluated only when needed, so there's not much difference here - unless you are viewing a list of 50 records with the field in the body.

But I am puzzled by the purpose of this field. Suppose you are sitting at your desk at 5:59 AM. Come 6 o'clock, nothing will happen. If, say at 6:05, you move to another record, the field will jump to today's date and stay there until AFTER 6:00 AM tomorrow. This information is not attached to any particular record. What is it good for?

[Hoggy]

That make sense to me about the storage. Thanks!

The purpose of the field is to define the relationship between portals in the "display" table, and the task items themselves.

For example, someone will have been set tasks to do on Monday, and others to do on Tuesday. There is a portal on the main display field which lists "Today's tasks" using the relationship.

The main problem with using just a straightforward date field, is that at the stroke of midnight all of today's tasks disappear and tomorrow's are displayed. Even though most of us would still think of one minute past midnight as being "today" still.

I picked 6am as a good time to divide the two days. It would need to be an exceptionally late night to last beyond then, and it's extremely rare for any of us to have started work by that time!

Hope this makes things a bit clearer. If anyone can suggest better ways of doing this, I'm all ears. I do get the feeling sometimes that my Filemaker solutions are just a series of bodges... :)

[comment]

OK, that makes sense.