Jump to content

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?

Thanks in advance for your answer.

Regards

Mariano.

Link to comment
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
Link to comment
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
  • Like 1
Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

This topic is 2384 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.