Attachments¶

In Totalview, information like state, description, location and phone settings are added to current states and reservations. Totalview uses this information to control phone settings and to display additional information to Totalview users. This additional information is also used in post processing of time stamp usage.

With Attachments more detailed information can be added to current states and reservations. When users set the current state or create reservations, they can, beside selecting a state such as In or Busy, also add structured information about what task they are working on.

The added information can later be listed on time stamp reports to better clarify the time usage or used in post processing to create reports on time usage on specific tasks. With attachments it is possible to sort time stamp usage not only on selected state, but also on the structured attachment data.

Attachments are an important and integrated part of Totalview Time, but are also available in Totalview basic.

Attachment example¶

Below is an example of setting the current state and at the same adding attachment information. The attachment information is structured data read from XML or SQL sources. The attachment structure and values are defined in the connected Attachment connector.

See section Enabling Attachments on how to enable attachments.

Adding Attachment¶

When Attachments are enabled, the display is extended with an Attachments section. In the example below the user can specify work details. The number of details and their values are defined in the Attachment connector.

Editing the Customer field shows a list of possible root values to select from. The values are structured in a hierarchy and sub values depend on values previously selected.

Below is an example of values available when Customer is Jackson Car Sale and Case is Totalview Basic. The actual values and their relationship are defined in the Attachment connector and can be customized by changing the connector settings file.

The specified attachments are saved along with the current state settings and reservations, and can be listed in reports or used in post processing of time stamps.

Showing attachments in reports¶

Below is an example of a time stamp report extended with attachment data. Apart from the state and time, the duration and attachment values are listed.

Post processing time stamp values¶

The actual time stamp registrations for users are saved in the Totalview database and can be post processed. By using the attachment values in the post processing, detailed reports can be extracted from the Totalview database.

E.g. creating a report that shows all users’ time stamp usage on work Jackson Car Sale – Totalview Basic, or more specific Jackson Car Sale – Totalview Basic – Setup. By using the attachment values, the level of details can be controlled by the report generator.

Another example is listing the time stamp usage for a user – or group of users – in the last month.

Using Attachments combined with time stamp usage enables various detailed reports to be generated.

Enabling Attachments¶

The usage of Attachments must be enabled on the Totalview server level and on user level, and one or more Attachment connectors must be created.

Attachment connectors are licensed and the Totalview server requires one license for each connected Attachment connector.

Enabling Attachments on server and user¶

Attachments are enabled on the Totalview server by checking the Attachments enabled flag in Totalview.Admin.Server settings. This flag is unchecked by default.

Attachment functionality is disabled on all users by default. The Use attachments flag must be checked for each user using Attachments.

Creating an Attachment connector¶

The Attachment connector provides the attachment structure and data to the Totalview server to present to the user. The structure and data can be customized See Customize Attachments.

Create the connector using Totalview3.Admin.Connectors. Select Category: System extensions and Subtype: Attachments.

The User filtering defines what users are allowed to use the connector. Only users that apply to the filter setting are allowed to use the connector. If no filter is set, then no user will use the connector.

Install the connector using Overview.Install/Update. This will create a new folder and copy the connectors’ program files and XML settings files to the folder.

The XML settings files define the attachment structure and data for the connector. A demo definition and demo data xml file is created when first installing an attachment connector.

Modify these files to customize the attachment definition and data See Customize Attachments.

Customize attachments¶

When installing the Attachments connector, the connectors’ settings files are installed in the XML folder. The folder is default located at <Totalview3>\Attachments\<connector name>.

There are 4 files in the XML folder:

xml and AttachmentsDefinition.xml.These files contain the current attachments structure and data. The attachments structure and data can be customized by modifying these files.
AttachmentsData – Example.xml and AttachmentsDefinition – Example.xmlThese files are demo examples that can be used as examples when customizing the attachments structure and data.

The Attachments Definition file¶

he AttachmentsDefinition.xml file contains the current attachments structure. The structure can be customized by modifying the file.

