Setting Up a Daily Calculation

[mlpremoli]

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?

Thanks in advance for your answer.

Regards

Mariano.

[Kris M]

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 December 19, 2015 by Kris M

[comment]

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 December 19, 2015 by comment

[Lee Smith]

6 hours ago, mlpremoli said:

I have a Database that deals with Bills and due dates

Please update your profile to reflect your current version of FileMaker Pro.

[mlpremoli]

Thanks all for you answer.

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:

Please update your profile to reflect your current version of FileMaker Pro.

I will as soon as I finish writing this post.

Thanks again and regards.

Mariano