Excel Integration - Axpo Demo Notes

Version 1, 2011-03-24 (AH)

Systems

• http://linthal.softxs.ch/beta -- Combined Beta system

• http://linthal.softxs.ch/dev/linthal115dev - Development system

Test Data

Fifteen LA2 documents have been entered into the Beta and Development systems listed above. The documents are copies of existing documents, and include specially created vorabzug, zur Freigabe and gut zur Ausfuerhung revisions. The T0, T0wv dates, Delta values and other planning related fields have been set in order to provide complete and consistent demonstration, which demonstrate the planning and scheduling process.

The document codes for the test data are:

• LA2-E-6-03-351-213-10_1002
• LA2-E-6-03-351-213-10_1003
• LA2-E-6-03-351-213-10_1004
• LA2-E-6-03-351-213-10_1005
• LA2-E-6-03-351-213-10_1006
• LA2-E-6-03-351-213-10_1007
• LA2-E-6-03-351-213-10_1010
• LA2-E-6-03-351-213-10_1011
• LA2-E-6-03-351-213-10_1012
• LA2-E-6-03-351-213-10_1013
• LA2-E-6-03-351-213-10_1014
• LA2-E-6-03-351-213-10_1015
• LA2-E-6-03-351-213-10_1016
• LA2-E-6-03-351-213-10_1017
• LA2-E-6-03-351-213-10_1018

Note that each document is a copy of an existing document aby adding 1000 to the Laufnummer as follows:

• LA2-E-6-03-351-213-10_1002 copied from LA2-E-6-03-351-213-10_0002

You can view the documents with the following search:

1. Change to Bereiche LA2

2. DrawMGT menu: Dokumente --> Suchen

   1. Bereich - Aktueller Bereich

   2. Kontakt - 213

   3. Laufnummer - 1000 --> 1020

Note that this displays all revisions associated with these documents:

• Revision	Code	Description
  v_B	GA	gut zur Ausfuehrung
  v_A	ZF	zur Freigabe
  v__	VA	Vorabzug

When you display the revision detail, a new bock is shown which contains the user defined fields with text describing the fields name and contents.

Limitations of the Excel VBA Code

The following columns are not implemented:

1. The Contract menu - The menu is implemented and changes will cause user lists in the the Wer menus to change, but this is not extensively tested, and due to the Gruppe menu not being complete, could lead to data that causes validation errors on the server.

2. The Gruppe columne - The menu is not implemented

3. Links to Grundlagen documents

4. The Format column

5. The Vorabzug ja/nein column

6. The Stn-Nr column

7. The Art (Pruefungsart) column

8. The Pruef-Nr column

9. The Q-Eklaerung column

10. The Verteiler Ext and Ablage/Archiv columns associated with gut zur Ausfuerhung revision

The following features are not completely implemented and/or tested:

1. Client side cross-field validation of workflow steps. E.g. there is no checking when you make a workflow step update to make sure that the preceding and subsequent workflow steps have are compatible with the change.

2. The server-side revision date is not set, this is because of a race condition, which can occur and cause server-side validation error. The problem is that if you change multiple revision dates for the same document, the sequence of saving records (server-side) leads to validation errors when the first revision is saved, but the second revision (before being saved) is out of sequence.

3. The record update count (record serial number) field processing is implemented but not completely tested, it is not recommended to do simultaneous updates on the server and client. Do a refresh in Excel if you update records on the server.

Basic Operation

This section describe operations that can be performed for the demo:

Startup

The following three files should be installed in the same directory:

1. LinthalDrawMGT-Template-1.xlsm - An example planning workbook

2. LinthalDrawMGT-Menu-1.xlsm - Lightweight Excel AddIn that installs the DrawMGT Excel Ribbon (menu)

3. LinthalDrawMGT-Template-1.xlsm - An Excel VBA AddIn that contains the library code that implements the Excel Interface

Procedure

1. Open the Template file:

2. Watch in the lower message area in the Excel screen (at the bottom) and verify that Excel opens the Menu and Lib files.

3. Excel will display an example document list and a number of hidden worksheets (these worksheets will not be displayed in the production version).

4. Verify there is a new Ribbon (menu) called DrawMGT next to the Start menu.

Menu Items

1. Server

   1. Login - Login into the DrawMGT server

   2. Logout - Logout from server

   3. Set Server - not enabled for demo

2. Data

   1. Search Documents - Displays a search form. See below for details

   2. Refresh Attributes - not enabled for for demo

3. Edit

   1. Get Next Sequence - not enabled for demo

   2. Refresh Selected - Refresh selected rows (local data) with latest data from server (see below)

   3. Refresh All - Refresh all rows (local data) with latest data from server (see below)

   4. Duplicated Selected - not enabled for demo

   5. Check for Duplicates - not enabled for demo

4. Save

   1. Save Selected - Save all rows to server (see below)

   2. Save All - Save selected rows to server (see below)

5. Test

   1. Test Cases - Displays a list of regression test cases (do not use)

Operations on all and selected rows:

• All Rows - Operation works on all rows in the spreadsheet

• All Rows - Operation works on rows where any cell in the row is selected. The selection does not need to be contiguous.

For both cases the header rows and empty rows are ignored. E.g. if you select cells in the header or empty rows nothing bad happens.

Login

1. Select menu item: DrawMGT --> Login

2. Enter DrawMGT user name and password
3. A confirmation dialog is presented if the login is successful

Search for and Display Data

