Question

Get values from view entries

Asked by: shuboarder

Hi,

I've got an idea that I am struggling to develop.
We have a self developed database catalog that has information on each of our databases.
We have 5 fields in our database documents where we can add "related databases" i.e. dbs we link to etc.

What I would like to do is create a portal type of linking system so that from within any of our databases we can link to the related databases based on our catalog information.

So far I have come up with the idea of creating a view in our catalog that displays database name in first column and related databases in second column.

i.e.

Database Name Related Databases
HR Database
HR News
HR Statistics

etc.

I would then like to embed this view into a page in any database showing single category based on current database name (@dbname)

So far so good...

Now the tricky part!

I would like it so that whenever someone clicks one of these documents in the embedded outline it takes them to that database and not the document in our catalog.

In the example above in our HR Database users would see the page with the embedded view and the 2 related databases. If they click the line that says HR News, then the HR News database will open etc.

The code below is a mess and will not work I know, but hopelly it will sort of give an idea of what I am trying to do.

I'm open to suggestions on better ways of achieving this etc. and appreciate any help you can provide.

Sub Queryopendocument(Source As Notesuiview, Continue As Variant)
	Dim sess As New NotesSession	
	Dim ws As NotesUIWorkspace
	Dim db As NotesDatabase
	Dim CurrentServer As String
	Dim doc As Notesdocument
	Dim folder As String
	Dim dbname As String
	
	Dim uidoc As NotesDocument
	Dim uiview As NotesUIView
	
	Continue = False ' Don't open the document
	
	Set ws = New NotesUIWorkspace
	Set db = sess.CurrentDatabase
	
	' Need to now get the selected view entry, take whatever is displayed in column 2 and then perform a lookup on this value
	' When we find the database in our catalog we can open by replicaID or folder / filepath
	
	folder = uidoc.FieldGetText("Folder")
	dbname = uidoc.FieldGetText("DatabaseName")
	
	CurrentServer = db.Server
	
	Call ws.OpenDatabase(CurrentServer, folder & "/" & dbname)
End Sub

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:

Select all Open in new window

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

Plus...

30 Day FREE access, no risk, no obligation
Collaborate with the world's top tech experts
Unlimited access to our exclusive solution database
Never be left without tech help again

Subscribe Now

Asked On: 2009-06-18 at 04:43:35ID24502069
Tags: Lotus Notes
,; lotusscript
Topics: Lotus Notes
,; Lotus Domino Email Server
Participating Experts: 2
Points: 500
Comments: 21

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

"The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
"Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
"Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Free Tech Articles

WARNING: 5 Reasons why you should NEVER fix a computer for free.
It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
SCCM OSD Basic troubleshooting
SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
Create a Win7 Gadget
This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
Outlook continually prompting for username and password
There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
Backup Exchange 2010 Information Store using Windows Backup
There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

Avoiding Bugs in Microsoft Access
Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
Top 10 Best New Features in Visio 2010
Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
IT Consultant Business Secrets Revealed
Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
Disaster Recovery and Business Continuity
Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
Organize Your Visio Diagrams with Containers and Lists
Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
How to Us Objects, Properties, Events and Methods in Microsoft Access
Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Watch More

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

by: tomsparrowPosted on 2009-06-18 at 05:16:38ID: 24656609

Your script is pretty close actually.

Since the ReplicaID field seems to store a date (at least in my catalog file), I've stuck with usign the filepath to open the DB.
It depends on the DB existing on the current server and having the same path as the server that updated the catalog file. You can swap CurretnServer to Doc.Server(0) if you want the original server.

Otherwisehis seems to do the trick:

	Dim sess As New NotesSession    
	Dim ws As NotesUIWorkspace
	Dim db As NotesDatabase
	Dim CurrentServer As String
	Dim doc As Notesdocument
	
	Continue = False ' Don't open the document
	
	Set ws = New NotesUIWorkspace
	Set db = sess.CurrentDatabase
	Set Doc = Source.Documents.GetFirstDocument	
	CurrentServer = db.Server
	
	Call ws.OpenDatabase(CurrentServer, doc.PathName(0))

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:

