@TextToTime("Today") in a view: change ViewSelectionFormula vs Tag/Flag a Document vs C-API Rebuild

hcl-bot · February 1, 2008, 8:32am

Dear All,I am a Lotus Notes Client and Web Developer from many years…

i have read many post and articles in this forum about the @TextToTime(“Today”) problem in a view selection formula.

I think i have make the correct approach in my applications, but i wish to ensure about some things.

My application is replicated on 4 server and contains many views (more than 80 views) with:

Current Year, Next Year, Last Year views
Some views with Montlhy Recently documents
Some views with Daily Recently documents
Views by EndDate

all of this views contains in the selection formula the option @TextToTime(“Today”).

The database contains >20.000 documents and will work also as historycal archive in the future.

So,

actually I use to flag/tab each document with a 2 scheduled daily agent that run at 1.30 am each day.

This 2 agent are enabled to run on each server replica and each agent flag/tab/modify only the document that has been created in the related replica, and that need a real updating in order to move document from a view to another view only if really necessary.

The replication task runs 4 time each day. (8.30 am, 12.30am, 16.30pm, 20.30pm)

I have tested this 2 scheduled agents and seems it works correctly and the rebuild index is performed for the EndDate views.

I have only some problem with a 3-Daily late view that seems not works correctly…i need each day to perform SHIFT+F9 in order to update the view.

the selection formula of the view is:

SELECT Form = “FA”:“FB”: “FC” & @Created >= @Adjust(@TextToTime(“Today”);0;0;-3;0;0;0)

the scheduled agent perform the following:

in agent document selection i have put:

In folder ‘All By Person 3 Daily’

in the agent formula i have put:

@If(

Form = “FA”:“FB”: “FC” &

@Created < @Adjust(@TextToTime(“Today”);0;0;-3;0;0;0) & RefreshedTRDailyFlag=“”

& SITE =SERVER_CREATION_SITE;

@Do(

@SetField (“RefreshedTRDaily”;@Now);

@SetField(“RefreshedTRDailyFlag”;“1”)

);

“”);

So, the agent execute a correct flag of the document day-by-day. The RefreshedTRDaily field is correctly set…BUT THE VIEW IN NOT REBUILD.

I wish to ask you the following:

Using that flag/tab approach wiht an Agent ALL MY VIEWS IN DATABASE MUST HAVE AN HIDE-COLUMN WITH THE FIELD THAT IS CHANGED IN THE RELATED AGENT ? (in the above case i shold create an hide-column with the field RefreshedTRDaily value)
I have notice that all view in my database have the following propertys:

Index - Refresh: Automatically

Index - Discard: If inactive for 45 days

You think that i need to change this setting to the default Lotus Notes values ?

Index - Refresh: Auto, after first use

Index - Discard: If inactive for 45 days

PS:

For info i wish to tell you that i think that for me it is very impossible to use the approach to change the ViewSelectionFormula in each view because i have more and more views in my database.

We cannot perform a daily change in the design viewselectionformula because i have also 4 replication db!!!

I also try to use C-API rebuild but i have many problems because the server is a SUN SOLARIS machine, and i have many problem with the related .dll files (.so files on sun solaris machine)

Thank you very much to all

excuse me for my English

Davide

hcl-bot · February 1, 2008, 8:48am

Subject: @TextToTime(“Today”) in a view: change ViewSelectionFormula vs Tag/Flag a Document vs C-API Rebuild

Change the view selection formula to eliminate the @TextToTime(“Today”):

SELECT Form = “FA”:“FB”: “FC” & RefreshTRDailyFlag = “”

Documents that are older than three days old will have a “1” in the RefreshTRDailyFlag field, so they will automatically be removed from the view.

hcl-bot · February 1, 2008, 9:47am

Subject: RE: @TextToTime(“Today”) in a view: change ViewSelectionFormula vs Tag/Flag a Document vs C-API Rebuild

Stan,this is also a good solution,

but i wish also to know why my approach does not works.

hcl-bot · February 1, 2008, 5:03pm

Subject: RE: @TextToTime(“Today”) in a view: change ViewSelectionFormula vs Tag/Flag a Document vs C-API Rebuild

Why do you have to press Shift+F9? In what way is the view incorrect? Is it displaying documents that should no longer be in there, or failing to display those that should?

Have you checked to make sure the agent is really modifying the documents?

Incidentally, I disagree with this approach. Modifying documents all the time is going to hurt performance and slow replication, as well as potentially cause save/replication conflicts. There are several alternative ways to approach this type of functionality; e.g. see Time/Date views in Notes: What are the options?

To which I would add, do you really need a view of everything > 3 days old? Why can’t you just have a view of everything, with the oldest stuff listed first? The user looks at the view and looks at the first entry; if it’s less than 3 days, no problem!

Another option that’s not mentioned there, is that you can write a scheduled agent to update the view selection formula to contain a hardcoded date. You can run it every night for views that change every day, or annually for views whose content changes annually, and so on.

hcl-bot · February 4, 2008, 3:19am

Subject: RE: @TextToTime(“Today”) in a view: change ViewSelectionFormula vs Tag/Flag a Document vs C-API Rebuild

Dear Andre Guirard,thanks for your response.

So, i can consider to remove the view 3 days old. The customer say to us only a veiw with Monthly Recently document. So, for this view i can use a different approach (eg: by flag document monthly as indicated by Stan Rogers)

But consider this,