The definition file defines what fields are listed on the display. It defines the caption, if they are mandatory or not and what attachment data should be listed when selecting the fields. The definition file does not contain the actual attachment data, but instead a reference to where data can be found.

Attachments data can be read from various sources such as XML files or a SQL server database. The ATTACHMENTDEFINITIONS can contain many ATTACHMENTDEFINITION each describing an individual layout and datasource.

<ATTACHMENTDEFINITIONS><ATTACHMENTDEFINITION CAPTION=”Work details” ATTACHMENTID=”WORK” DATASTORAGE=”XMLFile” …>

<VALIDSTATES>

…..

<FIELD

…..

<DEFAULTDEFINITION>

…..

</ATTACHMENTDEFINITION>

<ATTACHMENTDEFINITION CAPTION=”Location” ATTACHMENTID=”LOC” DATASTORAGE=”SQL”  ….>

<VALIDSTATES>

…..

<FIELD

…..

</ATTACHMENTDEFINITION>

</ATTACHMENTDEFINITIONS>

Valid states¶

The ValidStates section allows you to customize which states are allowed to use attachments. The attribute ShortID refers to the state’s ID, which is set in the administration module. Setting the ShortID=* enables all the state to use the attachment.

ValidStates can also hold the Corrections tag that enables attachments to be attached to balance corrections in the Totalview Time Correction Module.

Attachment can be used for current state or reservation as default. To restrict the use add the CURRENT and FUTURE attributes to the STATE tag.

To disable attachment for current state use CURRENT=”FALSE” and to disable attachments for reservation use FUTURE=”FALSE”.

Below is an example of a list of states that are allowed to be used for attachments.

</VALIDSTATES><STATE SHORTID=”In”/>

<STATE SHORTID=”bu” />

<STATE SHORTID=”out” CURRENT=”FALSE”/>

<STATE SHORTID=”pri” FUTURE=”FALSE”/>

<CORRECTIONS/>

</VALIDSTATES>

Example of enabling the attachment for all the current states but not future states.

</VALIDSTATES><STATE SHORTID=”In”/>

<STATE SHORTID=”bu” />

<STATE SHORTID=”*” FUTURE=”FALSE”/>

<CORRECTIONS/>

</VALIDSTATES>

Attachment data from XML file¶

If attachment data is set to be read from an XML file, the data is found in the AttachmentsData.xml file located in the same folder as the attachment definition file.

Below is an example of a definition where data is read from an XML file and the corresponding display.

<ATTACHMENTDEFINITION CAPTION=”Work details” ATTACHMENTID=”XMLCASE” DATASTORAGE=”XMLFile” LAST5VALID=”*”>

<FIELD FIELDID=”CUSTOMER”     CAPTION=”Customer”  REQUIRED=”True”>

<FIELD FIELDID=”CASE”         CAPTION=”Case”>

<FIELD FIELDID=”ACTIVITY”     CAPTION=”Activity”>

<FIELD FIELDID=”SUBACTIVITY”  CAPTION=”Sub activity”/>

<FIELD FIELDID=”WORKTYPE” CAPTION=”Worktype” HIDEINCLIENT=”True”/>

</FIELD>

</FIELD>

</FIELD>

</ATTACHMENTDEFINITION>

The ATTACHMENTDEFINITION TAG contains the following attributes:

CAPTION Defines the caption displayed.
ATTACHMENTID Identifies the attachment and links it to a corresponding ATTACHMENTID in the AttachmentData.xml file. This link is used when defining what data should be listed when the field is selected in the display. The hierarchy of FIELD items define the structure of what data can be selected based on the parent selection.
DATASTORAGE Identifies where the attachment data is located. Possible settings are XMLFile or SQL.
LAST5VALID

Defines when to check whether the cached last used attachments are still valid. If needed the data is sent to the Attachment connector for validity control. The Attachment connector then validates the cached attachment data against the current possible attachments values. Validation of cached attachment data is time consuming and should only be enabled when needed. Default value for LAST5VALID is “*” (Always valid) Possible values are:
- * Always valid and no validity check is performed.
- Runtime Validity check is performed each time the data is requested.
- xh Validity check is performed every x hours.

