# Setting Up a Daily Calculation

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

## Recommended Posts

Hi All

I have a Database that deals with Bills and due dates. Every bill has a due date and I need to calculate every day the status of a bill: Overdue, Short Term Due, Long Term Due, etc.

The Get (Current Date) - Due Date (field) is not the right one, as I read in this forum, this function is not dynamic. My question is, which would be my best approach? I was thinking in setting up a Date Field in which I replace every single day the current date.

What do you think?

Regards

Mariano.

##### Share on other sites

I would add a field to the table for todaydate and make the calc

Let (

_d - todaydate - duedate ;

Case (

_d = 3 ; status 1 ;

_d = 5 ; status 2 ;

"ect"

)

obviously insert your own status values and change the Case evaluations to what ever values make sense to the business process.

Then write a script that sets todaydate field for all appropriate records.

Best way is to set this up as a server scheduled script

Edited by Kris M
##### Share on other sites

5 hours ago, mlpremoli said:

The Get (Current Date) - Due Date (field) is not the right one, as I read in this forum, this function is not dynamic.

You just need to make the calculation field unstored. That is if I understand correctly what you mean by "not dynamic".

5 hours ago, mlpremoli said:

My question is, which would be my best approach?

That depends on your purpose. For displaying the status, you would probably want to use something like =

```Let (
age = Get (CurrentDate) - DueDate
;
Case (
age > 0 ; "Overdue" ;
age > -30 ; "Short Term Due" ;
"Long Term Due"
)
)```

However, for finding records of a certain status, you would be better off searching the DueDate field directly, since it can be indexed.

3 hours ago, Kris M said:

I would add a field to the table for todaydate

Whatever for?

5 hours ago, mlpremoli said:

I was thinking in setting up a Date Field in which I replace every single day the current date.

That's not a good idea, because (1) it will unnecessary modify all records in the table, rendering the last modified field unusable, (2) it will fail when a record is locked by another user and (3) it is entirely unnecessary.

Edited by comment
• 1
##### Share on other sites

6 hours ago, mlpremoli said:

I have a Database that deals with Bills and due dates

##### Share on other sites

If I understood correctly all your replied, two possible approaches to handle my question. First one using a dedicated "TodayDate" field which I would replace on a daily basis (not recommended by Comments) and another one using the Get (Current Date) in the calculation of the Tag, unstored which would refresh with no issues.  Searches should be based over DueDate field as this can be indexed.

4 hours ago, Lee Smith said:

I will as soon as I finish writing this post.

Thanks again and regards.

Mariano

##### Share on other sites

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

## Create an account

Register a new account