Relational type Documents
I would like to know how I could best implement the following solution. I have a Survey being filled out over the Web. It contains an area where the user can select multiple answers. When viewing the results of the survey thru a view I will see the field with multiple answers in it. (ie. The question might be: What activities are you interested in? with possible answers: Hockey, Football, Tennis) So if the user selected Hockey and Tennis, the field containing this information would display "Hockey, Tennis". What I would like to happen is that another document would be created, containing these selections, linked to the main survey document. Like a relational database with multiple tables would be like. The main survey document would be a parent to 2 child documents containing the activities. The reason is that my client wants to use MS Access to report on the data filled out and it would be eaiser for them to work with this data this way. (Simple join the two documents to list all activities a user is interested in.) What is the best way to do this. I'm using Domino r5 and it is a Web based solution.
You could create a lotusscript agent so that when the survey is saved the agent creates a new doc for each value checked off. So, when exported, in access you would have a survey table with all the primary info then you would have a sports table that would contain the person's name and a list of sports. Now, the method you use to get the data into access is a separate issue.
ASKER
I'm not worried about getting the data to access. I'm simply using the Lotus ODBC driver. I am more interested in the background agent idea. Do you have an example. Remember the parent child relationship. When this agent runs it needs to link them based on some id from the parent document. My experience with agents is extremly limited. Is it possible to have an example using JavaScript instead of LotusScript?
What you might try doing is for that view column, select 'Display Multiple Values as Seperate Entries'. That way, when a user selects Tennis and Hockey, it appears that there are two documents. When you export this data from the view, it will export as two entries.
The view colume I am referring to is the one that shows the activities. In the properties for the activities view column, click on the sorting tab, and then check 'Show multiple values as seperate entries'.
Does it really need to have a parent-child relationship or can you make your view sort a particular way so that it sorts by the person's name and always shows surveys before it shows the sports... such as:
Joe Blow Survey Document
Hockey Document
Tennis Document
Frank Smith Survey Document
Football Document
Tennis Document
Joe Blow Survey Document
Hockey Document
Tennis Document
Frank Smith Survey Document
Football Document
Tennis Document
NOTE: The thing that would hold each group of docs together would be the persons name on each document.
Try this; using the WebQuerySave event call an agent. Use this code for the agent. The code should create a new doc for each sport that is selected:
Dim db As New notesdatabase("server","di
Dim doc As NotesDocument
Dim SportDoc As NotesDocument
Set doc = session.DocumentContext
Forall x In doc.SportChoices
Set SportDoc = New NotesDocument ( db ) SportDoc.Form = "SportForm"
SportDoc.FName = doc.FName
SportDoc.LName = doc.LName
SportDoc.Sport = x
Call SportDoc.Save(False, True)
Dim db As New notesdatabase("server","di
Dim doc As NotesDocument
Dim SportDoc As NotesDocument
Set doc = session.DocumentContext
Forall x In doc.SportChoices
Set SportDoc = New NotesDocument ( db ) SportDoc.Form = "SportForm"
SportDoc.FName = doc.FName
SportDoc.LName = doc.LName
SportDoc.Sport = x
Call SportDoc.Save(False, True)
ASKER
snocross.
Where do I put this code. When I create an agent, assuming this is lotusscript your giving me, It gives me a place to put code called "Options", "Declarations", "Initialize" and "Terminate". Also, instead of creating a new database with this information I would like to use the current database, using a different form. Is this possible?
Netrat.
Your solution would work, except that I have many survey questions that will have multiple answers. It seems like it probably would be more effecient to simply create a seperate entry using only the required index and data columns.
Where do I put this code. When I create an agent, assuming this is lotusscript your giving me, It gives me a place to put code called "Options", "Declarations", "Initialize" and "Terminate". Also, instead of creating a new database with this information I would like to use the current database, using a different form. Is this possible?
Netrat.
Your solution would work, except that I have many survey questions that will have multiple answers. It seems like it probably would be more effecient to simply create a seperate entry using only the required index and data columns.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No comment has been added lately, so it's time to clean up this TA. I will leave a recommendation in the Cleanup topic area that this question is:
Answered by: snocross
Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
Ranjeet Rain
EE Cleanup Volunteer
Answered by: snocross
Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
Ranjeet Rain
EE Cleanup Volunteer
per recommendation
SpideyMod
Community Support Moderator @Experts Exchange
SpideyMod
Community Support Moderator @Experts Exchange
Format the view in such a way that you can export this to an excel sheet.
File - Export - Type 1-2-3 worksheet and file name with extn as .wk4
Export the whole view to Excel and then use MS Access to import this excel file and do whatever analysis you want.
In my last project I did the same thing.
But with R4.6 . . . .
Good Luck !
- Arun