Comparing a date vs. timeframe

HCL Domino Domino Forum
1

I have a database which categorizes records into views based on a field called planned_date and that field’s value when compared to today’s date. One view lists all items planned for this week, and two other views show items planned for last week and also for next week. A “flag” field is set up for each time period to indicate what view the record should be in.

The week starts on Sunday and ends on Saturday, so if today is Tuesday and the planned_date value is 3 days ago, the last_week flag would be set to “1”, and the other two flag fields would be set to “0”. The record would appear in the view containing items planned for last week. If today is Friday and the planned_date is 3 days ago, the this_week flag would be set to “1” and the other two fields set to “0”.

I’ve been able to do this using @adjust and @if statements in the flag fields themselves, but this requires manually refreshing the forms each morning. I’d like to write a script that will loop through all documents in the “Recent” view (contains all records with plan_date within 14 days +/-) and set these three flag fields during a scheduled agent, but i’m not sure how best to approach it.

Any ideas?

2

Subject: RE: Comparing a date vs. timeframe…

The fact that you have a “Recent” view and think you can use it as input to your process, makes me think you’re using @Today in the selection criteria. If you’re going to update all the documents anyway, you might as well make this view an output of your process, along with the other views you were talking about.

The suggestion to use db.Search is not very efficient in this case; you would do better scanning down a view that contains all documents sorted by the date field, and stopping when you came to one out of your range. You could also use a full-text search (FTSearch method) to find the documents that need updating, provided there are not too many of them.

However, I’m actually more of a fan of updating the view selection criteria rather than updating the documents. If there are a lot of documents modified it can take a long while for people with local replicas to get the changes, and there’s a view indexing penalty affecting all the other views that don’t care whether the date is recent. Use the NotesView.SelectionFormula method and you can hard-code in the relevant date ranges to select the documents you want – updating those date ranges daily. This is very quick to replicate and other views don’t need re-indexing from it.

I also would like to draw your attention to the StampAll method, in case you do decide to update documents after all.

  • Andre Guirard, IBM/Lotus Development

Useful blog: Best Practice Makes Perfect

For faster answers, be C R I S P Y

3

Subject: Comparing a date vs. timeframe…

You could build a collection of NotesDocuments based on a NotesDatabase.Search. Then loop through the result and call NotesDocument.ReplaceItemValue to set the new values. Don’t forget to save each modified document before going to the next one.

4

Subject: RE: Comparing a date vs. timeframe…

Thanks Rob…I don’t even really need a search, I can simply loop through all docs in the view, I’m just stuck on how to code the comparison of Today vs. Planned_date and setting the flags depending on what day of the week it is and how many days before or after the field value is compared to today.

For example in order to flag a record to identify it as last week, the range could be up to 13 days:

If planned_date is the first day of last week (Sunday) and today is the last day of this week (Saturday), the span is 13 days. But if today is Sunday and planned_date is yesterday (Saturday), there is only 1 day difference, but the record is still flagged as last week. I’m not sure how to code this in script…

5

Subject: RE: Comparing a date vs. timeframe…

What is it you are not sure of? Are you not sure of the algorithm or not sure what LS commands to use?

6

Subject: RE: Comparing a date vs. timeframe…

Pretty much both…here’s what I’m using in the Last_Week flag field input translation formula:

tmp := @Today;

pre1 := @Adjust(tmp;0;0;-1;0;0;0);

pre2 := @Adjust(tmp;0;0;-2;0;0;0);

pre3 := @Adjust(tmp;0;0;-3;0;0;0);

pre4 := @Adjust(tmp;0;0;-4;0;0;0);

pre5 := @Adjust(tmp;0;0;-5;0;0;0);

pre6 := @Adjust(tmp;0;0;-6;0;0;0);

pre7 := @Adjust(tmp;0;0;-7;0;0;0);

pre8 := @Adjust(tmp;0;0;-8;0;0;0);

pre9 := @Adjust(tmp;0;0;-9;0;0;0);