The FIELD TAG contains the following attributes:

FIELDID Identifies the field and links it to a corresponding FIELDID in the AttachmentData.xml file. Fields are defined in a hierarchy and the parent combined with the FIELDID defines what data should be listed when the field is selected in the display.The FIELDID is unique within the </ATTACHMENTDEFINITION>.
CAPTION Defines the caption displayed.
REQUIRED If True, the value is required. Default is False.If IFParent the value is required if the parent value is set.If a child element has Required=True, then all parent element Required will be set to true by default.If Required=True a value for the element must be selected always. If no value is available validation should fail.
REQUIREDCM Same as REQUIRED but only valid for the Totalview Time Correction Module.
SELECTSINGLE If True, and there is only one value in the data list to select from, then the value is selected automatically. If both REQUIRED and SELECTSINGLE are defined as True then REQUIRED takes precedence.
HIDEINCLIENT Value is not visible in the Totalview client, but only in the Totalview Time Correction Module.

Attachment XML data values¶

When attachment data values are set to be read from an XML file, the data is found in the AttachmentData.xml file located in the same folder as the AttachmentDefintion.xml file.

Below is an example of attachment data. The data is structured in a hierarchy and sub values depend on values previously selected. In the example below, Jackson Car Sale and Totalview 3 Basic are root values. Root values are the initial values the user can select from.

If the user selects Jackson Car Sale, then the next level of data are Totalview TRS and Totalview Basic. By using this hierarchy the user is guided to the task in a structured way.

<ATTACHMENTDATAVALUES ATTACHMENTID=”XMLCASE”>

<DATAVALUES>

<DATA FIELDID=”CUSTOMER” ID=”1000″ VALUE=”Jackson Car Sale”>

<DATA FIELDID=”CASE” ID=”1001″ VALUE=”Totalview 3 TRS”>

<DATA FIELDID=”ACTIVITY” ID=”1002″ VALUE=”Installation”>

<DATA FIELDID=”SUBACTIVITY” ID=”1003″ VALUE=”Server setup”/>

<DATA FIELDID=”SUBACTIVITY” ID=”1004″ VALUE=”User setup”/>

<DATA FIELDID=”SUBACTIVITY” ID=”1005″ VALUE=”Contract setup”/>

<DATA FIELDID=”WORKTYPE” ID=”1006″ VALUE=”Remote access”/>

<DATA FIELDID=”WORKTYPE” ID=”1007″ VALUE=”On location”/>

<DATA FIELDID=”WORKTYPE” ID=”1008″ VALUE=”At office”/>

</DATA>

</DATA>

</DATA>

<DATA FIELDID=”CASE” ID=”1012″ VALUE=”Totalview 3 Basic”>

…

</DATA>

</DATA>

<DATA FIELDID=”CUSTOMER” ID=”2000″ VALUE=”The Wallyes”>

…

The ATTACHMENTDATAVALUES TAG contains the following attributes:

ATTACHMENTID Identifies the attachment and links it to a corresponding ATTACHMENTID in the AttachmentDefinition.xml file. This link is used when defining what data should be listed when the field is selected in the display.

The DATA TAG contains the following attributes:

FIELDID Identifies the field and links it to a corresponding FIELDID in the AttachmentDefinition.xml file. Data fields are defined in a hierarchy and the parent combined with the FIELDID defines what data should be listed when the field is selected in the display.
ID Identifies the value. The ID must be unique as it is subsequently used to identify parents and value selections.
VALUE User friendly text identifying the selection.

Attachment data from SQL¶

If attachment data is set to be read from an SQL data source, then the select statements are defined in the AttachmentDefintion.xml file.

Below is an example of a definition where data is read from an SQL data source.

When using SQL data source, the definition contains the SQL connection string and the select statement to query the attachment data values.

<ATTACHMENTDEFINITION CAPTION=”Work details (SQL)” ATTACHMENTID=”SQLCASE” DATASTORAGE=”SQL”>

