get(current date) problem / solution

[JLanclos]

I have a database that I created for someone to help me with this example - I want my filemaker database to update a field called CurrentDate in filemaker via the server everyday. If I use a date field and run a server side script once a night (current solution), then I have to wait for it to finish and it can take a while with 100,000 plus records. If I used a calculation field set to get(current date) then the server does not update it nightly - each morning I was to do a find on what invoices are past due and everyday that find should change depending on today related to the due date on the invoice - any suggestions?

InvoicesEx.fmp12

[rwoods]

Hello, your example file is password protected so I can open it to have a look.

If you use a simple calculation field with Get ( CurrentDate ), and go to Manage Database -> Fields -> (select your field) -> Options -> Storage Options and turn ON the option 'Do not store calculation results -- recalculate when needed', then the field will recalculate whenever required. This could be slow since it will recalculate every time it is required, but will guarantee your results are always up to date.

If you are searching on this field more than once per day, then running a script (on the server) that updates the field with Get ( CurrentDate ) (it would be a simple date field rather than a calculation in this case), would mean the work would only need doing once per day, and would be the fastest solution.

It seems like you have the answer to your question already, but if you upload an unlocked version off your sample file we could take a look.

[comment]

5 hours ago, JLanclos said:

each morning I was to do a find on what invoices are past due

This seems to be an XY problem. If you want to find invoices that are past due, why don't you simply search the DateDue field for records where the value is smaller than Get (CurrentDate)? There is absolutely no need to add a calculation field for this, and certainly no need to artificially update all records overnight, just because the date has changed.

This is assuming the DateDue field is stored; if not, why isn't it?

 

[JLanclos]

Sorry fellas - the login is admin 1234 for the demo file - I uploaded the file last night to Filemaker Server to see if the date would change after midnight - it did not - so I would to perform a script or function on it to make the date change - we use a similar database in our clients system - the client DOES NOT want to perform a search to find past due invoices because they use a dashboard which tells them what is past due - they want the dashboard to update automatically each day to populate the correct number of past due invoices - thanks

InvoicesEx.fmp12

rwoods - in your reply, this is what I'm doing if understand you right - I have it as a simple date field and im running a script to update every record each night - this is what I want to change since it is over 100,000 records

[webko]

3 hours ago, JLanclos said:

dashboard which tells them what is past due - they want the dashboard to update automatically each day to populate the correct number of past due invoices

And as Comment said - the Dashboard can update by finding DateDue < Get (CurrentDate) as part of it's loading process....

[JLanclos]

ok I will try to explain it another way - I don't want to do a find for past due invoices - I want users, while doing other tasks in the database, to recognize that the invoice they are working on is past due and how long it has been due - therefore I need the currentdate field to be accurate everyday so I can use it for aging and to perform other thing using it - my original question was how can i have the currentdate field be accurate to the current day everyday on every record - thanks 

[comment]

I wish you would make up your mind regarding what your question is really about. Because I am quite sure you started by saying you wanted to find the past-due invoices. Then you said no, you do not want to find them - you want to show their count in a dashboard. Now you're saying you want to have a past-due indicator when viewing an individual invoice (presumably in form view). Those are three very different things (I cannot stress this enough).

So, first: you do not need a "currentdate field" in order to indicate to your users that the invoice they are working on is past due. You only need to conditionally format some object to change its appearance when Get (CurrentDate) > Invoices::DueDate (you would probably want to add another condition to this, so that settled invoices are not flagged). Or you could hide some object when the opposite is true.

Next: you do not need a "currentdate field" in order to calculate aging, or perform any other calculation requiring the current date. Your calculation formula can refer to Get (CurrentDate) directly. But any such calculation field must be set to "Do not store calculation results -- recalculate when needed" in the field's indexing options.

However,  you would not want to use an unstored calculation field for finding records.