pre10 := @Adjust(tmp;0;0;-10;0;0;0);

pre11:= @Adjust(tmp;0;0;-11;0;0;0);

pre12 := @Adjust(tmp;0;0;-12;0;0;0);

@If(

@Weekday(tmp) = 1 & Planned_Date >= pre6 & Planned_Date <= tmp;“1”;

@Weekday(tmp) = 2 & Planned_Date >= pre7 & Planned_Date <= pre1;“1”;

@Weekday(tmp) = 3 & Planned_Date >= pre8 & Planned_Date <= pre2;“1”;

@Weekday(tmp) = 4 & Planned_Date >= pre9 & Planned_Date <= pre3;“1”;

@Weekday(tmp) = 5 & Planned_Date >= pre10 & Planned_Date <= pre4 ;“1”;

@Weekday(tmp) = 6 & Planned_Date >= pre11& Planned_Date <= pre5 ;“1”;

@Weekday(tmp) = 7 & Planned_Date >= pre12 & Planned_Date <= pre6;“1”;“0”)

I can easily set up a script to loop through all documents in the view, but the logic needed to set all three flag fields is what I’m not sure about.

7

Subject: RE: Comparing a date vs. timeframe…

This function will return a string indicating the related week. I think that you can work out by yourself how to use it and set the relevant flag in your document.

Function CalcPeriod(PlanDT As NotesDateTime) As String

Dim ThisWeekSunday As NotesDateTime

Dim LastWeekSunday As NotesDateTime

Dim NextWeekSunday As NotesDateTime

Dim i1 As Long

Dim i2 As Long

Dim i3 As Long

Dim MinutesDay As Long

Dim msg As String

MinutesDay = 86400 '=24 * 60 * 60

Set ThisWeekSunday = New NotesDateTime(Now)

ThisWeekSunday.AdjustDay(-1 * Weekday(Now) + 1)

Set LastWeekSunday = New NotesDateTimeThisWeekSunday.DateOnly)

LastWeekSunday.AdjustDay(-7)

Set NextWeekSunday = New NotesDateTime(ThisWeekSunday.DateOnly)

NextWeekSunday.AdjustDay(7)

i1 = (PlanDT.TimeDifference(LastWeekSunday) / MinutesDay)

i2 = (PlanDT.TimeDifference(ThisWeekSunday) / MinutesDay)

i3 = (PlanDT.TimeDifference(NextWeekSunday) / MinutesDay)

If i1 >= 0 And i1 <= 6 Then

msg = “last week”

Elseif i2 >= 0 And i2 <= 6 Then

msg = “this week”

Elseif i3 >= 0 And i3 <= 6 Then

msg = “next week”

End If

CalcPeriod = msg

End Function

8

Subject: RE: Comparing a date vs. timeframe…

today := @Today;thisSunday := @Adjust(today; 0; 0; 1 - @Weekday(today); 0; 0; 0);

last Sunday := @Adjust(thisSunday; 0; 0; -7; 0; 0; 0);

nextSunday := @Adjust(thisSunday; 0; 0; 7; 0; 0; 0);

trimmedPlannedDate := @Date(planned_date);

plannedSunday := @Adjust(trimmedPlannedDate; 0; 0; 1 - @Weekday(trimmedPlannedDate); 0; 0; 0);

FIELD this_week := @If(plannedSunday = thisSunday; “1”; “0”);

FIELD last_week := @If(plannedSunday = lastSunday; “1”; “0”);

FIELD next_week := @If(plannedSunday = nextSunday; “1”; “0”);

“”

That last line does nothing, but it does fulfill the requirement that the last line be a “main or selection formula”. You’ll want to use the agent’s Search property rather than a selection formula.

9

Subject: RE: Comparing a date vs. timeframe…

Thanks guys…this approach works great! Found a little bug in the plannedsunday := line…added the extra placeolder for month and it’s all good. Appreciate your help!

10

Subject: RE: Comparing a date vs. timeframe…

Thanks – I fixed my posting in case anyone else needs something similar.