Avatar of fatihdi
fatihdi asked on

I want to find the forms in a view that has the containing govacationdate and returndate in ease.

We have a view and in that view people mistakenly create duplicate forms or forms that has containing dates of godate and comedate like

form1 :
godate:11 August 2011 -
comedate:16 August 2011

godate:12 August 2011 -
comedate:15 August 2011

I want to go through all the forms in the view and find the forms that has this and color it to yellow or smthg ,or may be delete it.
Lotus IBMDatabases

Avatar of undefined
Last Comment
Sjef Bosman

8/22/2022 - Mon

As you can see above that the dates are containg each other so this is no sense. If somebody will go on a vacation on 11 august 2011 and in the second form is saying will go on a vacation on 12 August, one of the forms is definetely wrong so i want to find out this.

To do that, you'd have to stamp the overlapping documents, risking replication/save conflicts.
Have you considered using a folder (I'm not fond of private views/folders, but there aren't a lot of options here - make one Shared, private on first use).
When you trigger your check, you'd first need to make sure that the folder is empty (remove all documents from that folder).
Then, you could build a search and put all the searchresults in that folder. With a few of assumptions:
  Set dtStart = New NotesDateTime(doc.GetItemvalue("GoVacationDate")(0))
  Set dtEnd = New NotesDateTime(doc.GetItemvalue("ReturnDate")(0))
  strQry = {Form="frmLeave" & Person=@Username & ((GoVacationDate>=[} & dtStart.DateOnly & {] & GoVacationDate<=[} & dtEnd.DateOnly & {]) | (ReturnDate>=[} & dtStart.DateOnly & {] & ReturnDate<=[} & dtEnd.DateOnly & {]))}
  coll = db.search(strQry, Nothing, 0)
Last step: present the folder.

@Crak -thank you for your comment..

What I want to ask here is

1- I should get the doc in the database with doc.GetNextDocument while doc will be nothing ,is that right?

2- In the query, Person=@Username what is this equation exactly? Is person , doc.Name(0)? why should I equate it to the @Username ? I am confused in here, Person is the name field on the form? so then isnot that supposed to be Person=doc.Name(0) ?

3- Should I put this code in an agent and how do i transfer the search results in a folder in lotusscript can you help me with that as well?

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes

The code is just a fragment of what you'd need. It was intended to get you started.

I think that your intention is to see if someone is making overlapping leave requests. For this purpose you're not interested in person X's requests, when person Y applies for a day off. That's why I added the @Username check. It is important to realise that the search (lotusscript) is running a selection formula in @functions (formula looks like a view secection formula, except "Select" itself). Person=@Username would be interpreted as {field "Person" equals the name of the user that is running the script}. You may have to add an @Name-conversion to @Username to match what you've stored in the Person field. Instead of using @UserName, you could also use Person=doc.Name(0) as you stated, if Name contains the name of the user. hat's regarding your 2nd question.

Regarding the 1st: doc would be a reference to the current document; the one on which the user is filling out a new request. Through doc we can obtain the date fields that were filled out.
I haven't pointed out how to assign a value to doc (assuming you'd understand). Most likely you'd apply the code in QuerySave, where you have Source (As NotesUIDocument) already available. doc would be Source' back-end: Source.document.
If doc remains Nothing, the code would fail since the dates can not be retrieved.
In the current example you should have no need to use .GetNextDocument anywhere: the search would hand you a NotesDocumentCollection. If the new request has overlapping requests (there is a catch here: when editing an existing request, the document could find itself!) the collection would contain one or more documents. If there are no overlapping documents, the collection would be empty. That brings me to your 3rd question: the entire collection can be moved to the folder using coll.PutAllInFolder( folderName$ [, createonfail ] ).

The 1st part of your 3rd question depends on your needs. I had QuerySave in mind, doing a check for a single document for a single user.
If you have an agent to run a full check on all documents, then a coupe of things should be handled differently:
-You couldn't use private folders (but in that case a public one would do just as good)
-In this folder it may not be clear which requests cause conflicts
-You'd need to look into all requests, and do a search based on each one of them (in question #1 that would mean yes: Set doc=vw.GetFirstDocument, while not (doc is Nothing) and Set doc=vw.GetNextDoc(doc))


My intention is not dealing with the forms that is being created but make a whole database check.. document by document and if find out duplicates move them to a folder as you mentioned... To achieve this.. I have written the code below in an agent as you mentioned but unfortunately It does not retrieve date information they all come

0.0.0000 <-- in this format

and in the strQry there is a problem... i find to find the overlapping forms

based on form type,name and dates, if this gives me the suspision of overlapping then I would want to move them....

Can you help me on the code and in building the search string?

	Agent CleanUp
	Created Jul 13, 2011 by Fatih DIKILI/TOYOTA
	Description: Comments for Agent
Option Public
Option Declare

Sub Initialize
	Dim s As New NotesSession
	Dim db As NotesDatabase
	Dim coll	As NotesDocumentCollection
	Dim view As NotesView
	Dim doc	As NotesDocument
	Dim dtStart As NotesDateTime
	Dim dtEnd As NotesDateTime
	Dim strQry As String
	Set db=s.Currentdatabase
	Set view=db.Getview("IzinGenelIK")
	Set doc=view.Getfirstdocument()
	Do Until doc Is Nothing
		Set dtStart = New NotesDateTime(doc.GetItemvalue("IzinBasTar1")(0))
		Set dtEnd = New NotesDateTime(doc.GetItemvalue("IzinDonTar1")(0))
		strQry = {Form="YillikIzin" & GirisiYapan=doc.AdSoyad(0) & ((IzinBasTar1>=[} & dtStart.DateOnly & {] & IzinBasTar1<=[} & dtEnd.DateOnly & {]) | (IzinDonTar1>=[} & dtStart.DateOnly & {] & IzinDonTar1<=[} & dtEnd.DateOnly & {]))}
	    Set	coll = db.search(strQry, Nothing, 0)
		Call coll.Putallinfolder("Duplicates",TRUE)
	    Set doc=view.Getnextdocument(doc)

End Sub

Open in new window

Sjef Bosman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

line 27 & 28 is not filled with the correct dates,

there is a problem with the query string

(a field in the form) GirisiYapan=doc.AdSoyad(0) (name field in the document) is meant for

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

@sjef-bosman thanks for suggestion but example below should not alert but in your case it will alert

previous document=  godate:12 August 2011  comedate: 15 August 2011
current document=    godate: 10 August 2011 comedate: 11 August 2011

these two entries do not overlap and should not cause an alert????but in your case

Sjef Bosman

No it won't.
Sjef Bosman

Because the situation you describe cannot happen... :-)
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

previous.comedate>=current.godate AND previous.godate<=current.comedate

above statement will give me what I want actually for this.
Sjef Bosman

That seems unnecessary to me. If you have your view sorted on godate, you know for a fact that
so, since by definition (I hope)
it is always so that

What you have to define is whether there is an overlap in the dates for these two periods:
      A:  22.7.2011 - 23.7.2011
      B:  23.7.2011 - 25.7.2011
so where A.comedate is equal to B.godate

Anyway, thanks for the grade!