<SQLCONNECTION DATASOURCE=”localhost” CATALOG=”Totalview3″ USER=”SA” PASSWORD=”****”/>

<FIELD FIELDID=”CASE” CAPTION=”Case”>

<SQL>

<SQLCOMMAND>SELECT ID, VALUE FROM ATTACHMENT WHERE PARENTID=@PARENTID</SQLCOMMAND>

</SQL>

<FIELD FIELDID=”ACTIVITY”         CAPTION=”Activity”>

<SQL>

<SQLCOMMAND>SELECT ID, VALUE FROM ATTACHMENT WHERE PARENTID=@PARENTID</SQLCOMMAND>

</SQL>

<FIELD FIELDID=”SUBACTIVITY”  CAPTION=”Sub activity”>

<SQL>

<SQLCOMMAND>SELECT ID, VALUE FROM ATTACHMENT WHERE PARENTID=@PARENTID</SQLCOMMAND>

</SQL>

</FIELD>

</FIELD>

</FIELD>

</ATTACHMENTDEFINITION>

The ATTACHMENTDEFINITION TAG contains the following attributes:

CAPTION Defines the caption displayed.
ATTACHMENTID Identifies the attachment. The hierarchy of FIELD items define the structure of what data can be selected based on the parent selection.
DATASTORAGE Identifies where the attachment data is located. Possible settings are XMLFile or SQL.

The SQLCONNECTION TAG defines the database login information such as username, password and the location of the SQL server, as well as the two optional parameters ENCRYPT and TRUSTSERVERCERTIFICATE.

The FIELD TAG contains the following attributes:

FIELDID Identifies the field. Fields are defined in a hierarchy and the parent combined with the FIELDID defines what data should be listed when the field is selected in the display. When DATASTORAGE is SQL, the SQLCOMMAND TAG defines the select statement to use when querying for data. SQL data example describes how to create an example ATTACHMENT table in the Totalview database and populate it with demo data.
CAPTION Defines the caption displayed.

The following parameters are allowed in SQL queries:

PARENTID Takes the ID from the previous selected field and uses it as a parameter for the next query
TVUSERID If this parameter is used it allows the SQL Query to search, based on the users login ID
USERWINLOGIN If this parameter is used it allows the SQL Query to search, based on the users windows login
CLIENTID If this parameter is used it allows the SQL Query to search based on the defined id of the client sending the request.
PARENTUSERDATA If this parameter is used it allows the SQL Query to search based on USERDATA from the previous request. E.g. the USERDATA acts as a round trip container with implementation specific data. An usage example can be found in the “AttachmentsDefinition – Example.xml” file located in the <InstallDir>\InstallFiles directory.

Attachment default data¶

Default attachment data can be defined and read from an XML file or from SQL.

The <ATTACHMENTDEFINITION> tag defines if and how the attachment default values are returned. If any default values matches the reques, the values will be set as default when the user is changing the current state or appointment.

Below is an example of an <ATTACHMENTDEFINITION>.

<DEFAULTDEFINITION DATASTORAGE=”SQL” READONLY=”False”>

<SQLCONNECTION DATASOURCE=”localhost” CATALOG=”Tv3″ USER=”SA” PASSWORD=”****” DBTYPE=”SQL”/>

<SQL>

select top(1) ‘<FIELD FIELDID=”CUSTOMER” CAPTION=”Customer” DATAID=”‘ + p.ID + ‘” DATAVALUE=”‘ + p.VALUE + ‘”>

‘ +'<FIELD FIELDID=”CASE” CAPTION=”Case” DATAID=”‘ + c.ID + ‘” DATAVALUE=”‘ + c.VALUE + ‘”>’+'</FIELD>

</FIELD>’

from ATTACHMENT p, ATTACHMENT c where p.ID = 200 and c.PARENTID = p.ID

</SQL>

</DEFAULTDEFINITION>

The READONLY parameter can be added to the FIELD tag in the result. When set to True the DATAVALUE is set as read only in the client and cannot be changed by the user.

The DEFAULTDEFINITION TAG contains the following attributes:

