I've tried searching the forums and the help file for an answer to this but haven't found one.

I have a script which I use to calculate the age of an unpaid invoice that includes the formula:

DaysOpen = Get(CurrentDate) - InvoiceDate

None of the data is Stored

It works fine, except that the number of days doesn't update accurately if the file has been open for more than a day. If I close the file, reopen it and run the script, the number of days changes. Is there some way to force the CurrentDate to update without closing the file, or is there a better way to format the calculation?

Thanks.

You can try "refresh window" script step just after your script step.

I've tried "refresh window" as a menu command but not as part of the script. I'll add it and see what happens, but I don't know of a way to test it until tomorrow when I can see if the number of days open changes from today. Thanks.

Edited September 17, 201015 yr by Guest

"DaysOpen = Get(CurrentDate) - InvoiceDate

None of the data is Stored"

Surely, InvoiceDate is stored?

InvoiceDate is entered manually, and not part of a calculation, so there's no storage option that I'm aware of.

What do you mean, "InvoiceDate" is not part of the calculation?

Perhaps you should post your file or a screen shot of the calc.

I said InvoiceDate is not part of "a" calculation, not "the" calculation. In other words, it's just a field that I use to enter the date I'm sending the invoice. I still use this InvoiceDate field as part of the other calculation shown above.

See if this helps:

refresh.zip

Thanks. I see how that works in your example.

Would using the button be any different than adding the "refresh window" as part of a script? Since the script also finds only the open invoices and sorts them, I still need to run the script.

And what's the difference between using the button, and the command in the pull down menu? I've tried using it and it didn't make any changes to the number of days.

Would using the button be any different than adding the "refresh window" as part of a script?

No. But if the script finds or sorts records, the window will be refreshed anyway.

what's the difference between using the button, and the command in the pull down menu?

What command do you mean?

But the window doesn't update after running the script and that's the problem. If the file has been open for two or three days, or longer, running the script doesn't change the number of days. Only closing the file; opening it and running the script will update the number.

The refresh window command I'm referring to is under the Records Menu.

Let's go back to the beginning: do you have a CALCULATION field for the invoice age? If yes, are you sure it's unstored (under Storage Options… - see my demo)?

AFAIK, the Refresh Window command does the same as the script steps (the script step has additional options, though).

Here's the original calculation:

DaysOpen = Get(CurrentDate) - InvoiceDate

DaysOpen is the calculation field

Get(CurrentDate)-InvoiceDate is the calculation

I checked the Storage options for both Get(CurrentDate) and DaysOpen, and neither one is stored.

I tried an experiment. I duplicated a record and changed the invoice date. The number in the DaysOpen field changed to reflect the new date. But if I run the script tomorrow, at least if it acts the same as previously, the number of days open will be the same as today, as if the CurrentDate field doesn't recognize that it's a day later and isn't following the date of the computer. On the other hand, I have an auto enter Date field that enters a date when a new record is created and it has always worked as it should.

It's hard to test this without changing the computer's date, something that I don't want to do, but I will see what happens tomorrow with the refresh window step.

Something doesn't fit in your description. Why don't you post a copy of your file (preferably stripped down to the bare essentials)?

Oops.

I created two new files so that I could test different methods of updating the number of days and left them open overnight, as well as the original. When I checked the files this morning, both of the new ones had automatically updated the number of days open, without the need to run any scripts. That was a big surprise. The original file hadn't changed, and even the refresh window didn't update anything.

After digging a little deeper I discovered my mistake. Even though I had checked the original file twice, I had accidentally selected None for Indexing, instead of Do Not Store for a Storage option. I don't recall ever doing any calculations of this type in the past, so this option is new to me and I'll have to be more careful in the future. Sorry for the confusion.

Edited September 18, 201015 yr by Guest
Problem resolved

This is why posting a file is preferable. It's hard to see errors in your own work.