Select all Open in new window

by: shuboarderPosted on 2009-06-18 at 06:45:50ID: 24657311

Hi tomsparrow,

this gets me 1 step closer, so thanks for that.
This script is now opening the database (e.g. HR Database) rather than the catalog document, but I'm after the related database (e.g. HR News) which I guess is a lot trickier!

There are 5 fields in our catlog document:

relateddatabase1
relateddatabase2
relateddatabase3
relateddatabase4
relateddatabase5

These fields display the name of the related database.

How can we establish which related database they are clicking on in the view?

by: tomsparrowPosted on 2009-06-18 at 07:05:55ID: 24657468

It's going to be a lot easier to implement if you reverse the lookup mechanism on the catalog forms:
From your original example add an entry for 'HR Database' into the catalog entries for HR News and HR statistics.

You can get a handle to the document in the UIView, but where you have multiple lines for a single doc I don't think you can distinguish between them, so you probably won't be able to work it that way round.

Also, I'd avoid having 5 separate fields - just use 1 multi-value field, otherwise you just know that someone's going to come along and want a 6th entry in a list somewhere. You'll only need the DB title in this scheme, so there's no problem with that approach.

by: shuboarderPosted on 2009-06-18 at 07:43:51ID: 24657925

Thanks for the advice. I was wondering whether it might be possible to do something with the view selection formula / layout to achieve a similar result?

e.g. Somehow get it to display the catalog documents that are related to the current db title without the need to categorise

by: tomsparrowPosted on 2009-06-18 at 07:58:48ID: 24658095

If you're using a view, then a categorised approach is what I would so.

The other option is to stick to your original way round (i.e. have the related DBs listed on the central record) and do a @DBLookup on the page to get the whole list. You can then use that list to create links on the page by a variety of means, and lookup back to the catalog against the related DB title for the info to open it when requested.

You could use passthrough HTML (computed when composed) or checkbox, listbox type fields with an onchange.

Personally, I'd stick with the view and reverse the lookup - it's more work to populate, but a lot easier to use once it's running.
If there's a lot of databases to configure, you can always add a lookup button that lets you select multiple entries from the catalog view and stamp them all with the master DB title
e.g. select HR News and HR Statistics from the HR Database record (or even the HR Database application) and have it append 'HR Database' to the 'relatedDB' list.
That would be NotesUIWorkspace.PickListCollection with the Catalog view, then run through the NotesDocumentCollection and appendToText list for each one.

by: shuboarderPosted on 2009-06-18 at 08:33:22ID: 24658474

OK, I'm trying to understand what you mean by reverse lookup and how this would work.
Could you perhaps explain what my view should look like etc. to achieve this?

Thanks!

by: shuboarderPosted on 2009-06-18 at 08:36:13ID: 24658505

Oh, think I get it... you mean basically swap my columns around... Interesting! I'll have a play with this idea and see how it looks. thanks for your help!

by: tomsparrowPosted on 2009-06-18 at 08:39:30ID: 24658534

By reverse I just mean the opposite of what you have now.

Taking your example 'HR News' would have a field with the value 'HR Database' in it (replacing your 5 fields).

The view would then be pretty much the same - categorised by this new field, with the second column would show the HR News title (which is presumably the first column in your view).

When you double click on the row, you're now opening the 'HR News' document (and therefore with the original script the HR News database) rather than the 'HR Database' record.

by: tomsparrowPosted on 2009-06-18 at 08:39:56ID: 24658538

Post overlap - yes, exactly.

by: sjef_bosmanPosted on 2009-06-19 at 03:30:57ID: 24665121

You know you can add a Form formula to the embedded view? Using that formula, you can temporarily change the form that will be used to open the document. In the QueryOpen of that form you can do anything you like, even open documents in other databases. The document itself need not be opened, so set Continue to False.