DATASTORAGE Identifies where the attachment default data is located. Possible settings are XMLFile or SQL. If set to XMLFile, the data is read from the AttachmentDataDefaults.xml file.
READONLY When set to true, all fields in the result are read only.

The SQLCONNECTION TAG defines the database login information such as username, password and the location of the SQL server, as well as the two optional parameters ENCRYPT and TRUSTSERVERCERTIFICATE.

The SQL TAG contains the SQL select sentence:

The result returned from the database should be either in the full <ATTACHMENTS> format

<ATTACHMENTS><ATTACHMENT CAPTION=”Work details” ATTACHMENTID=”XMLCASE” PROVIDER=”Attachments” VERSION=”1.1″>

<FIELD FIELDID=”CUSTOMER” CAPTION=”Customer” DATAID=”1000″ DATAVALUE=”Jackson Car Sale”>

<FIELD FIELDID=”CASE” CAPTION=”Case” DATAID=”1001″ DATAVALUE=”Totalview 3 TRS”>

<FIELD FIELDID=”ACTIVITY” CAPTION=”Activity” DATAID=”1002″ DATAVALUE=”Installation”>

<FIELD FIELDID=”SUBACTIVITY” CAPTION=”Sub activity” DATAID=”1005″ DATAVALUE=”Contract setup”/>

</FIELD>

</FIELD>

</FIELD>

</ATTACHMENT>

</ATTACHMENTS>

or in the <FIELD> format

<FIELD FIELDID=”CUSTOMER” CAPTION=”Customer” DATAID=”1000″ DATAVALUE=”Jackson Car Sale”>

<FIELD FIELDID=”CASE” CAPTION=”Case” DATAID=”1001″ DATAVALUE=”Totalview 3 TRS”>

<FIELD FIELDID=”ACTIVITY” CAPTION=”Activity” DATAID=”1002″ DATAVALUE=”Installation”>

<FIELD FIELDID=”SUBACTIVITY” CAPTION=”Sub activity” DATAID=”1005″ DATAVALUE=”Contract setup”/>

</FIELD>

</FIELD>

</FIELD>

The following parameters are allowed in SQL queries:

PARENTID Takes the ID from the previous selected field and uses it as a parameter for the next query
TVUSERID If this parameter is used it allows the SQL Query to search, based on the users login ID
USERWINLOGIN If this parameter is used it allows the SQL Query to search, based on the users windows login
CLIENTID If this parameter is used it allows the SQL Query to search based on the defined id of the client sending the request.

Attachment default XML data values¶

When attachment default data values are set to be read from an XML file, the data is found in the AttachmentDataDefaults.xml file located in the same folder as the AttachmentDefintion.xml file.

Below is an example of attachment default data. The data is listed as complete <ATTACHMENTS> data structures.

If needed a client and user filter can be set for each value. Only clients and users matching the filter will receive the attachment default value.

An empty <CLIENTID> setting or a setting with the value * will enable the data for all clients.

An empty <USERID> setting or a setting with the value * will enable the data for all users. <USERID> can be set to the users recordID, UserID or Windows login ID.

<ATTACHMENTDATADEFAULTS><DEFAULT>

<CLIENTIDS>

<CLIENTID>*</CLIENTID>

</CLIENTIDS>

<USERIDS>

<USERID>JD</USERID>

</USERIDS>

<ATTACHMENTS>

<ATTACHMENT CAPTION=”Work..” ATTACHMENTID=”XMLCASE” PROVIDER=”..” VERSION=”1.1″>

<FIELD FIELDID=”CUSTEMER” CAPTION=”Cust” DATAID=”2000″ DATAVALUE=”The Wally” READONLY=”True”>

<FIELD FIELDID=”CASE” CAPTION=”Case” DATAID=”2001″ DATAVALUE=”Totalview 3″ READONLY=”True”>

<FIELD FIELDID=”ACTIVITY” CAPTION=”Activity” DATAID=”” DATAVALUE=””>

</FIELD>

</FIELD>

</FIELD>

</ATTACHMENT>

</ATTACHMENTS>

</DEFAULT>

