10 Replies Latest reply on Jul 24, 2009 2:26 PM by singerdf

# Calculation field needs to update with each new day

### Title

Calculation field needs to update with each new day

### Post

I have a calculation field that evaluates whether merchandise can be sold based on a simple formula.  I do not know how to have the field recalculate for each new calendar day.

Any help would be great.

Specifics:  Group A can sell in 1 day.  Group B in 9 days.  I would like to run a report each morning telling me what is ready for sale.

Debbie

• ###### 1. Re: Calculation field needs to update with each new day

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?

• ###### 2. Re: Calculation field needs to update with each new day

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.

Debbie

• ###### 3. Re: Calculation field needs to update with each new day
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 ModeSet Field [ YourTable:: Customer ; "A" ]Set Field [ YourTable:: Date ; "≥" & Get (CurrentDate) - 1 ]New RequestSet Field [ YourTable:: Customer ; "B" ]Set Field [ YourTable:: Date ; "≥" & Get (CurrentDate) - 9 ]Perform Find [] can do it.`

• ###### 4. Re: Calculation field needs to update with each new day

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?

Thank you,

Debbie

• ###### 5. Re: Calculation field needs to update with each new day
It recalculates "as needed". For example, if you place it on a layout, it will recalculate every time the window is refreshed.
• ###### 6. Re: Calculation field needs to update with each new day

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.

Debbie

• ###### 7. Re: Calculation field needs to update with each new day

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.

• ###### 8. Re: Calculation field needs to update with each new day

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,

Debbie

• ###### 9. Re: Calculation field needs to update with each new day
I suggest you put a pause before Perform Find and examine your requests.
• ###### 10. Re: Calculation field needs to update with each new day

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,

Debbie