Hot Water - Need help. I’m creating a report using LS. I need to get only those documents (from child) where the field does NOT match the parent doc field. example: contact doc is parent (has field CONTACTFULLNAME); activity doc is child (has field ACTIVITYCONTACTFULLNAME). What would my syntax be? I tried: | NOT FIELD ACTIVITYCONTACTFULLNAME CONTAINS CONTACTFULLNAME | That doesn’t work…I tried Evaluate statement and can’t figure out to work that either…HELP!
Subject: full text query syntax
I think you are on the right track with
NOT FIELD ACTIVITYCONTACTFULLNAME CONTAINS CONTACTFULLNAME
But you can’t specify “CONTACTFULLNAME” as that is a field name, and its going to look for the literal string “CONTACTFULLNAME” … You’ll have to specify the actual text … so something like this:
’ assumes document is the parent document
’ assumes cdb is the current database
Dim SearchStr as String
Dim i as NotesItem
set i = document.GetFirstItem(“CONTACTFULLNAME”)
SearchStr = SearchStr + "NOT FIELD " + i.Name + " CONTAINS " + i.Text
Dim resps as NotesDocumentCollection
Set resps = document.responses
Call cdb.UpdateFTIndex(True)
DIm ResultX as NotesDocumentCollection
Set ResultX = resps.FTSearch(SearchStr, 0, 8, 16384 + 512)
Just coded that from memory basically, so no testing, but it should put you on the right path…
jeremy
Subject: RE: full text query syntax
Hi, Jeremy. thanks for response. However, that is one of my probs…I don’t know what the text will be…there are thousands of contactfullnames. I just want to get the ones from the child that don’t match up. Any other ideas? I’m stressin’
Subject: RE: full text query syntax
So, do I understand this correctly? You have the parent document, right? And you know the value on the parent, and you want to get the responses that have a a field, whose contents do not match the value (which you know) in the parent ?
If so, It doesn’t matter that you don’t know what the value on the child is, you only care to select those that don’t match … So you get the parent, get all the responses, and then filter the collection of responses using FTSearch, where the value of the field on the child doesn’t match the value on the parent …
Or am i missing what you are asking?
jeremy
Subject: RE: full text query syntax
Oh, boy. I am not explaining myself properly. There are thousands of parent docs in this database, who have responses associated with them. I am not looking for a particular parent doc with a particular value in the contactfullname field. I am looking for any/all activities (child docs) for any parent docs that do not contain the same value. Here is what it looks like:
John Smith (parent) - contactfullname = “John Smith”
email sent (activity) - activitycontactfullname = “John Smith”
letter sent (activity) - activitycontactfullname = “John Smith”
email sent (activity) - activitycontactfullname = “Alice Jones”
Mary Cooper (parent) - contactfullname = Mary Cooper
email sent (activity) - activitycontactfullname = “Mary Cooper”
letter sent (activity) - activitycontactfullname = “Greg Edwya”
email sent (activity) - activitycontactfullname = “Mary Cooper”
I need to get those activities that do not match the contactfullname field from the parent doc.
I apologize for not explaining myself properly. It wastes time.
Does this make sense now? So, I can’t really use the NOT FIELD ACTIVITCONTACTFULLNAME = “Smith”…right?
Subject: RE: full text query syntax
No. It cannot be done. You cannot select one document in a view based on values in another document in the view.
Subject: RE: full text query syntax
Ok, I understand, I was assuming that you were getting the parent document first, then selecting the children based on that … which is what you are going to have to do … Get all the parent documents, then iterate through them, and get each response set, FTSearch it, and then move to the next parent … there is no way to build the set by comparison to another document.
Subject: RE: full text query syntax
OK. Well, I actually got the point where I have gotten all of the parent docs, iterated thru and got their response set…that is where I am stuck…All of the parent and child docs are showing up in my report. I can’t seem to break it down to just show only those odd balls. I thought I could break it down using the ft search text query syntax…
Subject: RE: full text query syntax
Did you change your ft search like i mentioned before, where you specify the actual field value, not the field name of the parent. Like my example? That will work…
Subject: RE: full text query syntax
Sorry, Jeremy…I couldn’t see your response yesterday, for some reason. My email indicated there was a response, but I couldn’t see it…Oh well…OK. Help me understand something…you say “where you specify the actual field value…”. Sounds like you are telling me to actually put in a text value. Well, short of looking up every value of the contactfullname field from every contact doc, then put that in the ftsearch, I wouldn’t know what it is. Isn’t there some way to do a “match” or “compare” values from two different fields so the system looks for any activities whose activitycontactfullname does not match the parent’s contactfullname field? What am I missing?
Subject: RE: full text query syntax
Nancy…I just replace If n >1 Then with If n >0 Then and it seems to have worked. Do you know why?thanks