Data not yet requested,Data requested,Partially consolidated,Consolidation completed,In progress,Ready to consolidate,Do not consolidate,Data requested,Consolidated into master
Not Yet Distributed,Data Requested,Partially Consolidated,Consolidation Complete,In Progress,Ready to Consolidate,Do Not Consolidate,Data Requested,Consolidation Complete
Temp_Value
$16$21$20$Conflict$REF
$16Unique ID
0S0E
$21TitleH
WorksheetTitle
Title
3S5S
$20Status
Temp_Status
Worksheetstatus
StatusX
1S16S
Temp_Value
Temp_Status
Temp_Status
Temp_Status
0R1S2S
Not Yet Distributed,Data Requested,Partially Consolidated,Consolidation Complete,In Progress,Ready to Consolidate,Do Not Consolidate,Data Requested,Consolidation Complete
Data not yet requested,Data requested,Partially consolidated,Consolidation completed,In progress,Ready to consolidate,Do not consolidate,Data requested,Consolidated into master
'++LotusScript Development Environment:2:5:(Options):0:74
Option Public
'++LotusScript Development Environment:2:5:(Forward):0:1
Declare Public Type Assignment
Declare Public Type Request
Declare Function DoRequestAction ()
Declare Sub Distribute (hWorkBook As Variant, assign As Assignment, requestInfo As Request, parentDB As NotesDatabase, thisDoc As NotesDocument)
Declare Sub SendMailWithDocLink (parentDoc As NotesDocument, linkDoc As NotesDocument, assigned As Assignment, requestInfo As Request)
Declare Public Function StringConvert(Byval InitialString As String, ConvertType As Integer) As Variant
Declare Function ActivateMaster(thisUIDoc As NotesUIDocument) As Variant
Declare Function GetMasterEmbeddedObject (thisDoc As NotesDocument) As NotesEmbeddedObject
'++LotusScript Development Environment:2:5:(Declarations):0:10
' UI entry points
' * * * --> Note to translators: need to change UIDLL_NAME to match the dll name for each country
Const UIDLL_NAME = "tcui10en.dll"
' collect which kind of documents?
Declare Public Function GetConsolidateInfo Lib UIDLL_NAME Alias "GetConsolidateInfo" (Byval hInst As Long, pbCompleteOnly As Long, pbDeleteContributions As Long) As Long
' share now or later
Declare Public Function ShareDocument Lib UIDLL_NAME Alias "ShareDocument" (Byval hInst As Long) As Long
' get is the status of the contribution
Declare Public Function GetCollectionStatus Lib UIDLL_NAME Alias "GetCollectionStatus" (Byval hInst As Long, pCollectionStatus As Long) As Long
' which spreadsheet to use, existing or new
Declare Public Function BrowseForSpreadSheet Lib UIDLL_NAME Alias "BrowseForSpreadSheet" (Byval hInst As Long, pFilename As String, _
pNewOrExsisting As Long, pTitle As String ) As Long
' assign worksheets to users
Declare Public Function AssignWorkSheet Lib UIDLL_NAME Alias "AssignWorkSheet" _
(Byval hInst As Long, pSheetList As Any, pAssignmentList As Any, pNotifyAssigned As Long, pAccessFlag As Long, pMailMessage As String, pNotifyDistrib As Long ) As Long
' distributed workbook warning
Declare Public Function DistributeWarning Lib UIDLL_NAME Alias "DistributeWarning" (Byval hInst As Long, pShowAgain As Long) As Long
' -- constants
' * * * these constant values should not be changed * * *
Const EXISTING_FILE = 1
Const NEW_FILE = 2
Const DISTRIBUTE_NOW = 1
Const DISTRIBUTE_LATER = 2
Const CANCEL = 0
Const SUCCESS = 1
Const FAILURE = 2
Const STATUS_IN_PROGRESS = "1"
Const STATUS_READY_CONS = "2"
Const STATUS_DONT_CONS = "3"
Const STATUS_DATA_REQUESTED = "101"
Const STATUS_CONS_IN_MASTER = "102"
Const NOTIFICATION_PENDING = "1"
Const CONTRIBUTOR_NOTIFIED = "2"
Const UPDATE_IN_PROGRESS = "1"
Const UPDATE_COMPLETE = "4"
Const IGNORE = "5"
Const CONSOLIDATED_INTO_MASTER = "6"
Const NOT_YET_DISTRIBUTED = "1"
Const MASTER_DISTRIBUTED = "2"
Const PARTIALLY_CONSOLIDATED = "3"
Const CONSOLIDATION_COMPLETE = "4"
Const CONVERT_TO_ARRAY = 1
Const CONVERT_TO_TEXTLIST = 2
' -- error codes
Const WORKBOOK_PASSWORDED = 257 ' 0x101
Const COLLECT_PAGE_NOT_FOUND = 258 ' 0x102
' -- Strings
' -- Form names
Const CONTRIBUTOR_FORM = "Secondary"
Const CONTRIBUTOR_FORM_ALIAS = "Contributor"
Const MASTER_FORM = "Main"
Const MEMO_FORM = "Memo"
' -- View names
Const CONSOLIDATE_VIEW = "(Internal Consolidate)"
' -- * * * Strings to be translated * * *
Const MAIL_SUBJECT = "You have been assigned sheet --> " ' header for the subject of the mail with doc link
Const DOCLINK_TEXT = "Follow this link to your assigned sheet. " ' user sees this when clicking on doclink
Done'++LotusScript Development Environment:2:5:(Options):0:66
'++LotusScript Development Environment:2:5:(Forward):0:1
Declare Sub Click(Source As Button)
'++LotusScript Development Environment:2:5:(Declarations):0:2
'++LotusScript Development Environment:2:2:BindEvents:1:129
Private Sub BindEvents(Byval Objectname_ As String)
Static Source As BUTTON
Set Source = Bind(Objectname_)
On Event Click From Source Call Click
End Sub
'++LotusScript Development Environment:2:2:Click:1:12
Sub Click(Source As Button)
Dim workSpace As New NotesUIWorkspace
Dim UIDoc As NotesUIDocument
Set UIDoc = workSpace.CurrentDocument
' close the document, Notes will prompt for whether to save or not
UIDoc.Close
End Sub
O=Lotus Notes
O=Lotus Notes
PURSAFO
|.:#U
O=Lotus Notes
CN=Lotus Notes Template Development/O=Lotus Notes
PURSAFO
Fde!f^^
CreateTime
$TITLE
$AUTOLAUNCH
$Info
$SubForms
$WindowTitle
$Script
$$Script_O
$$ScriptName
$$FormScript
$$$FormScript_O
ForContributorsOnly
NotifyDistributor
SessionTimes
$Body
$ACTIONS
$SCRIPTOBJ_6
Times New Roman
VersionOpt|
6S7S9S14S
$VersionOpt
0R1S2S3S
SavedOnce
0R1S2S3S
PostedDate(
0R1S2S3S
DeliveredDate
0R1S2S3S
$AutoEditMode
0R1S2S3S
MailSaveOptions
0R1S2S3S
VersionOpt
0R4S
New Memo
Subject
Subject
Subject
4S6S12S13S14S15S17S21S23S25S26S28S
'++LotusScript Development Environment:2:5:(Options):0:74
Option Public
%INCLUDE "lsconst.lss"
'++LotusScript Development Environment:2:5:(Forward):0:1
Declare Sub InstantiateObjectVariables(source)
Declare Sub SaveDialog(SavedDoc)
'++LotusScript Development Environment:2:5:(Declarations):0:10
Dim w As NotesUIWorkspace
Dim s As NotesSession
Dim db As NotesDatabase
Dim note As NotesDocument
Dim newnote As NotesDocument
Dim Action As Variant
Dim DoNotClose As Variant
Dim ContinueSave As Variant
Dim NewDocument As Variant
'++LotusScript Development Environment:2:2:InstantiateObjectVariables:1:8
Sub InstantiateObjectVariables(source)
Set w = New NotesUIWorkspace
Set s = New NotesSession
Set db = s.CurrentDatabase
Set note = source.document
source.AutoReload = False
End Sub
'++LotusScript Development Environment:2:2:SaveDialog:1:8
Sub SaveDialog(SavedDoc)
SavePrompt = Messagebox("Do you wish to save this " & SavedDoc & "?", MB_YESNOCANCEL, "Save " & SavedDoc)
Select Case SavePrompt
Case IDCANCEL
note.MailOptions = "0"
DoNotClose = True
ContinueSave = False
Exit Sub
Case IDNO
note.SaveOptions = "0"
Exit Sub
Case IDYES
note.SaveOptions = "1"
End Select
End Sub
}P Ih
Memo'++LotusScript Development Environment:2:5:(Options):0:66
'++LotusScript Development Environment:2:5:(Forward):0:1
Declare Sub Postopen(Source As Notesuidocument)
Declare Sub Querysave(Source As Notesuidocument, Continue As Variant)
Declare Sub Queryclose(Source As Notesuidocument, Continue As Variant)
Declare Sub Querymodechange(Source As Notesuidocument, Continue As Variant)
Declare Sub Queryopen(Source As Notesuidocument, Mode As Integer, Isnewdoc As Variant, Continue As Variant)
'++LotusScript Development Environment:2:5:(Declarations):0:2
'++LotusScript Development Environment:2:2:BindEvents:1:129
Private Sub BindEvents(Byval Objectname_ As String)
Static Source As NOTESUIDOCUMENT
Set Source = Bind(Objectname_)
On Event Postopen From Source Call Postopen
On Event Querysave From Source Call Querysave
On Event Queryclose From Source Call Queryclose
On Event Querymodechange From Source Call Querymodechange
On Event Queryopen From Source Call Queryopen
End Sub
'++LotusScript Development Environment:2:2:Postopen:1:12
Sub Postopen(Source As Notesuidocument)
If note Is Nothing Then Call InstantiateObjectVariables(source)
If note.HasItem("PostedDate") And source.EditMode = False Then
Exit Sub
Else
If source.InPreviewPane Then
Exit Sub
Else
source.EditMode = True
End If
End If
If source.IsNewDoc Then
NewDocument = True
EditType = s.GetEnvironmentValue("MailStEd")
'EditType 1 = CreateStationery
If EditType = "1" Then
note.tmpAction = "SaveAsStationery"
note.IsMailStationery = 1
Call s.SetEnvironmentVar("MailStEd", "0")
note.MailOptions = "0"
note.SaveOptions = "1"
source.Reload
source.RefreshHideFormulas
End If
Else
NewDocument = False
If note.HasItem("IsMailStationery") Then
EditType = s.GetEnvironmentValue("MailStEd")
'EditType 2 = EditStationery
If EditType = "2" Then
Call s.SetEnvironmentVar("MailStEd", "0")
note.MailOptions = "0"
note.SaveOptions = "1"
Else
'Create a doc from Stationery
note.RemoveItem("IsMailStationery")
note.RemoveItem("MailStationeryName")
Call note.ReplaceItemValue("$VersionOpt", "6")
End If
Else
EditType = s.GetEnvironmentValue("MailStEd")
'EditType 5 = EditNewCopy
If EditType = "5" Then
Call note.ReplaceItemValue("$VersionOpt", "6")
Call s.SetEnvironmentVar("MailStEd", "0")
End If
End If
source.Reload
source.RefreshHideFormulas
End If
End Sub
'++LotusScript Development Environment:2:2:Querysave:1:12
Sub Querysave(Source As Notesuidocument, Continue As Variant)
DoNotClose = False
If note Is Nothing Then Call InstantiateObjectVariables(source)
'Remove these because they are explicitly set in this event
note.RemoveItem("MailOptions")
note.RemoveItem("SaveOptions")
Action = note.tmpAction
If note.HasItem("IsMailStationery") Then
If Action(0) <> "RenameStationery" Then
ContinueSave = True
Call SaveDialog("Stationery")
If ContinueSave = False Then
Continue = False
Exit Sub
End If
End If
End If
Select Case Action(0)
Case "SaveAsStationery"
sName = Inputbox$("What would you like to call this Stationery?", "Save as Stationery", "-Untitled-")
If sName = "" Then
note.MailOptions = "0"
note.RemoveItem("tmpAction")
DoNotClose = True
Continue = False
Exit Sub
End If
If NewDocument Then
note.IsMailStationery = 1
note.MailStationeryName = sName
note.MailOptions = "0"
note.SaveOptions = "1"
Else
note.SaveOptions = "0"
Set newnote = New NotesDocument(db)
Call note.CopyAllItems(newnote)
ItemList = newnote.Items
Forall i In ItemList
If Lcase(Left(i.Name, 3)) = "tmp" Then i.Remove
End Forall
newnote.IsMailStationery = 1
newnote.MailStationeryName = sName
newnote.Form = "Memo"
newnote.RemoveItem("PostedDate")
newnote.RemoveItem("DeliveredDate")
newnote.Save True, True
w.ViewRefresh
End If
Messagebox "This Message has been saved as Stationery in your Drafts folder. A new message will be created every time you open this Stationery.", 0, "Save as Stationery"
Case "RenameStationery"
MailStationeryName = note.MailStationeryName
sName = Inputbox$("What would you like to call this Stationery?", "Save as Stationery", MailStationeryName(0))
If sName = "" Then
note.MailOptions = "0"
note.RemoveItem("tmpAction")
DoNotClose = True
Continue = False
Exit Sub
End If
note.MailStationeryName = sName
note.MailOptions = "0"
note.SaveOptions = "1"
Case "SaveAsDraft", "SaveAndFile", "SendAndFile", "ConvertNewDoc"
note.MailOptions = "0"
note.SaveOptions = "1"
Case "Send"
note.MailOptions = "0"
note.SaveOptions = "0"
Case "ManualSave"
ContinueSave = True
Call SaveDialog("Document")
If ContinueSave = False Then
Continue = False
Exit Sub
End If
Case "ConvertToTask"
note.RemoveItem("DeliveredDate")
note.RemoveItem("PostedDate")
note.SendTo = ""
note.CopyTo = ""
note.SaveOptions ="1"
note.MailOptions = "0"
note.Form = "Task"
note.AssignState = 0
note.ExcludeFromView = "D"
Call note.ReplaceItemValue("_ViewIcon", 168)
End Select
note.RemoveItem("tmpAction")
If note.HasItem("$VersionOpt") Then Call note.ReplaceItemValue("$VersionOpt", "0")
source.Reload
End Sub
'++LotusScript Development Environment:2:2:Queryclose:1:12
Sub Queryclose(Source As Notesuidocument, Continue As Variant)
If DoNotClose Then
DoNotClose = False
Continue = False
End If
End Sub
'++LotusScript Development Environment:2:2:Querymodechange:1:12
Sub Querymodechange(Source As Notesuidocument, Continue As Variant)
If note Is Nothing Then Call InstantiateObjectVariables(source)
'The following covers the case where the doc is opened in read mode
'the placed in edit mode.
'See comments in queryopen
FromName = note.From
If FromName(0) = s.Username Then
note.RemoveItem("MailOptions")
note.RemoveItem("SaveOptions")
Else
note.MailOptions = "0"
note.SaveOptions = "1"
note.tmpAction = "ManualSave"
End If
source.Reload
End Sub
'++LotusScript Development Environment:2:2:Queryopen:1:12
Sub Queryopen(Source As Notesuidocument, Mode As Integer, Isnewdoc As Variant, Continue As Variant)
If mode = 0 Then Exit Sub
If Isnewdoc Then Exit Sub
If note Is Nothing Then Call InstantiateObjectVariables(source)
'The following covers the case where the doc is opened in edit mode
'If you wrote this memo remove MailOptions and SaveOptions
FromName = note.From
If FromName(0) = s.Username Then
note.RemoveItem("MailOptions")
note.RemoveItem("SaveOptions")
Else
'If received by you then you can manually save by not mail
You cannot convert this message into a Task because it already has a due date associated with it.
3S4S6S12S18S
tmpAction
ConvertNewDoc
Unavailable
0R1S2S3S7S9S
0R4S9S13S
0R4S
_Remove From Folder...
Categori_ze
SSend
O=Lotus Notes
O=Lotus Notes
PURSAFO
|.:#U
O=Lotus Notes
CN=Lotus Notes Template Development/O=Lotus Notes
PURSAFO
Fde!f^^
$$FormPostOpenAction
$TypeIcon
ExpireDate
ReplyDate
ComposedDate
$TITLE
$Info
$WindowTitle
$Script
$$Script_O
$$ScriptName
$$FormScript
$$$FormScript_O
$Body
$ACTIONS
WorksheetTitle"
Title
3S5S
SessionEditors
Contributor
SessionEditors
3S4S24S
Temp_Status
Worksheetstatus
StatusX
1S16S
Temp_Value
Temp_Status
Temp_Status
Temp_Status
0R1S2S
Data not yet requested,Data requested,Partially consolidated,Consolidation completed,In progress,Ready to consolidate,Do not consolidate,Data requested,Consolidated into master
Temp_Value
$12$28$26$23$8$10$Conflict$REF$C1$
$FormPrivs$F
$28TitleT
WorksheetTitle"
Title
3S5S
$26Contributor(s)
SessionEditors
Contributor
SessionEditors
3S4S24S$23Status
Temp_Status
Worksheetstatus
StatusX
1S16S
Temp_Value
Temp_Status
Temp_Status
Temp_Status
0R1S2S
Data not yet requested,Data requested,Partially consolidated,Consolidation completed,In progress,Ready to consolidate,Do not consolidate,Data requested,Consolidated into master
Data not yet requested,Data requested,Partially consolidated,Consolidation completed,In progress,Ready to consolidate,Do not consolidate,Data requested,Consolidated into master
Temp_Value
$21$28$26$23$10$Conflict$REF$C1$
View by Date
CN=Cathy Pfeiler/O=TCGEMINI2
0S0E
WorksheetTitle"
Title
3S5S
SessionEditors
Contributor
SessionEditors
3S4S24S
Temp_Status
Worksheetstatus
0S0E
$28TitleT
WorksheetTitle"
Title
3S5S
$26Contributor
SessionEditors
Contributor
SessionEditors
3S4S24S$23Status
Temp_Status
Worksheetstatus
StatusX
1S16S
Temp_Value
Temp_Status
Temp_Status
Temp_Status
0R1S2S
Data not yet requested,Data requested,Partially consolidated,Consolidation completed,In progress,Ready to consolidate,Do not consolidate,Data requested,Consolidated into master
TeamConsolidate is based on a .DLL and a Notes database template, with forms containing scripts created using the LotusScript language. These scripts are associated with various actions and buttons and use the type libraries, objects, methods, and properties from 1-2-3. The user interface of the TeamConsolidate database is provided by the TeamConsolidate .DLL, TCUI10EN.DLL.
TeamConsolidate uses Notes documents with 1-2-3 sheets as embedded objects. You cannot substitute another application for 1-2-3.
Access control
By default, all users of the TeamConsolidate database receive Author access to prevent unauthorized editing in the database and ensure that the feature functions properly. The originator of a master workbook needs to create the master document. After consolidation,
the access rights for the response documents are set to Reader to prevent further change.
The originator can consolidate response documents into the master workbook document, but if you want other users to be able to consolidate, they must have Editor access or higher.
Suggestions for modifications
You can use all of the TeamConsolidate features without altering the forms and views of a TeamConsolidate database. However, you can add forms, views, and documents to suit your needs. For example, you could change the user interface so that an agent checks to see if distributed sheets are marked ready to consolidate and, if so, automatically initiates the consolidation process.
Scripts
To preserve the basic functionality of TeamConsolidate, exercise caution if you edit existing scripts. However, you can add new scripts to the forms to perform various tasks according to your needs. For more information about LotusScript, consult your LotusScript documentation.
Views
You can add or edit views as you like. However, the TeamConsolidate database contains two hidden views: (Internal Consolidate) and (Parent ID). These are required views that you must not remove or modify.
Forms
The TeamConsolidate template contains three forms: Document with 1-2-3 workbook, Contributor, and Memo.
Document with 1-2-3 workbook -- The form containing the master workbook.
Note
Do not change the choices for the Status field, which are required for TeamConsolidate to function properly.
Contributor -- The response form that contains the distributed sheet. You can customize this form if you like. For example, you can change this form so that the embedded workbook does not launch automatically when you open a response document. To change this, open the Contributor form, go to the Launch tab in the InfoBox for the form, and deselect "Launch in place." Do not change the choices for the WorkSheetStatus field. These are required for TeamConsolidate to function properly.
Memo -- The standard e-mail form used in most Notes databases.
You can customize any of these forms to suit your needs. For example, you might want to add fields for the project name, due dates, or estimated costs.
Copying master and reponse documents
You can copy master documents or databases that contain only master documents, but you should avoid copying response documents. Response documents are linked to their master documents through the $REF field. Therefore, after you distribute sheets, any copies of response documents will refer to the original master document in the original database.
To prevent this confusion, do not use File - Database Copy to make copies of a TeamConsolidate database that has response documents, or use Edit - Copy and Edit - Paste to make copies of response documents.
If you want to make a copy of a database, for example, to use as a boilerplate for a new database, do so before you distribute sheets.
Note
If you replicate the database, the correct relationship between response and master documents is preserved.
Replication conflicts
If you try to consolidate a response document that has a replication conflict, you will get an error message. To see the documents with replication conflicts, use the view "By Contributor."
Editing the "About Database" and "Using Database" documents
Change the "About Database" and "Using Database" documents to reflect any changes you make. To edit these documents, choose Design - Other.
For Users
Overview: Distributing workbooks with TeamConsolidate
TeamConsolidate lets you distribute workbook sheets in a Lotus Notes database to request data or modifications from contributors. After they make changes, you can consolidate these sheets back into your original workbook.
For example, suppose you're using 1-2-3 to track expenses on a project involving several departments. You can create a workbook containing a sheet for each department. Using this database, you distribute each sheet to people in each department. They make updates and additions and then you can consolidate the changes into the original workbook. Storing the workbook in a Notes database makes the information available to everyone--even people at different locations--and can help you track changes over time.
How to distribute sheets and consolidate changes into your original document
The process for distributing the sheets of a workbook to contributors involves a single distributor and any number of contributors. The general procedure is outlined below.
A person familiar with designing and administering Notes databases sets up a Notes database that will contain the master workbooks and distributed sheets. This database must use the TeamConsolidate template, named TEAMCONS.NTF, which comes with 1-2-3.
The originator opens the Notes database in which to store the workbook.
In the database, the originator chooses Create - 1-2-3 Workbook Document, and either creates a new workbook or opens an existing one.
Next, the originator assigns individual sheets of the workbook to one or more contributors and notifies contributors in an e-mail message.
Next, contributors open the Notes database and edit the distributed sheets. When contributors finish adding data, they mark their documents as ready for consolidation and save them back to the Notes database.
After all contributors mark their documents "Ready for consolidation," the originator clicks the Consolidate Workbook Data button to consolidate contributors' changes into the master workbook.
After the changes are consolidated, the originator can save the master workbook as a separate 1-2-3 file or leave the workbook stored in the Notes database.
Overview: Setting up the master workbook
When you use TeamConsolidate, you work with 1-2-3 as you normally would. Because contributors will be working with only one sheet at a time, certain features of 1-2-3 are less important or useful, such as features that work across sheets.
Below is information to keep in mind when creating the master workbook and working in distributed sheets.
Setting up each sheet
When you are distributing sheets from a workbook with multiple sheets, put all the data and graphic objects that you want to distribute to one person on a single sheet. If a workbook contains macros, it is best to put these on a separate sheet.
Charts, drawings, maps, and OLE embedded objects
During consolidation, if separate distributed sheets contain charts, drawings, maps, or embedded objects with the same names, the objects with the duplicate names are not renamed. No objects or data will be lost, but to avoid confusion, before you distribute a sheet, assign names to objects rather than accepting the default names suggested by 1-2-3.
Versions and version groups
It is a good idea to avoid 3D versions in a distributed sheet. Although contributors can use data in multiple sheets, for example with formula calculations, 1-2-3 only consolidates the changes made on the single sheet that was distributed.
If the originator adds versions in the master workbook while sheets are distributed, the data in these versions will be lost during consolidation.
Version groups are not included in a distributed sheet.
Scripts
You can add, edit, and consolidate scripts that are attached to graphic objects on the current sheet, but no other scripts.
Note
If a script is attached to a range in a distributed sheet, the script will not be consolidated.
Locked workbooks
You can distribute, edit, and consolidate locked workbooks. The lock is ignored when the sheet is consolidated back into the master workbook.
Password-protected workbooks
Since you cannot password-protect an OLE object, you cannot password-protect a master workbook after it is in Notes.
Creating the master workbook from an existing workbook
Before you start, the Notes database for storing the workbook must be set up.
1. Open the Notes database where you want to store the 1-2-3 workbook.
2. Click the Create 1-2-3 Workbook Document button.
3. Enter a title for the master workbook document.
4. Select "Use an existing 1-2-3 workbook" and specify the file name.
5. Click OK.
6. Select whether to request data for the workbook now or later.
If you choose to distribute the workbook now, follow the steps, starting with step 3, in "Distributing sheets" later in this document.
If you choose to distribute the workbook later, when you are ready, click Request Workbook Data.
Creating a new master workbook
Before you start, the Notes database for storing the workbook must be set up.
1. Open the Notes database where you want to store the 1-2-3 workbook.
2. Click the Create 1-2-3 Workbook Document button.
3. Enter a title for the master workbook document.
4. Select "Create a new 1-2-3 workbook."
5. Click OK.
6. Set up and enter data in the 1-2-3 workbook object.
7. Click the Notes form outside the 1-2-3 workbook object when you finish creating the master workbook.
8. Close and save the Notes document.
You are now ready to distribute the sheets in the master workbook to contributors. See "Distributing sheets" below for information.
Distributing sheets
You distribute sheets to request data from contributors. Distributing sheets assigns them to contributors and makes each distributed sheet a response document under the master document.
1. Open the Notes document containing the workbook you want to distribute.
2. Click the Request Workbook Data button.
3. Select the sheets you want to distribute by dragging sheet names from the "Sheets in workbook" list to the "Sheets to distribute" list.
4. To choose contributors for each distributed sheet, click in the Contributors list and type a name or click the Addresses button to select names from an address book.
You can specify more than one contributor for each sheet. Separate names with a , (comma). You can also use group names to specify multiple contributors.
5. (Optional) Select "Only contributor(s) may view distributed sheets" to prevent others from seeing and entering data in the distributed sheets.
6. (Optional) To send e-mail to all contributors, select "Send mail with doclink to contributors," then enter your message in the box.
7. (Optional) Select "Notify me when contributors have finished" if you want to receive a message when documents are marked "Ready to consolidate" or "Do not consolidate."
8. Click OK, and enter your e-mail password (if prompted).
After you close the document, response documents containing the distributed sheets appear beneath the master document, which has the status "Data requested."
Details: Distributing sheets
When you can distribute sheets
You can distribute sheets from a master workbook document that has a status of "Data not yet requested" or "Consolidation completed." If a master workbook document has any other status, the Request Workbook Data button is not available.
After you distribute a sheet, you cannot distribute it again until the master workbook document has a status of "Consolidation completed." Consolidation is completed when all distributed sheets have a status of "Consolidated into master" or "Do not consolidate."
Choosing a view
In the TeamConsolidate database, you can choose from several ways to view the master workbook documents and the distributed sheets, which appear as response documents to the master workbook document.
Displaying the TeamConsolidate Navigator
You can click the Navigator button to display a row of buttons on the left side of the workbook window that replace the standard Notes navigators. The TeamConsolidate navigator contains these buttons:
All Documents -- Displays all documents in the view by originator name
By Status -- Displays all documents by status of master workbooks
By Date -- Displays all documents by modification date of master workbooks
By Contributor -- Displays distributed sheets by contributor name, but does not display the master workbooks
Editing a distributed sheet
Contributors can edit distributed sheets
Editing an embedded object without leaving the application in which it is embedded. Rather, the menu changes to show commands from the server application. You can edit embedded objects in place or out of place.
in place
or
Editing an embedded object by opening the server application. You can edit embedded objects in place or out of place.
out of place
. When you open a document containing a distributed sheet, the sheet is activated for in-place editing.
Editing the sheet in place
1. In the Notes database, open the document containing the distributed sheet.
2. Make your changes or additions to the sheet.
3. (Optional) Enter a comment in the Remarks field.
4. Close and save the Notes document.
5. In the Status of Contributor Document dialog box, make a choice reflecting the status of your work.
Note
If you plan to add more changes later, select "In progress."
6. Click Done.
Editing the sheet out of place
1. In the Notes database, open the document containing the distributed sheet.
2. Click outside the 1-2-3 workbook object to deactivate it.
3. Click the object to select it.
4. Choose Workbook - Open.
The sheet appears in a full 1-2-3 window.
5. Make your changes or additions.
6. Choose File - Exit & Return to close 1-2-3 and go back to the Notes document.
7. (Optional) Enter a comment in the Remarks field.
8. Save and close the Notes document.
9. In the Status of Contributor Document dialog box, make a choice reflecting the status of your work.
If you plan to add more changes later, select "In progress."
10. Click Done.
Details: Editing a distributed sheet
Limitations when editing a distributed sheet
When you are editing a distributed sheet, certain 1-2-3 commands and features are unavailable.
You cannot use Edit - Go To to go to objects on other sheets.
You cannot create or delete sheets.
You cannot create version groups.
You cannot insert or delete rows or columns in a versioned range (unless you created the version in the distributed sheet).
You cannot add or change scripts attached to ranges.
Editing sheets in place
When you edit a sheet
Editing an embedded object without leaving the application in which it is embedded. Rather, the menu changes to show commands from the server application. You can edit embedded objects in place or out of place.
in place
, the File and Window menus contain Lotus Notes commands. File - Save stores the entire Notes document containing the sheet, not just the sheet.
You can end in-place editing by clicking in the Notes document outside of the sheet. To return to editing the sheet, double-click it.
The 1-2-3 data is not saved until you save the Notes document.
Editing sheets out of place
When you edit the sheet
Editing an embedded object by opening the server application. You can edit embedded objects in place or out of place.
out of place
, the File menu contains 1-2-3 commands.
Setting the status of your work
When you save a distributed sheet, a dialog box requests you to confirm or change the status of the document. You can change the status to "In progress," "Ready to consolidate," or "Do not consolidate."
Duplicate range names
When you edit a distributed sheet, all range names proposed by Version Manager (for example, Range 1) will include the sheet name as a prefix (by default, a letter). If 1-2-3 detects a duplicate range name while consolidating distributed sheets back into the original workbook, 1-2-3 keeps the range name in the first sheet, but discards the names of any other ranges with the same name.
Scripts in distributed sheets
If a script is attached to a range in a distributed sheet, the script will not be consolidated.
Embedding additional objects
You can embed objects in a distributed sheet and they will be merged back to the master workbook during consolidation.
Consolidating distributed sheets
After contributors make changes, you can consolidate distributed sheets back into the master workbook.
1. In the Notes database, open the document containing the master workbook.
2. Click the Consolidate Workbook Data button.
3. Specify whether Notes will consolidate just those documents marked "Ready to consolidate" and whether contributors' documents will be deleted after consolidation.
4. Click OK.
You see a message confirming that the distributed documents have been consolidated successfully.
5. (Optional) To save the 1-2-3 workbook document as a 1-2-3 workbook file, click the Detach 1-2-3 Workbook button.
Details: Consolidating distributed sheets
Replication conflicts in distributed sheets
You can consolidate original response documents only. If two people edit the same document at the same time, the second person to save it will create a replication conflict. You must resolve replication conflicts and delete the conflict document before you can consolidate distributed sheets. You can see replication conflict documents only in the view "By Contributor."
Controlling which sheets are consolidated
If you select "Consolidate only documents marked Ready to consolidate," only those documents with a status of "Ready to consolidate" will be consolidated. If sheets are marked "Do not consolidate," "In progress", or "Data requested," you can consolidate them later, after the contributors change their status. If you don't select "Consolidate only documents marked Ready to consolidate," all documents with a status other than "Do not consolidate" will be consolidated.
If sheets are marked "In progress" and "Consolidate only documents marked Ready to consolidate" is selected, then the master workbook document will get the status of "Partially consolidated." Once the remaining sheets are marked "Ready to consolidate" or "Do not consolidate," you can consolidate again to incorporate the remaining data. Then the status of the master workbook document changes to "Consolidation completed."
Deleting contributors' documents
If you select "Delete contributors' documents after consolidating," Notes deletes the response documents after consolidation. This, however, does not remove replication conflicts for response documents.
If you choose not to delete contributors' documents, they become read-only documents to prevent changes to sheets that have already been consolidated.
O=Lotus Notes
O=Lotus Notes
PURSAFO
|.:#U
O=Lotus Notes
CN=Lotus Notes Template Development/O=Lotus Notes
PURSAFO
Fde!f^^
cuP:E_
$Info
$Body
Times New Roman
Task List
&Switch To
About TeamConsolidate
?_?__
`!b`1
7`^///?
?b___
`bOOO
`<b!R
What does this database do?
The TeamConsolidate database lets you distribute 1-2-3 workbook sheets to contributors to request data or modifications. After contributors make changes, you can consolidate these sheets back into your original workbook.
For example, suppose you're using 1-2-3 to track expenses on a project involving several departments. You can create a workbook containing a sheet for each department. Using this database, you distribute each sheet to people in each department. They make updates and additions and then you can consolidate the changes into the original workbook.
Who will use this database?
Members of a team who need to distribute or contribute to a spreadsheet.
Access control
Very Important:
The access level must be Author for all users of this database. This will prevent unauthorized editing in the database and ensure that the feature functions properly.
With Author access, users can create documents, edit documents they create, and edit documents that were assigned (distributed) to them, but they can't edit documents distributed to others. This helps prevent replication conflicts, because users can only edit those documents assigned to them.
You must have Manager access to create or modify the access control list (ACL). Later, you or the designer can refine access to particular views, forms, sections, or fields of a database.
O=Lotus Notes
O=Lotus Notes
PURSAFO
|.:#U
O=Lotus Notes
CN=Lotus Notes Template Development/O=Lotus Notes
PURSAFO
Fde!f^^
$Info
$Body
&Arial
Form Banner
wwwwwwwwwwww
wwwwwwwwwwww
wwwwwwwwwwww
wwwwwwwwwwww
wwwwwwwwwwwwwwwwwwwwwwwww
wwwwwxwwwwww
wwwwwxwwwwww
wxwwppww
wxwwppww
wpppwxww
wpppwxww
wwwwwxwwwwww
wwwwwxwwwwww
wpppwxww
wpppww
wwwwww
wwwwwxwwwwww
wwwwww
UUUUUUUUUUUUUUUUUUUUUUUUUUWw
UUUUXwxww
wpppwxww
UUUWwxwwwwww
wwwwwxwwwwwww
"""(]UUU
""""""""""""""""""""""/
"""""/
*""""/
@
@@ @
`@ `
@ @@
@@@@@
@`@@`
` @`
`@@`@
``@``
aaaaaaaa`
nd If
mmmmmmmm ' get the Um
FormTitleInfoDisplay
FormTitleDisplay
Subject
Title
O=Lotus Notes
O=Lotus Notes
PURSAFO
|.:#U
O=Lotus Notes
CN=Lotus Notes Template Development/O=Lotus Notes
PURSAFO
Fde!f^^
$TITLE
$Info
$$Script_O
$$ScriptName
$Body
&Arial
Response Form
@
@@ @
`@ `
@ @@
@@@@@
@`@@`
` @`
`@@`@
``@``
mmmmmmmmm
FormTitleInfoDisplay
FormTitleDisplay
WorksheetTitle
WorksheetTitleDisplay
O=Lotus Notes
O=Lotus Notes
PURSAFO
|.:#U
O=Lotus Notes
CN=Lotus Notes Template Development/O=Lotus Notes
PURSAFO
Fde!f^^
$TITLE
$Info
$$ScriptName
$Body
GraphicButton1
@ @@ @` @
@@ @@@@@`@@
`@ `@@`@``@
Text1Workbook Views
Button1By Contributor
Button2By Status
Button3By Date
Button4All Documents
HotspotRectangle1
All Documents
HotspotRectangle2
View by Date
HotspotRectangle3
View by Status
HotspotRectangle4
View by Contributor
O=Lotus Notes
O=Lotus Notes
PURSAFO
|.:#U
O=Lotus Notes
CN=Lotus Notes Template Development/O=Lotus Notes
PURSAFO
Fde!f^^
o3C)i
$ViewMapDataset
$ViewMapLayout
$37Parent_ID$
$RefV
0S0E
$34Response_ID
0S0E
WorksheetTitleSheet$29Status
Temp_Status
Worksheetstatus
Temp_Value
Temp_Status
Temp_Status
Temp_Status
0R1S2S
In progress,Ready to consolidate,Do not consolidate,Data requested,Consolidated into master
(.txt)