[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

@DbColumn lookup from embedded Excel Workbook

I would like create a Dialog List field (field name: DktDest).  The field choices will be  obtained by doing an @DbColumn lookup using data from a form-embedded Excell Workbook.  The embedded Workbook is named "Datakit Worksheet", the worksheet is titled "Alpha Dest" the column is "Dkt Dest" (Column A).  In my efforts to make this work, I tried referencing a "named range" (this range is named "DktDestProp") in the embedded spreadsheet, but was still unsucessful!

Thanks...Roger
0
rognyn
Asked:
rognyn
  • 2
1 Solution
 
HemanthaKumarCommented:
You have to import the excel data into a lotus notes view.. and then use DBColumn on this view..

~Hemanth
0
 
kaliosCommented:
Roger,

Yes, the efficient way to accomplish this is to import the excel document into a view.

Create a form named Import. Create all the fields you want to import from the excel document in this form.

Only keep the relevant data in the excel that you want to import. Lets say you are importing two columns from the view.
ColumnA , ColumnB. (These are the titles in the 1st row in the excel document)

Create two text fields in the form called ColumnA and ColumnB in the import form.
Now go to the excel document and do a save as and then save it as a .wk4 file.

Now create a view in Lotus Notes which has Select FORM = "Import".  Create two columns ColumnA, ColumnB in the view.

Then go to the view, click on file , import.

choose file types as Lotus 1-2-3 and then select the .wk4 file and click on import.
You should get a window called... 123Worksheet Import..
Select import as Main document.
Using form as Import form.
Column format : using view
Click ok.

Once the import is done.. You can view the data in the view.
Now you can use the @DBcolumn based on this view...

If you want to do this using Lotus Script.. Check this question...

http://www.experts-exchange.com/Applications/Email/Lotus_Notes_Domino/Q_21156814.html

Good Luck
Kalios
0
 
kaliosCommented:
The Lotus script in the link above basically shows you how to read data from an excel document and import into a form.

Kalios
0
 
RanjeetRainCommented:
If this is a one time activity and the document has fixed values, the above two suggestions are your best bet. If the contents of the attached document may vary, the above method wont help. In that case, use this approach.

(1) Detach the embedded file to a temproary location
(2) Read the range using OLE Automation and populate your list
(3) Or in lieu of step 2, connect to the excel sheet using ODBC and read the data dynamically. With this method, you can even use DBLookup/DBColumn on the excel sheet.

Read the topic "Accessing external databases through LS:DO using @functions" in the designer help for more info on this.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now