asked on

Match today's date with dates in NotesDocument field

Lotus Notes repeating calendar entries store their repeating dates in a field named RepeatInstanceDates.

With @Formula, I can use @IsMember to find if a value is in a list, but the members of RepeatInstanceDates are dates in the format 02/21/2011 05:15:00 PM CST, however I only want to match against the date portion, 02/21/2011.

Is there any way of doing this in the @Formula language?

ASKER CERTIFIED SOLUTION

Sjef Bosman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jlalande

ASKER

I've tried both and neither work for me.

If I already have the date in string format, is @Text(x; "D0S0") needed? If not, what format should the date string 'x' be in? I am assuming that D0S0 is a format argument.

Looking over this it looks like you are taking a list of dates and converting it to a list of strings in the format specified by 'D0S0', and checking if 'x' is in that list.

jlalande

ASKER

BTW, I am working in C# using the Domino COM Toolkit.

jlalande

ASKER

Actually, I think we're almost there. I replaced 'x' with @Today and now get matches. However, where I only have one repeating meeting today, I am getting four.

The other three are likely bookkeeping entries. Is there a way to filter these out?

jlalande

ASKER

Actually, there were two repeating calendar entries for today. I found a filter that works by checking if a field RepeatUnit exists using @IsAvailable. This returns a valid set of calendar entries for today.

My complete query string looks like:

Form = "Appointment" & Repeats = "1" & (AppointmentType = "3" | AppointmentType = "2") & @IsAvailable(RepeatUnit) & NoticeType != "C" & NoticeType != "R" & @IsMember(@Text(@Today; "D0S0"); @Text(RepeatInstanceDates; "D0S0"))

Can you see anything that could be done better or faster?

Thanks so much!

Sjef Bosman

Probably not much faster, but a little shorter:

Form = "Appointment" & Repeats = "1" & AppointmentType = "3":"2" & @IsAvailable(RepeatUnit) & !(NoticeType= "C":"R") & @IsMember(@Text(@Today; "D0S0"); @Text(RepeatInstanceDates; "D0S0"))

jlalande

ASKER

I adopted your changes and added support for additionally getting today's non-repeating calendar entries, thereby completing the solution to retrieve all of the meetings scheduled for today, repeating and non-repeating:

Form = "Appointment" & AppointmentType = "3":"2" & !(NoticeType = "C":"R")
& ((Repeats = "1" & @IsAvailable(RepeatUnit) & @IsMember(@Text(@Today; "D0S0"); @Text(RepeatInstanceDates; "D0S0")))
| (!@IsAvailable(Repeats) & @Text(@Today; "D0S0") = @Text(STARTDATETIME; "D0S0")))

Please let me know if I have missed anything.

You've been a great help. Thank you very much.

Sjef Bosman

Looks fine to me. You're doing an excellent job, sir!