Last piece of the puzzle!

[Willow_The_Whisp!]

Hi All,

I have a fairly trivial aged debtors database whereby I work out how old a clients debt (0-30, 31-60, 61-90 & 90 or older) is.

I have everything working as it should except(I feel so close yet so far):

The method that I have adopted to work out how old the debt is in each client record is to use a calculation field called debt_age that uses a current_date field with the calculation Status(CurrentDate). I should mention at this point that the debt_age calculation works fine. The problem I have is the current_date field presents the date fine on the record initially, but if I change the date on my system (testing the effects of a new day), the date in the current_date field holds the initial value and does not update or refresh inline with the date change.

Should I not rely on the Status(CurrentDate) calculation or am I missing something to trigger an update/refresh.

I would very much appreciate someone throwing me a line with this.

Cheers

Willow

[Lee Smith]

Hi Willow,

Others will probably jump in with answers, but to me you sounded like a person that would prefer to work this out yourself, so I thought I would reply how I would go about working this out for myself.

First of all, as you have found out, the Status(CurrentDate) function is not very intuitive when trying to debugs calculations, and changing the System dates back and forth can really be a hassle.

To get to the meat of the problem, I substitute a Global Field for it, until I'm sure my calculation is working properly. Here's how I do it.

First, create a global field for the date (g_date).

Second, depending on how may times the Status(CurrentDate), I would either change it by hand to my global g_date (for 1 or 2 incidences), for more I would use BBEdit (a text editor) to replace them.

Now you can change the g_date to what ever date you want to test the calculation. Once the thing is working the way you want, then go back in and change the g_date back to Status(CurrentDate).

Let me know if you would rather just have the answer.

Lee

[Willow_The_Whisp!]

Hi Lee,

I have already tested the calculation that involves the date field holding the calculation Status(CurrentDate) and if I use a global field in its place and manually adjust the date value I get the correct results. The problem I have is I want the date field holding the calculation Status(CurrentDate) to roll on dynamically day by day keeping inline with d/m/yyyy, as opposed to manually changing the date each time in a global field. I'm very sure this needs to be done by referencing the OS's date and time, I would just like to have the field current_date stay in sync with the system or at least reference it once a day.

thanks.

Willow

[underhat]

Willow_The_Whisp! said:

The problem I have is I want the date field holding the calculation Status(CurrentDate) to roll on dynamically day by day keeping inline with d/m/yyyy

Hi Willow,

Two options, depending on what you're doing.

Option 1, if you don't need any of your calculations (the ones which depend on the date) to be indexed:

You need to make the calculation unstored. Go into the Options for the calculation field, and on the Storage tab, you can do this. A stored calculation is saved on the record; an unstored calculation is calculated every time it's used, so it'll look up the date every time.

Option 2, if you need indexing (only a stored field can be indexed):

Instead of the Status(CurrentDate) fuction, use the Today function. The disadvantage is that every day, when your files are opened, they'll recalculate the calculation on every record (to update the date)m and this can take some time. Also, be careful -- if you leave the files open overnight, it won't recalculate. You have to close and open them to do this.

[Willow_The_Whisp!]

Hi Underhat,

Thank you very much!

To unstore the calculation is the solution I needed, and it seems a very straight forward one at that. The Today function I did look at but wanted to stay away from because my database will be held on a server host machine. I am not interested in relaunching the databases each day.

Thanks again.

[underhat]

Good plan -- glad you can avoid Today. If I recall correctly, you don't have to relaunch the server, but you do have to have everyone log out from the server. Then, the first person to login in the morning will have the calculation run on their computer -- usually pretty time consuming over the network.