1. Select menu item: DrawMGT --> Search Documents

2. A search dialog is displayed: Enter the following search criterion:

   1. Rapport - Select Bau

   2. Bereich - Select LA2

   3. Kontakt - Select 213

   4. Laufnummer - Select 1000 --> 1020, or select a smaller range (2002 -> 2006) if you want quicker results.

Note that the search and display of data is slow, and that a number of hidden rows and columns are displayed during the processing period. This is because the Excel screen updating (e.g. recalculation) is enabled during the data processing. This was done to assist the testing and development process.

After the search processing completes, the hidden cells and rows are hidden, and a list of approximately 15 documents is displayed.

The header contains sections for the document and its vorabzug, zur Freigabe and gut zur Ausfuerhung revisions.

Features:

1. The date calculation, based on the T0 and delta values is implemented. You should change T0 and the delta values, not the Soll dates

2. Automatic creation of the Drawing Code, based on classification menu settings and the Laufnummer and Teil is implemented

3. Status menus are implemented
4. Title, scale and other text fields are implemented

Updating Data and Saving It to Server

You can change data for documents and revisions.

Scenarios

1. Change the value of the T0 field and observe that the Soll dates change.

2. Change the value of the Delta fields and observer that preceding Soll dates change.

3. Set the Ist date for the Vorabzug Erstellen

4. Set the Ist date and Status (to non-Offen) for the Vorabzug Stellungnahme

   1. You can also change the Wer columns. Note that the user list is displayed based on the user roles in the server

5. Change the values in the Resourcenplannung and Verteiler. Notes:

   1. The Resourcenplannung von and bis dates are calculated base on the T0 value and should not be changed manually.

   2. The gut zur Ausfuerhung revision fields: Verteiler-Ext and Verteiler-Ablage are not saved correctly (a bug)

To save data there are two choices:

1. Select the menu item Save All. All rows will be saved

2. Select cells in rows that should be saved and press the menu item Save Selected

Restrictions

Given the limited test data observe the following restrictions:

1. Document code, you can demonstrate the document classification menus, and that the document code is automatically updated basedon the menu setings and Laufnummer and Teil, but don't save the document if the code is changed (this has not been extensively tested).

2. The Bereich or Gruppe - These menus are not completely implemented

3. Do not change Ver (revision code)

4. Do not copy and paste rows of data

Refresh Data

If data is changed on the server, then it is possible to refresh rows in the planning spreadsheet, so that they are updated with the latest server data.

To reshresh data there are two choices:

1. Select the menu item Refresh All. All rows will be refreshed

2. Select cells in rows that should be refreshed and press the menu item Refresh Selected

When data is refreshed, all document and revision data for the row is downloaded and displayed in the corresponding Excel cells. If differences occur, then the cell contains the server (new) value and a cell note is appended to the cell with the original (old) spreadsheet value.

Limitation: The difference checking has bugs for dates (due to the different date formats) and cell comments are often created when the date has not changed.

Demo Script

1. DrawMGT Display

   1. Change to Bereiche LA2

   2. DrawMGT menu: Dokumente --> Suchen

      • Bereich - Aktueller Bereich

      • Kontakt - 213

      • Laufnummer - 1000 --> 1020

   3. Select document LA2-E-6-03-351-213-10_0003-v

      • Display workflow detail for revision V, the Vorabzug

2. Excel Download

   1. Menu item: Search Documents

      • Rapport - Select Bau

      • Bereich - Select LA2

      • Kontakt - Select 213

      • Laufnummer - Select 1002 --> 1005

3. Excel Update

   1. Find row (probably row 15) for LA2-E-6-03-351-213-10_0003

   2. Update T0 and Delta value:

      • Column CO(341): T0: Change date to three days earlier

      • Column AS(28): Delta7: Change from 28 to 25

   3. Complete workflow steps Vorabzug Erstellen and Stellungnahme

      • Column AQ(29): Erstellen Ist: Set date one month ago

      • Column AR(29): Erstellen Wer: Change user

      • Column AV(33): Stellungnahme Ist: Set today's date

      • Column AW(34): Stellungnahme Wer: Change user

      • Column AX(35): Stellungnahme Status: keine Bermerkungen

   4. Update Resourcenplannung and Verteiler fields

      • Column CQ(49): Planner CAD: Change from 25 to 28

      • Column CS(51): Planner PL: Change from 6 to 7

4. Excel Save

   1. Select a cell in the row containing LA2-E-6-03-351-213-10_0003

   2. Menu item: Save Selected

5. DrawMGT Re-Display

   1. Refresh web screen for revision LA2-E-6-03-351-213-10_0003-v

   2. Verify changes from Excel displayed

6. DrawMGT Update

   1. In web screen for LA2-E-6-03-351-213-10_0003-v

   2. Button Aendern

      • Change Stellunnahme Dauer (Delta7): from 25 to 29

      • Save

7. Excel Refresh

   1. Select a cell in the row containing LA2-E-6-03-351-213-10_0003

   2. Menu item: Refresh Selected

   3. Verify Delta7 changed to 29 and that the old value in the cell note is 25

Compatibility Testing Notes

The Excel code has been tested on different versions of Windows and Excel.

The only know limitation is that the VBA code does not yet work with Excel 2003.

Excel Versions Tested

• Excel 2007 and Excel 2010

• Not tested Excel 2003

Windows Versions and Languages Tested

• Windows 7, German and English

• Windows XP, Hungarian