The theory (not completely tested):
- create a Form called XYZ in your portal
- in the view, do nothing special, except for setting the Form formula to "XYZ"
- in the form XYZ, in the QueryOpen, find out what document is consulted, and open the intended database
- set Continue= False at the start of QueryOpen, so the document isn't opened

It's a different line of investigation, I know I might be barging in, and disturbing an almost finished solution, but I couldn't keep myself from telling...

by: shuboarderPosted on 2009-07-02 at 08:51:16ID: 24764670

Hi Sjef,

this sounds good, but I don't think ws.OpenDatabase can be called from within QueryOpen event of a form.
Is there a way around this issue?

by: tomsparrowPosted on 2009-07-02 at 08:57:21ID: 24764736

Not sure what you're gaining by opening from a form. You do need an extra form with essentially the code you're losing from the view in, and an extra formula in the view.
The initial problem still remains that you can't find the line clicked in the UIView, only the document.

I didn't know about ws.openDatabase from QueryOpen, but it sounds quite feasible. Of course that's just another reason to stick with opening from the view :)

Did you get anywhere turning the columns around?

by: sjef_bosmanPosted on 2009-07-02 at 08:58:57ID: 24764754

The idea is to write the following QueryOpen:

      continue= false ' so the current document won't be opened at all
      If isnewdoc Then Exit Sub
      Call DoLotsOfThingsWithThisDocument(soure.document)

Why would you like to call ws.OpenDatabase?

by: sjef_bosmanPosted on 2009-07-02 at 09:01:43ID: 24764780

Forget my last question... :-$ Just try it.

by: tomsparrowPosted on 2009-07-02 at 09:02:04ID: 24764785

That's the LotsOfThingsWithThisDocument he wants to do - open a database that's listed in the currentDocument.

by: sjef_bosmanPosted on 2009-07-02 at 09:03:29ID: 24764808

Exactly... Adding more :-$ :-$ :-$ :-$ :-$ :-$ ...

by: shuboarderPosted on 2009-07-02 at 09:20:02ID: 24764961

I guess i can use PostOpen instead... the only issue is you get to see the form after the database has opened.

by: sjef_bosmanPosted on 2009-07-02 at 09:22:57ID: 24764990

Indeed, that's why I use QueryOpen and Continue= false, from a dummy form that you only need for the QO and its name in the View formula...

by: tomsparrowPosted on 2009-07-02 at 10:30:49ID: 24765602

Not trying to be awkward, but I don't still see any benefit over the QueryOpenDocument, and it's more complex.

by: shuboarderPosted on 2009-07-15 at 05:57:38ID: 24858750

The only difference between the 2 is as follows:

using QueryOpen would mean that the dummy form would never be displayed on screen.
Using PostOpen works, but the dummy (blank form) is displayed when you tab browse back to the database you linked from (because the script runs after the document has opened)

The other problem with PostOpen is that you cannot execute the same link twice in succession since the document is already open (you will just get the dummy form).

Sjef's proposal of using a dummy form and a form formula has 1 great benefit and that is it negates the need to double click the links, so I would like to and persue this approach.

To conclude, it's very nearly there and both of your suggestions have been extremely useful.
I think if there is a way to open a database in the QueryOpenDocument event then it will be perfect

by: tomsparrowPosted on 2009-07-15 at 06:09:43ID: 24858854

QueryOpenDocument on the form or the view?
My original script way back there opens the DB from the view QueryOpen....

I'm not sure how you can open without double click on the form dummy approach, and not on the view QOD version though.

Not being awkward, I just see a solution that does everything you want in my first post (with the lookup modifications we talked about) and no need for a dummy form.

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

For individual users
Instant access to solutions
Ask your tech questions
Start your 30-day Free Trial

Business Accounts

Perfect for organizations
Multiple users
Save over 36%
Create a Business Account

Answer for Membership

Earn free access
Showcase your knowledge
No credit card required
Sign Up to Answer

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...

Question

Get values from view entries

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

Lotus Notes

lotusscript

Lotus Notes

Lotus Domino Email Server

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press