Use Get(CurrentDate) in a related table

[Angus Cameron]

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?

[Fitch]

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?

[Angus Cameron]

Thank you very much, that solves my problem. I have only recently graduated from fp5 and need to get used to the conditional relationship facility in fp7. I am sure that the same technique will help me elsewhere.