<DEFAULT>

Keep current attachment when changing state¶

When changing state the user can select the special ID -999 <Keep> to keep the current select attachment value.

When selecting -999 <Keep> the server will search the current reservation attachment value and replace the -999 <Keep> tag with the tag from the current attachment.

The -999 <Keep> is defined in the XML or SQL data as all other data field. The only requirement is that ID=”-999”. There is no requirement for the Value part. See example below.

<ATTACHMENTDATAVALUES ATTACHMENTID=”XMLCASE”><DATAVALUES>

<DATA FIELDID=”CUSTOMER” ID=”-999″ VALUE=”Keep current attachment”>

<DATA FIELDID=”CUSTOMER” ID=”1000″ VALUE=”Jackson Car Sale”>

<DATA FIELDID=”CASE” ID=”1001″ VALUE=”Totalview 3 TRS”>

…

The saved Attachments structure¶

The selected attachment is saved with the current state or reservation information as an XML structure where each child data is an element of the parent data.

Saved attachment built on single definition¶

Below is an attachments example built on the definition and data file below.

Attachment structure

<ATTACHMENTS>

<ATTACHMENT CAPTION=”Work details” ATTACHMENTID=”XMLCASE” PROVIDER=”Attachments” VERSION=”1.1″>

<FIELD FIELDID=”CUSTOMER” CAPTION=”Customer” DATAID=”1000″ DATAVALUE=”Jackson Car Sale”>

<FIELD FIELDID=”CASE” CAPTION=”Case” DATAID=”1001″ DATAVALUE=”Totalview 3 TRS”>

<FIELD FIELDID=”ACTIVITY” CAPTION=”Activity” DATAID=”1002″ DATAVALUE=”Installation”>

<FIELD FIELDID=”SUBACTIVITY” CAPTION=”Sub activity” DATAID=”1005″ DATAVALUE=”Contract setup”/>

</FIELD>

</FIELD>

</FIELD>

</ATTACHMENT>

</ATTACHMENTS>

Definition file

<ATTACHMENTDEFINITION CAPTION=”Work details” ATTACHMENTID=”XMLCASE” DATASTORAGE=”XMLFile”>

<FIELD FIELDID=”CUSTOMER”     CAPTION=”Customer”  REQUIRED=”True”>

<FIELD FIELDID=”CASE”         CAPTION=”Case”>

<FIELD FIELDID=”ACTIVITY”     CAPTION=”Activity”>

<FIELD FIELDID=”SUBACTIVITY”  CAPTION=”Sub activity”/>

<FIELD FIELDID=”WORKTYPE” CAPTION=”Worktype” HIDEINCLIENT=”True”/>

</FIELD>

</FIELD>

</FIELD>

</ATTACHMENTDEFINITION>

Data file

<ATTACHMENTDATAVALUES ATTACHMENTID=”XMLCASE”>

<DATAVALUES>

<DATA FIELDID=”CUSTOMER” ID=”1000″ VALUE=”Jackson Car Sale”>

<DATA FIELDID=”CASE” ID=”1001″ VALUE=”Totalview 3 TRS”>

<DATA FIELDID=”ACTIVITY” ID=”1002″ VALUE=”Installation”>

<DATA FIELDID=”SUBACTIVITY” ID=”1003″ VALUE=”Server setup”/>

<DATA FIELDID=”SUBACTIVITY” ID=”1004″ VALUE=”User setup”/>

<DATA FIELDID=”SUBACTIVITY” ID=”1005″ VALUE=”Contract setup”/>

<DATA FIELDID=”WORKTYPE” ID=”1006″ VALUE=”Remote access”/>

<DATA FIELDID=”WORKTYPE” ID=”1007″ VALUE=”On location”/>

<DATA FIELDID=”WORKTYPE” ID=”1008″ VALUE=”At office”/>

</DATA>

</DATA>

</DATA>

<DATA FIELDID=”CASE” ID=”1012″ VALUE=”Totalview 3 Basic”>

…

</DATA>

</DATA>

