Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2207
  • Last Modified:

GetAllDocumentsByKey() Stoped working, need alternative

Basically all I need to do is set an object variable in excel VBA.  The object is a lotus notes email that I am finding in the Inbox folder by subject.  Allthough Lotus Notes documentation suggests that GetAllDocumentsByKey() requires a catagorized and sorted view, we have been using this code for four years to search emails in multiple inboxes that were not sorted and catagorized.  All of a suden, it stoped working.

So now I need an alternative to GetAllDocumentsByKey() that works searching subjects in a Lotus notes inbox.  Need something that searchs fast because there are a lot of documents to find.  GetAllDocumentsByKey() was very fast until it suddenly stoped working.  I've attached a picture of the inbox folder I'm searching.   Picture of Inbox I'm searching
0
atprato
Asked:
atprato
  • 11
  • 8
  • 4
1 Solution
 
mbonaciCommented:
Where did you read that it has to be categorized?
If the param (key) is a string, it searches in the first sorted column of the view.
If the param is an array of strings, then it searches: first sorted column for array(0), second sorted column for array(1), ...

See here for more info:
http://publib.boulder.ibm.com/infocenter/domhelp/v8r0/topic/com.ibm.designer.domino.main.doc/H_GETALLDOCUMENTSBYKEY_METHOD.html
0
 
atpratoAuthor Commented:
Ok, true, I may have mispoke there.  Still it is suddenly not working, even when I sort first.  Is there any alternative less picky code that is fast too?  Or how to sort the folder before I use getalldocumentsbykey to ensure it will work everytime?
0
 
ironcladsCommented:
My thoughts on the subject is if it worked before there's no reason for it not to be working still, unless something has changed.  Has the view name/alias changed?  Is the first column a sorted column?  Did you try your code using a different view, perhaps one that is categorized or simplified view (less columns)?  Has your environment changed recently?  Something has changed and it is vital to determine what.  I wouldn't blame the "GetAllDocumentsByKey" function before ruling out other possibilities.  If you have tested things out thoroughly and are still having issues and haven't found the issue, I have an alternative.

You could write a Lotusscript agent in a db that basically goes out to each db you want to search, does basically the same thing and dumps the results out to a .tsv (tab seperated value) file which opens just fine in Excel.  I've done things like this and could give you some starter code if you want to go this direction.

Just some thoughts here.  Good Luck.

-TIM
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
atpratoAuthor Commented:
I agree that something must have changed.  Problem is my influence is on the VBA side, not the notes side.  I can't change notes, I just need the best possible VBA to handle the curve balls notes throws at me.  This latest change is the latest curve ball.

What is the code to sort the email inbox folder by subject?  Maybe that will fix my problem?
0
 
ironcladsCommented:
Have you tried using FTSearch to get a collection of documents?  This method should work like "getAllDocumentsbyKey" but is a different way to get results.

Below is some information on FTSearch and an example too.

http://www.codestore.net/help/help85_designer.nsf/f4b82fbb75e942a6852566ac0037f284/1322fa854cf8ecc5852574cf006b9ceb?OpenDocument
http://www-12.lotus.com/ldd/doc/lotusscript/lotusscript.nsf/1efb1287fc7c27388525642e0074f2b6/e8eac6b2b6c7157f8525642e0075cdba?OpenDocument
http://www.manning.com/patton/excerpt_errata.html

-TIM
0
 
atpratoAuthor Commented:
I swapped out the code like this, but got a type mismatch debug?

        'Set dcc = ExpenseView.GetallDocumentsByKey(key)
        Set dcc = ExpenseView.FTSEARCH(key, 0)

Key in this case was "WK# 43-AL YOUNG-"

Any idea what I did wrong?
0
 
ironcladsCommented:
Can you try to simplify the string and leave out any special characters and see if that returns results and doesn't throw an error?  Just trying to see if it will work for you before we get too elaborate.

http://www-10.lotus.com/ldd/ddwiki.nsf/dx/08042008111947AMKMKKYE.htm

-TIM
Lotus-Domino-Designer-8.5-Help.pdf
0
 
