Calculated field using 'today'

[Jeff Spall]

Hi, I'm making a change to our online employee contact list database so that administrators can make changes for starters and leavers ahead of time. I'll have a calculation field 'key' that is:

If{Today change_date and status="start", "no",

If{Today change_date and status="leave", "no","yes"}}

where the 'change_date' field is the start/leave date.

I'll then add key=yes to all the searches.

The question is, is it a bad idea to use the 'today' function in a field that will be searched on every time? Is this going to slow the database down?

The contact list only has about 250 records in it, but it is referenced by a dozen or more other databases to get email addresses etc. and I have some 'printable' pages that find all the records as a series of inlines and skips to make columns across the page, so this database works hard and slowing it down will affect the whole site.

thanks, Jeff

[Garry Claridge]

I've been using one which uses Status(CurrentDate). It is quite slow.

All the best.

Garry

[Jeff Spall]

Thanks Garry, I had my suspicion that it might be so.

.......Seemed like a good idea 'til I started working on it

regards, jeff

[Charlie Boisseau]

I had a DB which had several thousand (large) records. I tried "Today" and "CurrentDate" and they both were grindingly slow. Especially if you were recovering a file, it took half an hour or so just for the one DB. I would be interested to know of any other way of integrating current dates into calculations. Is there a Javascript to do it from CDML? The only implementation in my case is on the web.

Cheers.

[Jeff Spall]

Hi, seems like a calculation that's looking at the current date - a moving target!- is bad news, so I think the workround for me is to have a start/finish date in the database and put an inline action on the administrator's page that looks for records which need attention by checking that date.

Administrators will have to set the show/hide flag themselves as this is only happening a couple of times a month, yet my contact is referenced by just about every database we have.

Jeff

[Keith M. Davie]

I hope I am stating this correctly.

Jeff's problem is that he wants administrators to be able to perform certain functions ("so that administrators can make changes"). It appears that the solution he is trying to achieve is not an online solution but one served directly through the db. In Jeff's solution he will need to deal with ~250 records.

Jeff proposes a calculation-field solution and wonders if it will be slow.

Garry suggests that he has had a similar problem. Chazboi suggests slowness over several thousand records.

If I understand Jeff's If-conditional calculation correctly, Jeff is asking FileMaker to perform a calculation so that an action can be taken on the results of that calculation. If that is the case then it would appear that FileMaker must first perform that calculation in every record. That would explain the slowness. The more records the more the calculations which must be performed.

Designing solutions sometimes requires stepping back and looking at what you are really asking the application to do.

In Jeff's instance it appears to be an offline administration problem. Sometimes the problem is that the administrator does not want to deal with the administration at a time convenient for the db. Since Jeff's instance involves both direct access and browser access to the same db, it may be that there is a time when no one is accessing through the browser or db. At that time the administrator could arrange to do housekeeping activities, closing the website in the process. With that freedom many more solutions become possible.

It may be that the best solution is to serve the solution entirely through browsers, limiting any serious housekeeping activities to anywhere from once a week to once a month. In this scenario a limited number of carefully designed scripts can be safely run.

Another approach, which Chazboi may find interesting, was a solution which I posted in regards to Turansky's query on the cdml forum - both threads contain "currentdate" in the title. While the solution I posted suggested a browser solution for a date range, understanding the use of the symbols of the db find operation will allow you to alter that code to find just today's (or any day's) dated records. Also one needs to understand and allow for the way FileMaker enters "fmp-currentdate" in one's computer's format.

But then, perhaps I am misunderstanding the problem.

[Garry Claridge]

I believe the problem with date selections, with cdml, is that the dates (fields or currentdate) are converted to text. Hence, the only comparative operator available is 'equals'.

This is where I have used Javascript for some date operations and Calculated fields for others.

A better solution may be available!!!

All the best.

Garry

[Keith M. Davie]

Garry, in the solution to which I refer I formatted the date fields as dates, not text.

[Jeff Spall]

Thanks for taking such efforts over this guys!

To Clarify, all the access is through browser interface, when I say 'administrators' I mean our HR department who keep it up to date - i don't let them get at the databases!

It'll be simple to present them with a find of records which need attention and that avoiids the database calculating. There aren't mant records, but they are referenced several thousand times a day. Sometimmes the best way to solve a problem is to go round it!

thanks, jeff