Hi.
I have a problem that I can’t resolve. I am creating a registration process for a client. The process creates a Contact (with ID=sequential number), Emergency Contact (ContactID field = ID from Contact), Sport Registration/Waiver Form (again with ContactID), goes off to Paypal for payment and returns to a Thankyou page (again with ContactID).
Linking these forms would be simple in MS Access, but seeing as I am working on Lotus Notes, I am trying to accomodate the following:
Create a view with information from all 4 forms, for example:
Sport, Name, City, Emegency Contact, Payment Status
by linking the ID=ContactID across all 4 forms. Thus far I have not had any success in doing this.
I would hate to have to program all the above 4 forms of information on one mammoth form as this process works great other than the reporting/view issue I now face.
Any ideas out there? Ideally, I need some way to run a query linking all 4 forms (from a view?) and displaying the fields of information necessary.
Thanks, in advance,
TH
Subject: Linking Views by Key - Is it Possible?
If you really must keep all the info parsed into 4 spearate forms and have them ‘linked’ via a common key what you can do is the following:
use a view that is sorted/categorized, then cateforize by the KEY field and then under each category you should see 4 documents that match
- if you want all the info to appear as ONE single row with bits of data from all four forms then you can create a table in a form. THe first coulumn in the table will have a multivalue field (separated by newline) and here the code will be a DBlookup to find all the KEYs (ContactID in this case).
The next column will be the same type of field except its code will be an @for(@dblookup using each contactID to find the data you want to show. so if its data from Form1 that you want to display just make your SELECT statement on the viewyour DBLookup is working off selects on FORM1, them column 3 can be data from FORM2, etc…
btw,Is this for the client or web?
HTH
ST
Subject: RE: Linking Views by Key - Is it Possible?
Hi. Thanks for the response. Something to consider. This will be primarily for the web as the software from a user’s perspective will be on the web.
Could I do the same with @DBLookup in the columns of the view and using the key=ID for each?
TH
Subject: RE: Linking Views by Key - Is it Possible?
No impossible, @dblookup does not work at the view level. to get a single row showing all data you have to use a table like i explained, or of course, you can use DB2 as your datastore like Andre suggested. Another alternative to ‘display’ this data is using a reporring tool with NotesSQL (like Cognos, or Crystal).
Using NotesSQL to connect to the NSF File you can do some ‘relation’ types reporting off the database and have it displayed in your preffered reporting tool.
ST
Subject: RE: Linking Views by Key - Is it Possible?
No. If major client-side JavaScript is an option, there are any number of ways to do client-side “joins” in the browser with data from two or more views, but you absolutely cannot do lookups within the views themselves. Notes is not relational, and the fact that there is no tabular structure to hop through makes lookups relatively expensive – far too expensive to do, say, 10,000+ times while indexing a view for use – so it is a prohibited operation. DB2 Query Views in Domino 7 (with the optional DB2 storage mechanism) will permit joins, but you can’t do that in the classic NSF.
Subject: RE: Linking Views by Key - Is it Possible?
Are you really using All Versions, or is there a specific version which is your development target? You can’t do this in version 6, but it is possible in version 7 using a DB2 Query View. I demoed it for Lotusphere, and it was taped, in case you want to view the session.
Of course, you ca always create hidden computed fields that contain copied of the information you want in the view via @DbLookup, but keeping the copies up to date is a pain.