atpratoAuthor Commented:
Changed Key to "AL YOUNG" but got the same error.  What about some code to sort the inbox before using getalldocumentsbykey()?  I know from history that getalldocumentsbykey() is a fast search that works, just need to get it working again?
0
 
ironcladsCommented:
Ok, I have to ask the question.  Is the db valid.  When you set it, is it an actual database value?  Also, the view, is expenseView a view?  Is it set?  Can you get the db.Name value and does that return a value.  How about ExpenseView.Name?  Is it value?  You're assuming that getalldocumentsbykey is bad because it's not returning any documents, but have you verified the other elements are indeed being set before you call either getall or ftsearch?  It sounds to me like something isn't getting set correctly.

Let me know what you find.

-TIM

0
 
atpratoAuthor Commented:
Here is my setup:

    Application.ScreenUpdating = False
    Dim Session As Object
    Dim db As Object
    Dim ExpenseView As Object
    Dim Entries As Object
    Dim att As Object
    Dim body As Object
    Dim doc As Object
    Notesserver = Range("Server")
    Set Session = CreateObject("Notes.NotesSession")
    Set db = Session.GETDATABASE(Notesserver, Range("MailDB").Text)
 
    On Error GoTo trap
    Set ExpenseView = db.GetView("($Inbox)")
    On Error GoTo 0
   
    Call ExpenseView.Refresh

I'm not an expert so I need some help answering your questions.  I know how to use the imediate window.  So I can do like:
?db.name
and tell you the results, but I need the right syntax.  ?db.name does not seem to be a vaild command.  If you tell me the commands, I can tell you the result.
0
 
ironcladsCommented:
Sorry, it's db.Title and view.Name.  If these two are valid, can you read a document?  something like Set doc = ExpenseView.GetFirstDocument.  Does that return a value?  If so, you could iterate through the view and check for matching values.

I don't believe GetAllEntriesByKey is valid with use in Excel, but you could check.  It's a valid Lotusscript function as of Notes 5.

Let me know what you find.

-TIM
0
 
atpratoAuthor Commented:
?db.title
SCU Payroll

?expenseview.name
($Inbox)

Yes, I can read a document.  I did
Set tst = ExpenseView.getfirstdocument
Then to validate:
?tst.getfirstitem("subject").text
WK# 23-Stephanie Hawkins-

So my object variables seem valid.  So I should be able to use lotusscrit with these lotus objects.
0
 
ironcladsCommented:
You could try to use GetAllElementsById.  If it works it is much faster than GetAllDocumentsByKey when you get only column values instead of resolving to the document.  I'm posting some information as well.

If this won't work, then I'd recommend using what was previously mentioned:

Set doc = view.GetFirstDocument
While not(doc is nothing)
'do something
Set doc = view.GetNextDocument( doc )
Wend

It would take longer to run, but also work in lieue of GetAllDocumentsByKey.  Either that or from my original post, you could write that lotusscript agent to get values and dump out a file for use in Excel.

-TIM
Lotus-Domino-Designer-8.5-Help.pdf
Lotus-Domino-Designer-8.5-1-Help.pdf
Lotus-Domino-Designer-8.5-2-Help.pdf
Lotus-Domino-Designer-8.5-3-Help.pdf
0
 
ironcladsCommented:
Oops...I meant to say, GetAllEntriesByKey.  I'm mixing Lotuscript and JS up.  Would be nice if you could mash up languages like you can music.  Would be fun...and a nightmare. ;-)
0
 
atpratoAuthor Commented:
Hmm, something is wrong.  I have valid objects.  I just want to fill an object variable with a collection of documents based on a subject search.  Should be simple, right?  Someone please help!
0
 
atpratoAuthor Commented:
When I do:

        Set dcc = ExpenseView.GetallEntriesByKey(key)
        Set doc = dcc.getfirstdocument

When I try to set doc, it bugs out as "Object doesn't support this property or method"
0
 
ironcladsCommented:
FYI, on the GetAllEntriesByKey it's going to be an entry collection, not document collection.  Your doc should be changed to entry for the sake of confusion with the value set as:

Set entry = dcc.GetFirstEntry

If it is valid, then you could do somthing like this:
colvalue = entry.ColumnValues(0)

