Solved

need help to build a database.search formula

Posted on 2008-10-30
18
369 Views
Last Modified: 2013-12-18
I'm writing a webservice with java on lotus domino designer / notes 7.

I want to get all contact that are in subset of "regions".

The problem is the region migh be defined in a "parent" field.
for that i build a unefective but working function "getFieldFormParents" i loop trough the colection to get only the documents that are in the selected regions.

I'm getting timed out...

Could i do this directly in the database.search using a formula or something faster than my loop thing?

private String getFieldFormParents(Document Doc, String FieldName, Database ContactDB){

		String ParentUNID;

		Document ParentDoc;

		String FieldValue;

		

		try{	

			//check for the field

			FieldValue=getField(Doc, FieldName);

			if (FieldValue.trim().length() > 0){

					//found!

					return FieldValue;

			}else{

				//check for a parent

				ParentUNID = Doc.getParentDocumentUNID();

				if (ParentUNID.trim().length() > 0){

					ParentDoc = ContactDB.getDocumentByUNID(ParentUNID);

					//recursive call

					return getFieldFormParents(ParentDoc, FieldName, ContactDB);					

				}else{

					//no more parent field not found

					return "";

				}

			}

		} catch(Exception e) {

			e.printStackTrace();

		}

		return null;

	}

private String getField(Document ContactDoc, String FieldName){

			 Item item;			

                 try{

					// Get item object. Global object nabdoc was set earlier in

					// getPersonDocument function.

					item=ContactDoc.getFirstItem(FieldName);

					//Sometimes item does not exist in the document, it's not an error so

					// we return an empty string.

					if (item==null){

						return "";

					}else{

					//Return the value that the item holds

						return item.getValueString();

					}

				} catch(Exception e) {

					e.printStackTrace();

				}

			return "";

		}
 
 

	private DocumentCollection getContactDocumentCollection(int intContactSelectionType,

												 String strFilter, 

												 boolean onlyFirstLetterFilter,

												 int[] RegionSelection,

												 Database ContactDB){			

			DocumentCollection ContactsSearch;

			DocumentCollection ContactsReturn;					

			Document ContactDoc;

			Document CompanyDoc;

			String strRegion;

			int intRegion;

			boolean blnFound;

			try{

				if (onlyFirstLetterFilter){

					//Search the database for documents where CompanyName field matches received parameter

					ContactsSearch = ContactDB.search("Form=\"Contact\" & @LowerCase(@Left("+ContactSelectionType.getSearchField(intContactSelectionType)+";1))=\""+strFilter.toLowerCase().charAt(0)+"\"", null, 0);

					//ContactsReturn = ContactDB.search("Form=\"Contact\" & @LowerCase(@Left("+ContactSelectionType.getSearchField(intContactSelectionType)+";1))=\""+strFilter.toLowerCase().charAt(0)+"\"", null, 0);

					
 

				}else{

					ContactsSearch = ContactDB.search("Form=\"Contact\" & \"A\"=\"X\"", null, 0);				

					//ContactsReturn = ContactDB.search("Form=\"Contact\" & "+ContactSelectionType.getSearchField(intContactSelectionType)+"=\""+strFilter+"\"", null, 0);				

				}

				ContactsReturn = ContactDB.search("Form=\"Contact\" & "+ContactSelectionType.getSearchField(intContactSelectionType)+"=\""+strFilter+"\"", null, 0);

				ContactDoc = ContactsSearch.getFirstDocument();

				while (ContactDoc != null) {

					strRegion=getFieldFormParents(ContactDoc, "Region", ContactDB);

					strRegion = strRegion.trim();

					if (strRegion.length() == 0){

						//region is from unlinked CIE

						CompanyDoc = getCompanyDocumentForContact(ContactDoc, ContactDB);

						strRegion=getField(CompanyDoc, "Region");

						strRegion = strRegion.trim();

					}

					if (isInteger(strRegion)){

						intRegion = Integer.parseInt(strRegion);

						blnFound = false;

						for (int i=0;  i<RegionSelection.length;  i++){

							if (intRegion == RegionSelection[i]){

								blnFound = true;

								break;

							}

						}

					}else{

						blnFound = false;

					}

					

					//remove doc from Collection

					if (blnFound) {

						ContactsReturn.addDocument(ContactDoc);

					}

					ContactDoc = ContactsSearch.getNextDocument();

				}

					

				

				return ContactsReturn;

			} catch(Exception e) {

				e.printStackTrace();

			}

			return null;

 		}

