Match today's date with dates in NotesDocument field

Posted on 2011-02-24
Medium Priority
Last Modified: 2013-12-18
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?
Question by:jlalande
  • 5
  • 3
LVL 46

Accepted Solution

Sjef Bosman earned 2000 total points
ID: 34975839
I can't check right now, but I suppose the field RepeatInstanceDates contains a date value, not a string. Let's also assume your date x is a date value.

To check whether date x is in the field, try this condition (can be used in the query):
      @Text(x; "D0S0")=@Text(RepeatInstanceDates; "D0S0")
which is shorthand for
      @IsMember(@Text(x; "D0S0"); @Text(RepeatInstanceDates; "D0S0"))

Author Comment

ID: 34976210
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.

Author Comment

ID: 34976292
BTW, I am working in C# using the Domino COM Toolkit.
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.


Author Comment

ID: 34976449
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?

Author Closing Comment

ID: 34976634
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!
LVL 46

Expert Comment

by:Sjef Bosman
ID: 34977748
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"))

Author Comment

ID: 34980328
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.
LVL 46

Expert Comment

by:Sjef Bosman
ID: 34980634
Looks fine to me. You're doing an excellent job, sir!

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Watch the video to know how one can repair corrupt Exchange OST file effortlessly and convert OST emails to MS Outlook PST file format by using Kernel for OST to PST converter tool. It can convert OST to MSG, MBOX, EML to access them. It can migrate…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question