An Alternative to DBLookup. Without The Views.
Most of my Forms use an @DBLookup or two at one point or another. It's a great function for all sorts of stuff, with one big drawback — you have to have an associated view to lookup against.
How many times have you worked on an application - whether originally yours or not - and needed to do a lookup only to find there's no suitable view? As a conscientious developer you loathe the idea of adding yet another view. Even if you do find a view you can use you then worry it might change or go missing and kill the code you add. It's a tricky situation.
Imagine if there were another function we could use. Somewhere between @DBLookup and @GetDocField. Maybe called @FindDocument:
@FindDocument(Field; Value; ReturnField; Keywords)
So you could use it like this to find somebody's email address:
@FindDocument("username"; "jake howlett/rockall"; "email")
Maybe it would be better if it were called @FindDocuments and returned all matches. Maybe the parameters passed to it should allow text lists so we could match multiple fields?
Now, I know what you're going to say. Performance would suck. So, instead, we've got to add a view each time we want to get at the documents via another route. Surely there's as much of a performance hit there!? How many times have you seen a database with a myriad of views named like "(luThisByThat)" only to wonder whether they're all really needed.
Personally, I'd suffer the performance hit just so I could easily get to any document I wanted, without having to search for the best view to use or adding another one. How slow could it possibly be? It's only like a db.Search() call, which always seem quite fast to me.
Skip to the 
You can subscribe to an individual
For a second there I thought you'd discovered a solution to one of the biggest gripes us Notes bods have.
Oh well, I live in hope!
It's not just me then Dave. I was half expecting the first reply to be either a) Don't be so stupid b) You can do that with this @Function.
Sorry about the misleading title.
Why not have a single lookup view with all documents, with the key column being "formname*keyvalue"
Even with a lot of documents, this should be reasonably fast, and you can use it for all new forms, without creating a new view.
Instead of many views, I use to include only one view (let's call INDEX) with *one* sorted colum marked with "Show multiple values as separate entries" (important). The formula for this column is something like this (following your example):
@If(
Form="docPerson";("User."+FULLNAME:"Mail."+MAIL);
Form="docGroup";("Group."+NAME);
.../...
Form + " - No Key")
Then, it's possible to include as many keys as required for every type of document. So you could use this formula to get the mail of any user:
* Get mail from username
@DbLookup("":"" ; "":"" ; INDEX;"User.jake howlett/rockall" ; "MAIL")
* Get username from mail
@DbLookup("":"" ; "":"" ; INDEX ; "Mail.jake@rockall.com";"FULLNAME")
* Get all users list:
@DbLookup("":"" ; "":"" ; INDEX ; "User." ; "FULLNAME")
In lotusScript :
get one document
index.getdocumentbykey("Person.XXX",true)
get a collection
index.getAllDocumentsByKey("User.",false)
Tim.
"formname*keyvalue"
I don't follow. How would this work?
Juan:
Nice idea. Never thought of or seen that before. It's not the ideal solution I'm after though, where you can look for ANY field/value combination in any document at any time, without worrying about whether it's in this special view or not.
Juan,
You realise of course that the index for this kind of view would be huge. Really huge. Really, Really, REALLY huge.
Whilst I like the idea, I think the hit on the database size would be too much for most applications. It's far better to have a smaller number of limited views, than one big view.
Jake
Let's say you have two forms e.g.
person
company
and each document has a unique field called
id
Your all-singing all-dancing lookup view would have one sorted column showing the value of the form field concatenated with a separater character eg "*" and the id value
e.g. you have a "person" document where the id is "abc001"
the view column would show
person*abc001
Now you can easily do a @dblookup (your dblookup presumably knows what type of form it is searching for)
The beauty is that you can have multiple key values - say you want to do a dblookup on two separate field values, then you set your view column to
form*field1*field2
and so on.
(I used to work with the Pick o/s which is where compound keys such as this really came into their own)
If you know or can store the document ID of the document where you want to get the data - then @GetDocField( documentUNID ; fieldName ) can sometimes be an option.
Actualy, rereading this, it's the same solution as Juan's!
Also, (hmmm, must read posts more closely), your desire to look up any combination of fields at any time isn't solved by this.
Nah, go on creating views. Notes isn't a relational db, otherwise you could use "select" statements (don't get me started on the NotesSQL offering)
DigestSearch method gives you almost what you ask for, especially if you use LotusScript instead of @Formula.
{Link}
I'd be very happy if it was possible to access fieldvalues in documents in other databases with @GetDocField.
@GetDocField( <database>, <unid> ; <field>)
Ben Langhinrichs!!! Please help us!!!!
.::AleX::.
Ahh yes, the holy grail of searching unstructured data. What you want has costs associated with it.
If you wanted to be able to arbitrarily match on any document in the database, you have two choices:
1. Make index that permutes all document fields and values.
2. Brute force search
The first option will make your searches fast, but the updating of the index will become a general performance bottleneck and will use *lots* of disk space. Note that this option is really the same as a full text index, both sematically and performance wise.
The second option is to open each document, examine it contents to see if they match the criteria. This is how db.search works (and incremental view refreshes too). This is a linear search, if you database has N documents, then it will examine all N documents. So this method will be pretty fast as long the total number of documents is small, but simply does scale.
what about keeping the Indexes outside notes over for example in (LEI) msaccess indexed tables ?
makerjoe
Jake
I'm sure you already realise that it all depends.
On expectations of users / developers / budget holders and every other &*(! who has an opinion on the applications.
There is NO answer to the problem of how to find a document. There are times when a brute force linear search is acceptable and times when you really need an index to get there fast.
One of the small issues with Domino is that it's not at all intuitive what the trade off values are so it's hard to make good judgement calls when developing.
Working (as you have) for some years gives a feel for things but that's not really a scientific basis for doing things this rather than that way.
Does anyonm
e have any REAL evidence of the trade offs between eg a single big index (say every document indexed by form +key value) against say a bunch of much smaller indices with documents restricted by form name and indexed on key value.
Enquiring minds would rather like to know.
Not that it would change my development habits.
Perish the thought.
Regards
If you're looking for the email address Jake .... try @NameLookup( [ lookupType ] ; username; itemtoreturn )
;0
Without re-designing everything a simple interface to the existing search and ftsearch methods under that covers would be very nice.
That is, if we are talking wishlists.
I have a full text indexed database with about 178,000 documents. In the "Search This View" feature in the Notes Client I used the following formula:
"[form] equals order and [courtstyle] equals county"
It returned 592 documents in well under a second. So I think the equivalent in formula language, Java or LotusScript would give you the ability to do totally general searches based on the contents of specific fields. (I've had a hard time searching number fields this way, however.)
The full text index is stored external from the Notes database itself so size isn't as much of a problem. Of course the full text index isn't updated in real time so that make it problematic for many applications.
I don't know much about how often the full text indexing process runs or if it only indexes documents that changed since the last index. Anybody know about this?
Rob:-]
Juan, I think that you method has merit in some situations. Like Jake I hadn't thought of that before.
Jake, I finally put together an article for The View on LotusScript Performance which will appear in this months issue. The article focuses on on processing subsets of documents in a view (subset determined by a key value). Obviously I can't give away the details but I will suggest that you seriously rethink your opinion of db.search. As mentioned there are always trade-offs between speed of accessing a document and the performance hit of generally maintaining N number of views. In my opinion this is where it becomes more art that science.
Rob, The main problems with FTI searches are the existence of the index and the currency of the index. By existence I mean does the index exist? Think replicated databases. Think databases replicated locally. If you start doing FTI searches on a db that doesn't have a FTI then you may need to start looking for another job because it will be so very slow.
The currency of a index means how up to date it is. If the FTI isn't up to date then your search may not find all documents. In some cases this is ok but often with database lookups you actually want to find (all) the documents(s) in the database that match the key.
Unrelated - but a big wish list item for me is a "data grid control" to present query results without having to embed a view. Maybe you could try to establish the top ten Domino developer wish list items one of these days.
Rob,
I believe You can test if the database index is up-to-date, and if not, force the server to update it, before the actual FTI search is made. (Or?)
Even if Damien is no longer in charge of it, his posting seems to give a good indication of what we have to expect here for the future. Probably nothing.
One (not perfectly related) side note: It turns out, that @DocFields DOES work in column formulas, even if the documentation explicitely states the opposite. So, wherever this might be helpfull, you can set up one single view column holding the values of all items stored in one document (using @GetField inside @Transform or @For). You can even create one long string denoting name-value pairs.
Obviously this could become a performance hog with respect to both, index size and index update time, but I still found it to be entertaining at least.
Alex - For Notes Version 3, I introduced the @YourCommand Toolkit. In it was the FieldGet function in formula language, which would pretty much do this. Unfortunately, Damien is correct. It is only fast if your database is small. Still, very powerful. I've tested, and it still works in ND7, but the toolkit is no longer available publicly. C'est la Vie!