Open in new window

0
Comment
Question by:David Robitaille
18 Comments
 
LVL 63

Expert Comment

by:SysExpert
ID: 22845366
Not sure, but why not use a full text index and then use the search function if it exists in Java...

0
 
LVL 18

Author Comment

by:David Robitaille
ID: 22845423
Well, how does you do this in lotus script? I got a vague idea of what you are talking about, but I not a lotus expert and i hope to jump to another call when that webservice is done.
I will translate in java, I`m getting good a this....
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 22845576
Hi again,

Unfortunately, this is the way, if you want all the flexibility you can get. There are (at least) three ways of searching in Notes:
- Search, that's what you use, flexible but dead slow, because it does a linear search of the database without using any index
- FTSearch, very fast and less flexible, no formulas but a lot is still possible
- Views and GetDocumentByKey(), selection like Search, ultra-fast since indexed, but the selection is fixed; if you change the selection formula during runtime the speed is reduced to that of Search

GetDocumentByKey, or GetAllDocumentsByKey have a parameter that tells Notes to use an exact match or not; if not, then the key value (or key values) being looked for are matched at the start of the string in a specific column.

It seems to me that you could use one or more rather fixed views to search for your data. The GetDocumentByKey function's first parameter is an array of key values, each matching the corresponding column in a view.

Matching for a single starting letter seems no problem using the exact_match parameter set to false.

Now the parent problem. Two ways: your method if you don't want to copy data down the hierarchy all the time, or just that. It is rather common practice to copy data to the full hierarchy of documents if a view of only responses is required, or a sorted non-hierarchical view.

Does this help you a little?
0
 
LVL 1

Expert Comment

by:johnjardin
ID: 22849497
The FT Search uses Select Statements, like you would in a View selection formula. It works quite well.
0
 
LVL 18

Author Comment

by:David Robitaille
ID: 22850018
"Now the parent problem. Two ways: your method if you don't want to copy data down the hierarchy all the time, or just that. It is rather common practice to copy data to the full hierarchy of documents if a view of only responses is required, or a sorted non-hierarchical view."
Well, it s because the database in not made by someone in our company. It s a 3th party product and I`m trying to pull the data out of it as i discover it`s structure...
The data "should" be copied down the hierarchy, but it`s look like it does not be copied all the times...  There is a "Region" field within the "contact" document, but it look like they are not filled all the time, even if the contact is associated with the company in the lotus application.
I made the recursive getFieldFormParents search using ParentUNID, but it seem to not work verry well. I made it recursive because, theoricaly, company could have divisions. But  Debugging test just show me the field is populated from the getCompanyDocumentForContact(ContactDoc, ContactDB); it basically retrieve the "company document" using the "company name" field int the "Contact Document".
So, That company name field appear to be more reliable than those ParentUNID.

So here what i need
  • Contact Doc
    • UNID
    • FirstName
    • LastName
    • CompanyName
  • Company Doc
    • CompanyName
    • Region
When you are talking about "key" i always think about primary/foreign key type of thing. But i got a feeling it not what that. how could that help me? I started to work with lotus notes for about 2 week, and within one of theses weeks i was trying to make notesql work, so i m not that good at those search things.

 

private Document getCompanyDocumentForContact( Document ContactDoc, Database ContactDB){

		String CompanyName;

		DocumentCollection searchResults;

		try{	

			CompanyName = getField(ContactDoc, "CompanyName");

			searchResults = ContactDB.search("Form=\"Company\" & CompanyName=\""+CompanyName+"\"", null, 0);

			return searchResults.getFirstDocument();

		} catch(Exception e) {

			e.printStackTrace();

		}

		return null;

	}	

