I have added two new fields in my Notes database and now the end-user wants me to populate those fields from an Excel Spreadsheet she has been using to capture the data. How can I do this?
What I was thinking was to import her data into a hidden view and sort by the user’s id (only distinct field), then have another hidden view in my database that has all of the current information. Run an agent to compare user’s id in two views and pull information from one to the other if id matches. Will that work and if so, how to do it?
Thanks in advance.
Subject: Import information by matching field?
Yes, the plan that you have laid out sounds like it would work fine. What are you stuck on? Writing the agent or what?
Subject: RE: Import information by matching field?
I always feel so stupid coming out here. I’m a self-taught Lotus Notes programmer so when I’m put up to a challenge like this I ‘know’ it can be done, but I rarely know how to do it! Yes, writing the agent is the issue. I can do some searching . . . thank you.
Subject: RE: Import information by matching field?
Hey Patty,
Maybe I can save you some time.
dim ExistingDocs as NotesView
dim ImportDocs as NotesView
dim SearchResult as NotesDocument
dim CurrDoc as NotesView
dim session as new NotesSession
set ExistingDocs = session.CurrentDatabase.GetView(“ExistingDocsView”)
set ImportDocs = session.CurrentDatabase.GetView(“ImportDocsView”)
set CurrDoc = ImportDocs.GetFirstDocument()
do until CurrDoc is Nothing
set SearchResult = ExistingDocs.GetDocumentByKey(CurrDoc.GetItemValue(“my_search_field”)
if not SearchResult is nothing then
... Copy information over and save
end if
set CurrDoc = ImportDocs.GetNextDocument(CurrDoc)
loop
This should help you get started.
Subject: Import information by matching field?
If you look around this forum and the 4/5 forum (and the Sandbox and OpenNTF and…), you’ll find a number of examples of code that allows you to pull from or push to Excel.
I assume you have a key field that can be used to match Notes docs with Excel rows. If that’s true, conceptually, what I’d do is:
Open the Excel file.
Navigate to the first row of data.
Grab the value for the key.
Use the key and find the notes doc by key (*).
Grab the values in Excel and put them into the Notes doc.
Save the Notes doc.
Move to row 2 in Excel and repeat.
(*) This assumes you have unique keys and only one Note document per key. If any give key has more than one Notes doc, they you’ll need to figure out a key that is unique and maps one and only one row of Excel data to one and only one Notes document.
HTH.
Doug
Subject: RE: Import information by matching field?
Thank you. The key is the user’s id (their email id) and yes I have that on both my notes form and the excel spreadsheet.