Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

GetAllDocumentsByKey() Stoped working, need alternative

Posted on 2010-11-19
23
Medium Priority
?
2,167 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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 2000 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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

688 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