Jump to content

Calculated field using 'today'


Jeff Spall
 Share

This topic is 7168 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 less.gif change_date and status="start", "no",

If{Today great.gif 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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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! smile.gif

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

Link to comment
Share on other sites

This topic is 7168 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.