Problem refreshing values in a view

HCL Domino Domino Forum
1

Hello, I have a view column using the fllowing formula to display the number of days between two dates:

@If(Date_In_Stock!=“”;(Date_In_Stock-@Adjust(Date_From_Stock;0;0;15;0;0;0))/86400;(@TextToTime(“Today”)-@Adjust(Date_From_Stock;0;0;15;0;0;0))/86400)

Date_From_Stock and Date_In_Stock are editable Date/Time (Notes Format) fields.

It seemed to work fine during the tests, but in real life I realised that the values in the column are not updated. In order to be displayed the correct number of days between Today and The Day_From_Stock for example I have to Edit/Save the document. Even the forced refresh of the view does not help:

Sub Queryopen(Source As Notesuiview, Continue As Variant)

'Force a refresh

Dim view As Notesview

Set view = source.view

Call view.Refresh

End Sub

Do you have any Idea why the number f days in the field is not refreshed?

Thanks in advance.

George Nazarov

2

Subject: Additional unrelated thought

@If(Date_In_Stock!=“”;(Date_In_Stock-@Adjust(Date_From_Stock;0;0;15;0;0;0))/86400;(@TextToTime(“Today”)-@Adjust(Date_From_Stock;0;0;15;0;0;0))/86400)

Not a part of your problem but I noticed you don’t check to see if Date_From_Stock is set? I guessing this is a required field on your form and probably cannot be save unless set? Otherwise you could have a problem if that value is not set.

3

Subject: Problem refreshing values in a view

Not an area I’m consider myself strong, but it sounds like the view index is not updating for some reason.

As this view seems to have a lot of overhead each time someone opens the view, have you thought about having an agent do the calculation each night after midnight so the view could just display the results vs having to do the calculation everytime someone opens it?

4

Subject: Problem refreshing values in a view

Not sure if this will make a difference but try replacing ‘@TextToTime(“Today”)’ with ‘@Today’ instead.

Also note that any column or view selection formulas which have @Today or @Now in them will greatly impact performance as the server needs to refresh the entire view each time it’s opened by any & every user.

Alex

5

Subject: RE: Problem refreshing values in a view

That’s the reason to avoid @Today and @Now. The company policy does not allo this formulas to be used in a view.

Actually even that does not help :(((

6

Subject: RE: Problem refreshing values in a view

Um, that would help – if it doesn’t work for you I’m not sure why. It would produce poor performance in that view.In general, when you have a view index, the index is stored on disk. When you use the view, Notes only looks at documents that have changed since the view was last used, to see whether they belong in the view and to recalculate their column values. If the document has not changed, Notes doesn’t bother to re-evaluate the column formulas, but uses the stored values.

If you use @TextToTime(“@Today”), you are fooling the view indexer into not recalculating values that might be out of date.

There are several different approaches to making something like this work:

Use @Today without @TextToTime and accept the performance hit.

Use @TextToTime, but do something to discard the view index every day at midnight, so that the first person who opens it the next morning has to wait while the data are recalculated. There’s a server console command to do this, and you can talk to your admins about setting up such a command to run. Problem is, this has to be done for each replica, and it’s hard to arrange for local replicas.

Hard-code the date into the view design, and add a daily agent that updates the view design to contain the correct date in its formulas.

Write an agent to run nightly and update documents to contain the desired column value in a field (this is generally a better option for things that only change once; you don’t want to modify every document every night). You would also need a computed field in the documents, so that documents edited during the day would show up in the view.

Don’t use a view; use a folder, and have a nightly agent (or view queryopen code) to update the folder contents. This is a bit more of a challenge if documents might be added during the day because, of course, the view doesn’t update itself automatically; that’s where the queryopen code comes in.

In cases where you want a view selection of one day’s documents, you can have a view categorized by date and display only today’s category.

Not all these techniques are useful in all cases.

7

Subject: Problem refreshing values in a view - Updated

Thanks for the valuable information and for the perfectly strucrured answer. Based on that and the other posts on my question I’ve decided to create a computed field Day_Plus in the form. The field is updated on every document change. The field code is:@If(Date_In_Stock!=“”;(Date_In_Stock-@Adjust(Date_From_Stock;0;0;15;0;0;0))/86400;(@TextToTime(“Today”)-@Adjust(Date_From_Stock;0;0;15;0;0;0))/86400)

Then I have created an agent that updates the Day_Plus field on a selection of documents ( uses ‘Part Record’ form AND field STEP is less than 9 (means that Date_In_Stock is empty))

Here comes the problem. The agent works only the first time after saving a change into the code. I can execute the agent and it works fine. The updated values appear in the vew. Then I shift the date with one day (using the windows clock settings) and run the agent again. None of the documents is updated this time.

The behaviour is very unstable. Sometimes, if I run the agent second time, it works. Sometimes not.

If I turn the date back one day, the agent calculates the date as if it was running the next day.

I have n Idea how to continue.

Please help.

8

Subject: RE: Problem refreshing values in a view - Updated

Messing with your clock settings is kind of iffy, because Notes records date/time values all over the place of the last time things happened, so if you set the clock back, suddenly all these recorded times are in the future and things begin working oddly.
You’re sure the agent is set to run on all documents, not modified documents or selected docs?

Agents are really pretty predictable. They aren’t going to do something different every time you run them, except insofar as the agent changing the data might be making a change that would affect the next iteration. Look carefully at one of the documents you think the agent should be changing and step thru the agent in your head, figuring out the values of the expressions.

Also note there’s a way to test run an agent to see how many documents match its selection criteria. This is a good way to distinguish an agent that fails because it’s not finding any documents to work on, and an agent that finds documents but doesn’t do anything to them.

9

Subject: RE: Problem refreshing values in a view - Updated

I Think that finally it worked. I’m testing it for a week already and it works according to plan :slight_smile:

Thanks a lot for the valuable support. It’s a good feeling to know that you can rely on the forum.

10

Subject: Problem refreshing values in a view

All a refresh does is look update the view index for documents that were changed since the last refresh. It does not recalculate every value in the view - for that, you need to Rebuild the view, not refresh it.

Notes has this concept that if a document has not changed since the view index was built, then the documents column values have not changed. The only way they could have changed is if you have a @Now or @Today in the formula, which is considered a special case.

That, of course, has a large penalty, as all values need to be computed each time they are displayed.

The correct method to do that would be to move the formula into an agent that would update a field on each document each night, and then show that field in the view.