Here's what it looks like in lotusscript:
  Dim session As New NotesSession
  Dim db As New NotesDatabase("","test.nsf")
  Dim view As NotesView
  Dim entry As NotesViewEntry
  Set db = session.CurrentDatabase
  Set view = db.GetView("All")
  view.AutoUpdate = False
  Set entry = view.GetEntryByKey("Sports car", False)
  Forall colval In entry.ColumnValues
    col% = col% + 1
    Messagebox colval,, "Column " & col%
  End Forall

Open in new window


Yes, it should be simple.  Afterall, as you said, it was working until recently.  Go figure.  I was hoping someone else with more Excel experience might be able to shed some light on your issue.  Unfortunately, I'm tapped out at this point.  Either escalate the item so more people respond (hopefully) or go with the iteration of documents or the lotusscript agent as I propose.

Good Luck.

-TIM
Dim session As New NotesSession
  Dim db As New NotesDatabase("","test.nsf")
  Dim view As NotesView
  Dim entry As NotesViewEntry
  Set db = session.CurrentDatabase
  Set view = db.GetView("All")
  view.AutoUpdate = False
  Set entry = view.GetEntryByKey("Sports car", False)
  Forall colval In entry.ColumnValues
    col% = col% + 1
    Messagebox colval,, "Column " & col%
  End Forall

Open in new window

0
 
mbonaciCommented:
First, let me ask you, are you sure you previously used Inbox folder for your lookups?
Can you tell us more about your business case? Why and what are you looking-up?

Ok, since you don't have access to Notes, I suggest you check out the folder columns settings, in order to figure out which is the first sorted one:

NotesViewColumn class:
http://publib.boulder.ibm.com/infocenter/domhelp/v8r0/topic/com.ibm.designer.domino.main.doc/H_NOTESVIEWCOLUMN_CLASS.html

    Dim Session As Object
    Dim db As Object
    Dim ExpenseView As Object
    Dim Entries As Object
    Dim att As Object
    Dim body As Object
    Dim doc As Object

    Notesserver = Range("Server")
    Set Session = CreateObject("Notes.NotesSession")
    Set db = Session.GETDATABASE(Notesserver, Range("MailDB").Text)
  
    On Error GoTo trap
    Set ExpenseView = db.GetView("($Inbox)")

    Forall c In ExpenseView.Columns

        Messagebox ( c.Title & ", sorted: " & c.IsSorted & ", formula: " & c.Formula )

    End Forall

Open in new window

0
 
atpratoAuthor Commented:
Basically, excel spreadsheets are mailed to this inbox.  I collect the data from certain spreadsheets based on the subject of the email they are attached to.  Crude, but works well.

Ok, very interesting result to your question.  I had to use for each c since VBA did not seem to know what to do with forall.

There are several columns before the "Subject" column that are sorted:true.  And the column I am meaning to search is set to sorted: false.

I guess I misunderstand the whold sorted thing.  When I look at the inbox it is sorted by subject, but I guess that does not matter, maybe this sorted property is what matters.

Can I use VBA to set the other columns to sorted: false and set subject to sorted: true (subject is the last column)?  If so, what is the code?  Any consequence to messing with this setting?  Thanks for your help!
0
 
mbonaciCommented:
Yes, you need to know that there is a difference between how the view data is sorted in the view index and how the users are sorting documents in the UI.

See in the attached image, the top setting is index, and "Click on column header to sort" is UI.

So, in order to use getDocumentByKey (or any other *byKey method) the view index must be sorted by that column (in memory it's represented by fast BST - binary search tree), so that the key can be found very fast. Otherwise, it would be called Search, not Lookup :)
Clipboard01.gif
0
 
mbonaciCommented:
And yes, you can change sorting programatically:

c.IsSorted = True

Open in new window


But be aware that this change will be visible in the UI as the default sorting (when folder is first opened it will be sorted according to "Sort" setting, until the user changes that by using column header arrows).
0
 
atpratoAuthor Commented:
Ok, this fixed the problem!!!

    For Each c In ExpenseView.Columns
        If c.Title = "Subject" Then
            c.issorted = True
        Else
            c.issorted = False
        End If
    Next c
0
 
atpratoAuthor Commented:
Thanks so much!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 11
  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now