a) today i have open my actual 3 days old view, and the background schedule agent have flag correctly the documents during this night 04-Feb-2008, so i have open my view and the view IS CORRECTLY INDEXED (the >=3 days old document are diappears)

b) i have another views, with the following selection formula:

Enddate >= @TextToTime(“Today”)

So, in this views the document are correctly flagged by another scheduled agent (this night of 04-Feb-2008), BUT IN THIS CASE THE VIEW IS NOT RE-INDEXEX/REBUILD.

(ps: this views is used in my application to show all Course documents with From-To date that expired day-by-day)

The options a) + b) return my actual and real situation in my application !

The only differences from a) and b) is the following:

In the views a) (Daily views) i have put an hide column with the field that is changed/refreshed by the sceduled agent.

In the view b) i have not hide column. Can this should be a problem ?

I Think that the real problem to use this approach is on the views options ? as indicated before in other post i have:

Index - Refresh: Automatically

Index - Discard: If inactive for 45 days

I Think also that sometime the server Rebuild the index after the scheduled agent modifications, situation a), and sometimes no rebuild is performed, situation b).

i tell you another thing,

now, if i force the scheduled agent to run on server (eg: Disalble and re-Enable the agent) with my LN client open, ALL VIEWS ARE RE-INDEX IF THE AGENT FLAG THE DOCUMENTS.

The problems of option a) and option b) are only when each day i go to work, turn-on my pc and open the related views in order to test what happened during the night.

Finally,

Andre,

i have at least 20 views that mus be refreshed daily. (views of the expired Course documents as mentioned in the situation b)

Do you think that every night i should change the selection formula of the views via LotusScript code ?

i try to use the following LS code for each view:

            ...

f(0)= v.SelectionFormula

a(0) = "Today"

b(0) = Cstr(Today)

td = Replace(f, a, b)

v.SelectionFormula =  td(0)

with this code i can chage daily the view selection formula from:

Enddate >= @TextToTime(“Today”)

To

Enddate >= @TextToTime(“04/02/2008”)

what you think about ?

Thank you very much

D.

hcl-bot · February 4, 2008, 9:07am

Subject: RE: @TextToTime(“Today”) in a view: change ViewSelectionFormula vs Tag/Flag a Document vs C-API Rebuild

Using an agent to set the selection formula to use a hardcoded date, makes it unnecessary to use @TextToTime. The whole point of using @TextToTime in the first place, is to trick the view indexing code into not realizing that you’re using @Today. If you update the selection formula, you’re not using @Today. Instead you should just put the date in square brackets, e.g. [4/2/2008]. So your formula might end up being:

SELECT Enddate >= [4/2/2008]

After the first time your code edits the selection formula, it will no longer contain the original value (“Today” in your example) so you can’t substitute for the original value. You have to substitute for whatever is inside of square brackets.

I don’t think having the indexing option set to “Automatic” is a problem. I think it’s the best choice for these views.

This article: Time/Date views in Notes: What are the options? discusses various ways to do what you’re trying to do.

This blog entry talks about another alternative.

You say when you change the agent it runs and “updates all the view indexes”. The agent doesn’t update view indexes. What’s really happening is that when you change the agent it updates the documents that need updating, and when it runs on schedule it doesn’t update those documents. That’s because the agent is set to run on modified documents, and the documents it needs to process have not been modified since the last time it ran.

hcl-bot · February 6, 2008, 7:13am

Subject: RE: @TextToTime(“Today”) in a view: change ViewSelectionFormula vs Tag/Flag a Document vs C-API Rebuild

Dear Andre,the agent is set as target: All documents in database.

however,

i have perform many tests…the solution to flag/tag documents with a scheduled agents seems works correctly.

for example, we can use the method to change the selection views formula for my Last Year/Current Year/Next Year views, but i should change this selection formula in all replicas (4 replicas) but for my company policy we cannot change the design elements in production environment. LN managers have scheduled on each server the replace design task from a template .ntf, and i cannot set the flag “Prohibit design refresh or replace to modify” on my application and my application template.

So, i think to let the approach with the scheduled agents that flag/tab the documents (only if really necessary)…i will not have the best database performance with the replication but i think and i hope that it will not be a problem.

i have just finish to talk with the server Administration and he tell me that on server there is a nightly task “updall” that runs each night.

which parameters should i use with “updall” task ?

hcl-bot · February 6, 2008, 10:00am

Subject: RE: @TextToTime(“Today”) in a view: change ViewSelectionFormula vs Tag/Flag a Document vs C-API Rebuild

If you read the article I posted a link to, you will discover the answer to your question about updall.

hcl-bot · February 6, 2008, 7:55am

Subject: RE: @TextToTime(“Today”) in a view: change ViewSelectionFormula vs Tag/Flag a Document vs C-API Rebuild

“… but i should change this selection formula in all replicas (4 replicas) but for my company policy we cannot change the design elements in production environment.”

The design change will replicate just as soon, as the modified documents would. The difference is, that this is only one (design) document to replicate.

Even if a formal company policy forbids you to change design elements in production (generally a wise thing), this shouldn’t hold you back from using the suggested approach: After all, it is NOT you modifying the view. It’s the signer of the agent, thus the same ID that signed the view when it was put in production.

It’s just up to you if you better argue about it with the responsible admin, or rather let it slip in silently …

“LN managers have scheduled on each server the replace design task from a template .ntf, and i cannot set the flag “Prohibit design refresh or replace to modify” on my application and my application template.”

Not much of a problem, just have the agent updating the view be scheduled after the design tasks runs.