Jump to content

Use Get(CurrentDate) in a related table


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

Recommended Posts

I have a manufacturing inventory database that stores usage and purchase quantities on random Transaction Dates up to a year from now. I have a series of calculation fields which allow me to determine on what date for a given Stock Code the quantity in inventory turns negative, that is usage exceeds purchases. Because the calculation is quite processor intensive, but I need it to be real time data, I am trying to limit the calculated transactions to only those within the next 90 days. To do so I have an indexed field "Stock Code Date", based on the Transaction Date, which contains the Stock Code for transactions up to 90 days away but becomes blank thereafter. As a result the relationship that I have created between the Master Inventory Stock Code Record and "Stock Code Date" only identifies near term records and ignores those more than 90 days away.

My problem is that for the relationship to work the "Stock Code Date" must be indexed. But an indexed field does not update based on a change in the current date. I don't want to have to manually un-index and re-index the field every day. Is there a way in which I can automate this process, please?

Link to comment
Share on other sites

How about putting the current date + 90 into a global field in the parent table, and creating/modifying a relationship to add the criteria Stock Code Date < global date?

Link to comment
Share on other sites

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