Open in new window

0
 
LVL 18

Author Comment

by:David Robitaille
ID: 22850798
I think i put the finger on something.
i just saw a CompanyUnique field on the Contact Document. but for some reason:
that work (but it`s too slow)
CompanyUnique = getField(ContactDoc, "CompanyUnique");
searchResults = ContactDB.search("Form=\"Company\" & CompanyUnique=\""+CompanyUnique+"\"", null, 0);
return searchResults.getFirstDocument();

and that dont work (but it`s fast enough)
   CompanyUnique = getField(ContactDoc, "CompanyUnique");
   return ContactDB.getDocumentByUNID(CompanyUnique);
????
0
 
LVL 18

Author Comment

by:David Robitaille
ID: 22850963
ok, CompanyUnique look like this : "BMIH-4RTS3C"
and UNID look like this : "98D8137851C21D2D852569A7006DAE04"
How could i use those CompanyUnique field????
 
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 22851752
Maybe there is a view that's ordered by the CompanyUnique field? So you can use a GetDocumentByKey there?

The unique-ID that's known as UNID (it actually means Universal ID) is used to uniquely identify documents within replicas of one database. That id is guaranteed to be unique across replicas. The hidden field $Ref contains the parent's UNID. If a document has a field $Ref, it is a response document, and then you should be able to follow the hierarchy up to the root.

Sadly, things can go wrong. What could have happened in your database? Someone deleted a parent document and forgot the responses. They are not automatically deleted, and they become orphan documents in the database. In a hierarchical view, you'll never see them again, unless something attaches them to a new parent. In a flat view though, that selects that particular type of documents, you can still see them.

Now, if they made a relationship between parent and child using the CompanyUnique field, and the field exists on both levels, you should be able to use that to find the company in a view. By the way, that unique key is generated by the formula function @Unique .

