Lotus View and Date values in column formula

1
votes

Hi(sorry for possible dublicate).

I am trying to fix the performance issues in some of the legacy commercial databases which were badly designed in the past.

There are a lot of views in this databases and they are all containing the formulas @Now, @Today values in the view columns in the inner formulas, which are increasing Lotus Views open/refresh speed (as a notice, there is no dates manipulations in the view main selection formulas).

Information in this databases must to be always up to date and refreshed, so the solution with changing the view options (refresh time interval) are not good enough.

I am trying to create a global document (or to find similar solution), which will be updated one time at night with the server agent and will contains fields with year,month and day and trying to add this document as a subform to every document, so this fields with year e.t.c will be accessible in every document and so I could call them in my future column @-formulas and every day this field in this subform will be updated.

Is it really possible/right solution? How can I do that? Can you please give some advice what is the best way to solve this?

p.s. I have already read a ton of articles about Lotus Views performance issues when using dates and mostly all of them are about creating a server agent, which will update the selection formulas one time per day... but in my variant - I am having a formulas in the columns.

Thank you.

UPDATED 03.03.2018:

Thanks to Knut Herrmann, I have understood that I need to use an agent to update column formulas and that this is possible. But I am also trying to understand why the view will work faster after the view formula will be updated? Not quite understand this solution. Why it has no need to update view index every time after one-time formula is updated at the night and why it's refreshing every time now when I am not using this approach?

2
lotus-noteslotuslotus-formula

2 Answers

1
votes

Use the agent approach.

Change the column formula with NotesViewColumn's Formula property every night:

Dim column as NotesViewColumn
set column = ...
column.Formula = "your column formula"

The idea behind the agent is to avoid @Today in column formulas as this function causes the view to refresh every time it is called.

Example:

Every night at 2 a.m. change column formula (to show the number of days left)

(@Date(Deadline) - @Today) / 86400

to 

(@Date(Deadline) - @Date(2018; 03; 04)) / 86400

This way you eliminate the "dangerous" function "@Today" by replacing it with the current date in formula.

1
votes

Another option would be to add a field to all the documents using the NotesDocumentCollection StampAll() method in a scheduled agent. For example:

    dim db as NotesDatabase
    dim dc as NotesDocumentCollection
    dim todayDate as new NotesDateTime("")
    set dc = db.alldocuments() ' Or whatever selection of documents you want.
    set todayDate.localtime = format(Now(),"mm/dd/yyyy")
    call dc.stampall("TodayDate",todayDate)