<DATA FIELDID=”CUSTOMER” ID=”2000″ VALUE=”The Wallyes”>

Saved attachment built on multible definitions¶

Below is an example built on AttachmentDefinitions file with two definitions.

Definition file

<ATTACHMENTDEFINITIONS>

<ATTACHMENTDEFINITION CAPTION=”Work details (XML data)” ATTACHMENTID=”XMLCASE” DATASTORAGE=”XMLFile”  VERSION=”1.1″ LAST5VALID=”*”>

<VALIDSTATES>

<STATE SHORTID=”Inn”/>

</VALIDSTATES>

<FIELD FIELDID=”CUSTOMER”     CAPTION=”Customer”>

<FIELD FIELDID=”CASE”         CAPTION=”Case”/>

</FIELD>

</ATTACHMENTDEFINITION>

<ATTACHMENTDEFINITION CAPTION=”Tools (XML data)” ATTACHMENTID=”XMLTOOL” DATASTORAGE=”XMLFile”  VERSION=”1.1″ LAST5VALID=”*”>

<VALIDSTATES>

<STATE SHORTID=”Inn”/>

</VALIDSTATES>

<FIELD FIELDID=”TOOL”     CAPTION=”Tool”/>

</ATTACHMENTDEFINITION>

</ATTACHMENTDEFINITIONS>

Attachment structure

<ATTACHMENTS>

<ATTACHMENT CAPTION=”Work details (XML data)” ATTACHMENTID=”XMLCASE” PROVIDER=”Att1″ VERSION=”1.1″>

<FIELD FIELDID=”CUSTOMER” CAPTION=”Customer” DATAID=”1000″ DATAVALUE=”Jackson Car Sale”>

<FIELD FIELDID=”CASE” CAPTION=”Case” DATAID=”1001″ DATAVALUE=”Totalview 3 TRS”/>

</FIELD>

</ATTACHMENT>

<ATTACHMENT CAPTION=”Tools (XML data)” ATTACHMENTID=”XMLTOOL” PROVIDER=”Att1″ VERSION=”1.1″>

<FIELD FIELDID=”TOOL” CAPTION=”Tool” DATAID=”100001″ DATAVALUE=”Hammer” />

</ATTACHMENT>

</ATTACHMENTS>

Handling special XML characters¶

The characters (<), (&), (>), (“), (‘) have special meaning when used in XML and must be treated specially by the various clients.

In the data element received and saved the special characters are replaced with the XML following element tags < (<), & (&), > (>), " (“), ' (‘).

When reading data a conversion must be made to display eg. &lt as (<).

Example: <DATA FIELDID=”CASE” ID=”1001″ VALUE=”J&K”> displays 1001-J&K

When writing data a conversion must be made to save eg (<) as &lt.

Example: 1001-J&K goes to <FIELD FIELDID=”CASE” CAPTION=”CASE” DATAID=”1001″ DATAVALUE=”J&K” />

SQL data example¶

This example shows how to modify the attachment definition file to read data from an SQL data source instead from the XML file and how to create a table with demo data.

Modify the attachment configuration¶

In the AttachmentsDefinition – Example.xml file there is listed an example using SQL data source. Copy the <ATTACHMENTDEFINITION … DATASTORAGE=”SQL”> section and replace the existing definition in the AttachmentsDefintion.xml file.

Modify the DATASOURCE, CATALOG, CATALOG, PASSWORD attributes in the SQLCONNECTION TAG to reflect you current settings.

Create ATTACHMENT table and data¶

The SQL example expects an ATTACHMENT table with populated data. Run the following script to create and populate an ATTACHMENT table.