> ... but it`s too slow ...

That's what I said: Search is slow; it can be used instead of a view, e.g. in background/nightly agents that run only once per day; it is more efficient to do a Search than to maintain the asdditional view's indexes all day.

For your purpose, cannot you find a way to use several views for rapid searching?

@johnjardin:
> The FTSearch uses Select Statements
No, it does not. FTSearch has an entirely different syntax. But it IS very quick indeed.
0
 
LVL 18

Author Comment

by:David Robitaille
ID: 22851829
"Now, if they made a relationship between parent and child using the CompanyUnique field, and the field exists on both levels, you should be able to use that to find the company in a view"
I will go in that trail, but i m losing myself in those views... the app have about 140 views...
  1. How could i find the correct view, what should i check?
  2. How will i use a view to get the document?
Or could i use FTSearch to fing the company faster ; the folowing code works well in a search.

CompanyUnique = getField(ContactDoc, "CompanyUnique");
searchResults = ContactDB.search("Form=\"Company\" & CompanyUnique=\""+CompanyUnique+"\"", null, 0);
return searchResults.getFirstDocument();
 
 
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 46

Accepted Solution

by:
Sjef Bosman earned 500 total points
ID: 22852007
ONEHUNDREDANDFOURTY VIEWS?? Goodness...

Here comes a giant leap forward: download DDSearch, it will help you searching the design.
http://www-10.lotus.com/ldd/sandbox.nsf/ecc552f1ab6e46e4852568a90055c4cd/f090b24ea76b1c0100256c45003e6396

Use DDSearch to find view using CompanyUnique.

It can be used on R5 and R6. For R7 you might need some tinkering with the Registry. R8 I don't know.
Also, it is possible that the generation of the XML file fails. Please say so if it does, I know how to correct that.

My advice about searching, if it's done quite often:
1) use a view
2) if a view seems impossible, try to use a view
3) maybe 2 views then?
4) use FTSearch, if that can be done
5) still can't use a view??
6) okay, all right, you win, let's use a Search...

:-))
0
 
LVL 18

Author Comment

by:David Robitaille
ID: 22852172
i m with R7
I got that error installing DDSearch
IBM Lotus Notes 6 is installed as a MultiUser configuration on this system. Because a MultiUser configuration excludes IBM Lotus Domino Designer 6, you cannot install DDSearch for IBM Lotus Domino Designer 6 in this configuration.
but, How will i use a view to get the document?

0
 
LVL 18

Author Comment

by:David Robitaille
ID: 22852484
ok, i think i got it, i found a view called "companybyname"
i found the way to extract the Document, seem fast enougn but need more test, giving new shortly...

private Document getCompanyDocumentForContact( Document ContactDoc, Database ContactDB){

		String CompanyName;

		View CompanyView; 
 

		try{	

			CompanyView = ContactDB.getView("companybyname");

			if (CompanyView == null){

				return null;

			}else{

				CompanyName = getField(ContactDoc, "CompanyName");	

				return CompanyView.getDocumentByKey(CompanyName, true);

			}

		} catch(Exception e) {

			e.printStackTrace();

		}

		return null;

	}	

Open in new window

0
 
LVL 18

Author Closing Comment

by:David Robitaille
ID: 31511840
Thanks a lot on that one. I got something usable!
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 22854521
That's the error indeed. Here's the solution:

http://www-10.lotus.com/ldd/sandbox.nsf/Threads/91DBF96DE7BAB7998525719500554A64?OpenDocument

Nice that the solution fits your problem, more or less. I hope you can you do without all the fancy search possibilities.

Thanks for the grade!

PS Installing DDSearch should really work after the the registry change. If you have trouble with "DXL exporter failed" or something like it, look on the same Sandbox page for more help.
0
 
LVL 18

Author Comment

by:David Robitaille
ID: 22867030
Well, again, Thanks a lot, on that one and on the others questions on domino/lotus you helped me with.
I hope i will not need your help anymore, it not because i dont like you, but i dont especially like that kind of database. I think i got enough knowedge for the needs of that project.
I just whant your advice on one last thing, that could lead on a other question. just answer yes or no. i may open a new question based on your advice.
To speed up things, there is a easy way to build a view that fits my needs :
  • Contact Doc  
    • UNID  FirstName  
    • LastName  
    • CompanyName
  • Company Doc  
    • CompanyName (for linking)
    • Region

       

0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 22869751
You should not "like" or "dislike" databases, they are "things" and not worth being liked or disliked. :D

No, but maybe.

Sorry, it needs a little explanation.
If you think of creating a view with two types of documents, that can be done, but the relationship cannot really be fixed. What can be done is a view based on two types of forms, ordered by CompanyName, and then by form-type. It all depends on the ordering. A potential problem is contacts without company: they'd show up in the view but there is no "parent" company document in the view.
0
 
LVL 18

Author Comment

by:David Robitaille
ID: 22869947
Well, in fact, I not that i dislike them, but that I dislike working with them. I feels like I m hitting screws with a hammer. Not that note is the wrong tool, but that I`m the wrong tool. In that case, you where my screwdriver!
Your explanation on my last interrogation gave me some light. on my quest for a correct view, I found a view named "All By Company Hierarchy" that reflect what you are describing. they solved the "no company" problem with code like this :
@If(Form = "Division" | (Form = "Contact" & DivisionName != ""); @If(CompanyName = "" ; "-- No Company" ; @Trim(CompanyName)) + "\\" + @Implode(@Trim(parentDivisionNames:DivisionName); "\\") ;
form = "Company" | (Form = "Contact" & CompanyName != ""); @Trim(CompanyName) ;
Form = "Contact" & CompanyName = "" ; "-- Individual"; "" )

Anyway, i was not able to use it to get what i want and I think it will be better, for now, to leave it the way it is.
So finally, I cannot say it enough, thanks a lot. you really helped me with those "things".
 
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 22871477
LOL

You're welcome!  A+
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
This video discusses moving either the default database or any database to a new volume.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now