Solved

GetAllDocumentsByKey() Stoped working, need alternative

Posted on 2010-11-19
23
2,008 Views
Last Modified: 2013-12-18
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
Comment
Question by:atprato
  • 11
  • 8
  • 4
23 Comments
 
LVL 22

Expert Comment

by:mbonaci
ID: 34173622
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
 

Author Comment

by:atprato
ID: 34173988
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
 
LVL 3

Expert Comment

by:ironclads
ID: 34174173
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
 

Author Comment

by:atprato
ID: 34174363
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
 
LVL 3

Expert Comment

by:ironclads
ID: 34174582
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
 

Author Comment

by:atprato
ID: 34174706
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
 
LVL 3

Expert Comment

by:ironclads
ID: 34174836
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
 

Author Comment

by:atprato
ID: 34175104
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
 
LVL 3

Expert Comment

by:ironclads
ID: 34175478
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
 

Author Comment

by:atprato
ID: 34175766
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
 
LVL 3

Expert Comment

by:ironclads
ID: 34175827
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:atprato
ID: 34175997
?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
 
LVL 3

Expert Comment

by:ironclads
ID: 34176156
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
 
LVL 3

Expert Comment

by:ironclads
ID: 34176193
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
 

Author Comment

by:atprato
ID: 34176257
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
 

Author Comment

by:atprato
ID: 34176285
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
 
LVL 3

Expert Comment

by:ironclads
ID: 34176413
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
 
LVL 22

Expert Comment

by:mbonaci
ID: 34188073
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
 

Author Comment

by:atprato
ID: 34188258
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
 
LVL 22

Expert Comment

by:mbonaci
ID: 34188369
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
 
LVL 22

Accepted Solution

by:
mbonaci earned 500 total points
ID: 34188417
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
 

Author Comment

by:atprato
ID: 34189502
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
 

Author Closing Comment

by:atprato
ID: 34189505
Thanks so much!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

707 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

11 Experts available now in Live!

Get 1:1 Help Now