CREATE TABLE [dbo].[ATTACHMENT](

[ID] [nvarchar](50) NOT NULL,

[VALUE] [nvarchar](150) NOT NULL,

[PARENTID] [nvarchar](50) NOT NULL,

CONSTRAINT [PK_ATTACHMENT] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

insert into ATTACHMENT values(100, ‘Totalview’, 0)

insert into ATTACHMENT values(110, ‘Totalview server’, 100)

insert into ATTACHMENT values(111, ‘Documentation’, 110)

insert into ATTACHMENT values(112, ‘Development’, 110)

insert into ATTACHMENT values(113, ‘Test’, 110)

insert into ATTACHMENT values(120, ‘Totalview client’, 100)

insert into ATTACHMENT values(121, ‘Attachment interface’, 120)

insert into ATTACHMENT values(122, ‘Documentation’, 121)

insert into ATTACHMENT values(123, ‘Test’, 122)

insert into ATTACHMENT values(200, ‘Totalview Attachments’, 0)

insert into ATTACHMENT values(210, ‘Loading data’, 200)

insert into ATTACHMENT values(211, ‘Read from SQL’, 210)

insert into ATTACHMENT values(212, ‘Read from XML’, 210)

insert into ATTACHMENT values(213, ‘Documentation’, 210)

insert into ATTACHMENT values(214, ‘Test’, 210)

insert into ATTACHMENT values(220, ‘Analyse data’, 200)

insert into ATTACHMENT values(221, ‘Read used attachments’, 220)

insert into ATTACHMENT values(400, ‘Totalview TRS’, 0)

insert into ATTACHMENT values(410, ‘TRS Admin’, 400)

insert into ATTACHMENT values(411, ‘New contracts’, 410)

insert into ATTACHMENT values(420, ‘TRS Reports’, 400)

insert into ATTACHMENT values(421, ‘Totalview time usage’, 420)

insert into ATTACHMENT values(430, ‘TRS Correction’, 400)

insert into ATTACHMENT values(431, ‘Overtime acceptance’, 430)

Extracting attachment time stamp usage¶

Attachments and time stamp usage can be extracted from Totalview by using reports or by executing queries against the Totalview database.

Executing attachments using queries¶

Execute the query against the Totalview database and export the result to an XML file.

Start Excel and open the XML file as an XML table.

The queried data is imported into Excel in a structured manner and can be sorted and grouped as needed.

The attachment query¶

Below is an example of a query, selecting attachments for a user in a specified period.

The user can modify the where clause to customize the query. If other parts of the query are altered it may result in the query failing or the Excel import failing.

DECLARE @Attachments nvarchar(max)select @Attachments = COALESCE(@Attachments, ”) +

‘<STATE ‘ +

‘ID=”‘ + CAST(S.RESOURCEID as nvarchar(max)) + ‘” ‘ +

‘USERID=”‘ + RS.USERID + ‘” ‘ +

‘CAPTION=”‘ +  RS.NAME + ‘ ‘ + RS.LASTNAME + ‘” ‘ +

‘START=”‘ + CONVERT(varchar, S.STATESTART, 105) + ‘ ‘ + CONVERT(varchar, S.STATESTART, 8) +  ‘” ‘ +

‘END=”‘ + CONVERT(varchar, S.STATEEND, 105) + ‘ ‘ + CONVERT(varchar, S.STATEEND, 8) +  ‘” ‘ +

‘DURATION=”‘ + CAST(DATEDIFF(SECOND, s.STATESTART, S.STATEEND) as nvarchar(max)) + ‘”>’ +

CAST(R.ATTACHMENT AS nvarchar(max))

+ ‘</STATE>’

from

STATEFINISH S, RESERVATION R, RESOURCE RS

where

— Modify here start

RS.USERID = ‘JJ’

and S.STATESTART > ‘2010-11-01’ and S.STATEEND < ‘2010-11-30’

— Modify here end

and R.ATTACHMENT is not null and CAST(R.ATTACHMENT as nvarchar(max)) <> ‘<ATTACHMENTS/>’

and R.RECID = S.RESERVATIONID

and RS.RECID = S.RESOURCEID

select Attachments = ‘<ROOT>’ + @Attachments + ‘</ROOT>’

Copy and execute the query. Right click on the result and select save as. Save the result as an XML file.

Import the result into Excel.¶

Start Excel and select Open. Select the exported XML result file.

Choose to import the file as an XML table.

The XML result file will be imported and flattened into Excel. Use filter and grouping functionality to extract the data needed.