What exactly is the "simple formula"?
Debra Singer wrote:
Group A can sell in 1 day. Group B in 9 days.
In 1 day/9 days from what?
There are two types of Customers: A and B. I can sell A's merchandise in 1 day after in comes in. I can sell B's merchandise after 9 days. So, I defined a field labeled: Ok to Sell. It is a calculation which evaluates the expression:
If Customer = A and Get ( Current Date) - Date >1 good to go. If Customer = B and Get (Current Date)- Date >9 good to go. What I noticed is that the field evaluated only on the day I created it.
I know there must be a script or Find that would allow me to evaluate for that information each DAy. I simply cannot put it to paper.
1. The calculation field needs to be set to unstored (in Storage Options...).
2. You don't need the calculation field in order to find "good to go" records. A script along the lines of:
Enter Find Mode
Set Field [ YourTable:: Customer ; "A" ]
Set Field [ YourTable:: Date ; "≥" & Get (CurrentDate) - 1 ]
Set Field [ YourTable:: Customer ; "B" ]
Set Field [ YourTable:: Date ; "≥" & Get (CurrentDate) - 9 ]
Perform Find 
can do it.
See, I knew it was something so simple. Can you explain a bit more in detail how the storage option works. If I set the calculation field to "unstored" what takes place? Does it recalculate only when the database is opened? Does it recalculate when Get(Current Date) changes each day?
It recalculates "as needed". For example, if you place it on a layout, it will recalculate every time the window is refreshed.
I have tried to use the script you suggest. I can find all of the fields with "A". The next part for the difference in dates does not work for me. My Date field is a date. The find field is looking for a date and it seems to be returning a number.
I will keep at it.
Debra Singer wrote:
The find field is looking for a date and it seems to be returning a number.
I don't understand what you mean by that.
Thank you for your patience. When I enter this: Set Field [ YourTable:: Date ; "≥" & Get (CurrentDate) - 1 ] into the script it tells me there are no matching records.
So I will ask my questions and/or you can explain what this step is doing. 1. I assume we are placing a find criteria in the field:Date. 2. that criteria should look for records that have the date which is the current date less 1. So in English all dates from yesterday back. 3. I even tried setting the script with just that line to no avail.
Hope that helps,
I suggest you put a pause before Perform Find and examine your requests.
I don't know where my typo was but each customer find works great now. My next task would be to be able to find the combined set. When I started out that was the reason for a separate field which looked at each record and said good to go or not. For the time being, changing the storage as you suggested seems to be working.
I would like to learn more about the scripted finds and am happy to try any and all of your suggestions. In your original post it included a second New Request which had the second customer. That did not return any more records. Again, I will continue to tinker.
Thank you very much for your prompt response,