Differences between revisions 12 and 13

Excel Integration - Axpo Demo Notes

Version 3, 2011-06-06 (AH)

Ref Prj-E-6-01-610-221-00_0004-Anl-v03

Contents

Excel Integration - Axpo Demo Notes
Status of the Excel Integration
Operation
Compatibility Testing Notes
1. Excel Versions Tested
2. Windows Versions and Languages Tested

Status of the Excel Integration

Recently Completed Features

The temporary test data has been removed from the beta servers.
- This means that searches will not return any documents, because there are no documents with the correct revision types.
- Use the Create New Document menu item to create new documents for saving and searching
New menu items Create Report O2F1 and Create Report O2F2
- O2F1 (Dokumentenlieferprogramm) - incomplete prototype (see below)
- O2F2 (Ressourceplanung) - not implemented.
All main menu items work
- With the exception of the new Create Report items mentioned above.
The initial testing of document and revision serial numbers (hidden fields for catching simultaneous updates) is complete.

Limitations of the Excel VBA Code

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

Known bug: New rows. When creating new rows with Search Documents or Duplicate Selected, the rows created at the end of the first contiguous selection, instead of after the last selected item.
Known bug: Highlight colors. The highlight coloring of the new comlumn Status/DrawMGT-Nr. is incomplete and needs corrections and more testing.
- Check for Duplicates menu sets the highlight color to white in spite of previous yellow/red coloring.
- The highlight colors are:
  - White = OK
  - Pink = Error, typically a duplicate document
  - Yellow = Warning, typically not saved or cell changed
Incomplete implementation: Cell-locking. Cells with a light-orange color are intended to be read-only.
- This is a temporary solution to avoid slowing the developement effort. Protecting worksheets is a time consuming exercise.
- Currently the following fields are set to read-only:
  - Calculated fields, typically Soll date fields
  - Bereich and Vorabzug ja/nein. When the Vorabzug ja/nein menu is changed then all the Vorabzug fields will be locked/unlocked. This code is not well-tested, and also does not set the revision codes correctly.
Incomplete testing: The validation of the Ist date sequence is implemented, but not extensively tested, there may be bugs.
Incomplete implementation: Create Reports.
- O2F1 (Dokumentenlieferprogramm) is an early prototype, There are column formatting errors (date fields).
- O2F2 (Ressourceplanung) is not implemented.

Columns Not Implemented

The following worksheet columns are not implemented:

The Q-Eklaerung column
Links to Grundlagen documents
The Format column
The Vorabzug ja/nein column - Does not clear the Vorabzug fields when the menu is set to nein
The Stn-Nr column
The Art (Pruefungsart) column
The Pruef-Nr column

Operation

Startup

Open the Excel file LinthalDrawmgt-Planning-1.xlsm
Click on the DrawMGT menu.

Menu Items

Server
1. Login - Login into the DrawMGT server
2. Logout - Logout from server
Save
1. Save Selected - Save all rows to server (see below)
2. Save All - Save selected rows to server (see below)
Search
1. Search Documents - Displays a search form. See below for details
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)
Edit
1. Create New Document - Create a new document at the selected row (make sure it is at or below row 18!)
2. Duplicated Selected - Duplicate the selected rows
3. Get Next Sequence - Get the sequence Lauf-Nr. for the selected rows
4. Restart Revision Sequence - Assign the text set of revision codes to the selected rows
5. Check for Duplicates - Check for duplicate documents. Checks the DrawMGT-Nr. and the Documentindex
6. Refresh Attributes - Functions correctly, but is very slow (it downloads many thousands of records!)
Report
1. Create Report O2F1 - Displays a Documentenlieferprogramm report (incomplete)
2. Create Report O2F2 - Displays a Ressourcenplanug report (not implemented yet)

Operations on all and selected rows:

All Rows - Operation works on all rows in the spreadsheet
Selected 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.

Select menu item: DrawMGT --> Login
Enter DrawMGT user name and password
Use system (selected by default):
- ZG Development -- http://linthal.softxs.ch/dev/linthal115dev
A confirmation dialog is presented if the login is successful

Creating and Saving Documents

Features

Create new documents/revisions
Duplicate documents/revisions
Save changes and new documents/revision to the server
Refresh documents/revisions from the server

Document Creation Scenario

Create a new document using the Create New Document menu item
- Assign a unique document code manually or using the Get Next Sequence menu item
- Set/edit the other (non-readonly) fields
Save the document
Duplicate the document (perhaps multiple times)
- Assign unique document codes
- Save the documents
Delete the document rows from the worksheet
Search for the documents just created (see next section)

Editing Scenarios

Change the value of the T0 field and observe that the Soll dates change.
Change the value of the Delta fields and observe that preceding Soll date fields change.
Set the Ist dates and Status fields.
- You can also change the Wer columns. Note that the user list is displayed based on the user roles in the server
Change the values in the Resourcenplannung and Verteiler.
- The Resourcenplannung von and bis dates are calculated base on the T0 value and cannot be changed manually.

Saving

To save data there are two choices:

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

Searching for Documents

Note that there is no longer any test data in the beta server and that Search will only show documents with revisions having the following revision types:

Revision

Code

Description

v_B

GA

gut zur Ausfuehrung

v_A

ZF

zur Freigabe

v__

VA

Vorabzug

When documents are displayed, the header contains sections for the document and its vorabzug, zur Freigabe and gut zur Ausfuerhung revisions.

Features:

The date calculation, based on the T0 and delta values is implemented. You should can T0 and the delta values. The Soll date felds are colored to indicate that they are read-only
Automatic creation of the document code, based on classification menu settings and the Laufnummer and Teil fields is implemeted
Wer, Ist and Status fields are implemented
Title, scale and other text fields are implemented

Restrictions

Given the limited test data observe the following restrictions:

You cannot change Bereich or Vorabzug ja/nein for a document that has already been saved
You cannot not change Ver (revision codes)
You can copy entire rows of data and use the Check for Duplicats to flag them.
- It will not be possible to save copied rows, unless unique document codes are assigned to the rows. Enter them manually or use the Get Next Sequence menu item

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:

Select the menu item Refresh All. All rows will be refreshed
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 ifferences occur, then the cell contains the server (new) value and a cell note is appended to the cell with the original (old) spre dsheet value.

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

-Deletions are marked like this.
+Additions are marked like this.
 Line 3:
-'''Version''' 2, 2011-05-20 (AH)



'''Ref''' Prj-E-6-01-610-221-00_0004-Anl-v02
+'''Version''' 3, 2011-06-06 (AH)



'''Ref''' Prj-E-6-01-610-221-00_0004-Anl-v03
 Line 14:
-. The three Excel files have been reduced to a single file

  1. All main menu items work:

     1. ''Search Document'' -- The document/revision download performance has been improved

     1. ''Create New Document'' -- Implemented (see the warning below)

     1. ''Get Next Sequence Number'' -- Implemented

     1. ''Check for Duplicates'' -- Now highlights the left-most cell and creates a cell comment indicating which rows are duplicate 

  1. Menu items that access the server now check for duplicates before the server access is performed.

  1. The hidden worksheets, except for the ''Bau-Layout'' worksheet (a bug), are now hidden, and the system starts with single examp e planning worksheet.
+. The temporary test data has been removed from the beta servers.

     * This means that searches will not return any documents, because there are no documents with the correct revision types.

     * Use the ''Create New Document'' menu item to create new documents for saving and searching



  1. New menu items ''Create Report !O2F1'' and ''Create Report !O2F2''

     * ''!O2F1'' (''Dokumentenlieferprogramm'') - incomplete prototype (see below)

     * ''!O2F2'' (''Ressourceplanung'') - not implemented.



  1. All main menu items work

     * With the exception of the new ''Create Report'' items mentioned above.



  1. The initial testing of document and revision serial numbers (hidden fields for catching simultaneous updates) is complete.
-Line 26:
+Line 30:
-Line 29:
+Line 32:
-. Re-loading data, using ''Search Documents'' into an existing list is not extensively tested and we have seen at least one bug.  For safety always: 

     * Select '''Rapport''' = ''Bau''

     * Select the checkbox '''Place results in a new worksheet''' at the top right of the search screen



  1. There is a new comlumn ''Status/DrawMGT-Nr.'', which contains the DrawMGT's internal document id (which is blank for new docume ts). This field has the background color set based on the document status (which is new and not completely tested) as follows:

     * '''Grey''' = OK

     * '''Ping''' = Error, typically a duplicate document 

     * '''Yellow''' = Warning, typically ''not saved'' or ''cell changed''



  1. Cells with a light-orange color are meant to be read-only, this is a temporary solution to avoid slowing the developement effor . Protecting worksheets is a time consuming exercise. Currently the following fields are set to read-only:

     * Calculated fields, typically ''Soll'' date fields

     * ''Bereich'' and ''Vorabzug ja/nein''. When the Vorabzug ja/nein menu is changed then all the Vorabzug fields will be locked/unlocked. This code is not well-tested, and also does not set the revision code correctly.



  1. There is a minor bug with ''Create New Document'', it works, but '''must select a cell in row 17 or below'''. Otherwise it will be impossible to save the new document.



  1. The validation of the ''Ist'' date sequence is implemented, but not extensively tested, there may be bugs.
+. Known bug: New rows. When creating new rows with ''Search Documents'' or ''Duplicate Selected'', the rows created at the end of the first contiguous selection, instead of after the last selected item.



  1. Known bug: Highlight colors. The highlight coloring of the new comlumn ''Status/DrawMGT-Nr.'' is incomplete and needs corrections and more testing.

     * ''Check for Duplicates'' menu sets the highlight color to white in spite of previous yellow/red coloring.

     * The highlight colors are:

       * '''White''' = OK

       * '''Pink''' = Error, typically a duplicate document

       * '''Yellow''' = Warning, typically ''not saved'' or ''cell changed''



  1. Incomplete implementation: Cell-locking. Cells with a light-orange color are intended to be read-only.

     * This is a temporary solution to avoid slowing the developement effort. Protecting worksheets is a time consuming exercise.

     * Currently the following fields are set to read-only:

       * Calculated fields, typically ''Soll'' date fields

       * ''Bereich'' and ''Vorabzug ja/nein''. When the Vorabzug ja/nein menu is changed then all the Vorabzug fields will be locked/unlocked. This code is not well-tested, and also does not set the revision codes correctly.



  1. Incomplete testing: The validation of the ''Ist'' date sequence is implemented, but not extensively tested, there may be bugs.



  1. Incomplete implementation: ''Create Reports''.

     * ''!O2F1'' (''Dokumentenlieferprogramm'') is an early prototype, There are column formatting errors (date fields).

     * ''!O2F2'' (''Ressourceplanung'') is not implemented.
-Line 49:
+Line 55:
-The following worksheet columns are not implemented (and have a dark grey background):
+The following worksheet columns are not implemented:
-Line 54:
+Line 60:
-. The ''Vorabzug ja/nein'' column - Currently has a bug where the revision codes are not reset currectly
+. The ''Vorabzug ja/nein'' column - Does not clear the ''Vorabzug'' fields when the menu is set to ''nein''
-Line 86:
+Line 92:
-. '''Test'''

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

     1. '''Create Report !O2F1''' - Displays a ''Documentenlieferprogramm'' report (incomplete)

     1. '''Create Report !O2F2''' - Displays a ''Ressourcenplanug'' report (not implemented yet)
-Line 98:
+Line 105:
 . Select menu item: '''DrawMGT --> Login'''
-Line 101:
+Line 108:
     * ZG Development -- http://linthal.softxs.ch/dev/linthal115dev
-Line 104:
+Line 111:
-== Search for and Display Data ==



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

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

    1. '''Place Results in a new worksheet''' -- Select the checkbox ('''Important!''')

    1. '''Rapport''' - Select '''Bau'''

    1. '''Bereich''' - Select '''LA2'''

    1. '''Kontakt''' - Select '''213'''

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



After the search processing completes 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

 1. Automatic creation of the ''Drawing Code'', based on classification menu settings and the ''Laufnummer'' and ''Teil'' is implemeted

 1. Status menus are implemented

 1. Title, scale and other text fields are implemented





== Updating Data and Saving It to Server ==



You can change data for documents and revisions.



=== Scenarios ===
+== Creating and Saving Documents ==



=== Features ===



  1. Create new documents/revisions

  1. Duplicate documents/revisions

  1. Save changes and new documents/revision to the server

  1. Refresh documents/revisions from the server



=== Document Creation Scenario ===



  1. Create a new document using the ''Create New Document'' menu item

     * Assign a unique document code manually or using the ''Get Next Sequence'' menu item

     * Set/edit the other (non-readonly) fields

  1. Save the document

  1. Duplicate the document (perhaps multiple times)

     * Assign unique document codes

     * Save the documents

  1. Delete the document rows from the worksheet

  1. Search for the documents just created (see next section)



=== Editing Scenarios ===
-Line 133:
+Line 135:
-. Change the value of the ''Delta'' fields and observe that preceding ''Soll'' dates change.

  1. Set the ''Ist'' date for the ''Vorabzug Erstellen''

  1. 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

  1. 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.

     1. The ''gut zur Ausfuerhung'' revision fields: ''Verteiler-Ext'' and ''Verteiler-Ablage'' are not saved correctly (a bug)
+. Change the value of the ''Delta'' fields and observe that preceding ''Soll'' date fields change.

  1. Set the ''Ist'' dates and ''Status'' fields.

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

  1. Change the values in the ''Resourcenplannung'' and ''Verteiler''.

     * The ''Resourcenplannung'' ''von'' and ''bis'' dates are calculated base on the ''T0'' value and cannot be changed manually.



=== Saving ===
-Line 146:
+Line 148:
-=== Restrictions ===



Given the limited test data observe the following restrictions:



  1. The ''Bereich'' for a document that has already been saved

  1. Do not change ''Ver'' (revision code)

  1. Do not save documents for which the ''Vorabzug ja/nein'' menu has been changed

  1. You can copy entire rows of data and the check for duplicats flag them. It will not be possible to save copied rows, unless:

     * No DrawMGT-Nr. is assigned to the rows (in Col R)

     * You must change the documet codes to avoid duplicates



== 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

  1. 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  ifferences occur, then the cell contains the server (new) value and a cell note is appended to the cell with the original (old) spre dsheet value.





= Demo Script (old) =



  1. '''DrawMGT Display'''



     1. Change to Bereiche '''LA2'''

     1. DrawMGT menu: '''Dokumente --> Suchen'''

        * ''Bereich'' - '''Aktueller Bereich'''

        * ''Kontakt'' - '''213'''

        * ''Laufnummer'' - '''1000 --> 1020'''

     1. Select document '''LA2-E-6-03-351-213-10_0003-v__'''

        * Display workflow detail for revision '''V__''', the ''Vorabzug''



  1. '''Excel Download'''



     1. Menu item: '''Search Documents'''

        * ''Rapport'' - Select '''Bau'''

        * ''Bereich'' - Select '''LA2'''

        * ''Kontakt'' - Select '''213'''

        * ''Laufnummer'' - Select '''1002 --> 1005'''

 

  1. '''Excel Update'''



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



     1. Update ''T0'' and ''Delta'' value:

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

        * Column AS(28): ''Delta7'': Change from 28 to 25



     1. 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''



     1. 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

 

  1. '''Excel Save'''



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

     1. Menu item: '''Save Selected'''

 

  1. '''DrawMGT Re-Display'''



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

     1. Verify changes from Excel displayed

 

  1. '''DrawMGT Update'''



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

     1. Button '''Aendern'''

        * Change ''Stellunnahme Dauer'' (Delta7): from 25 to 29

        * Save

 

  1. '''Excel Refresh'''



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

     1. Menu item: '''Refresh Selected'''

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







= 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'''

  1. DrawMGT menu: '''Dokumente --> Suchen'''

     1. ''Bereich'' - '''Aktueller Bereich'''

     1. ''Kontakt'' - '''213'''

     1. ''Laufnummer'' - '''1000 --> 1020'''



Note that this displays all revisions associated with these documents:
+== Searching for Documents ==



Note that there is no longer any test data in the beta server and that ''Search'' will only show documents with revisions having the following revision types:
-Line 275:
+Line 158:
-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.
+When documents are 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 can T0 and the delta values. The ''Soll'' date felds  are colored to indicate that they are read-only

  1. Automatic creation of the document code, based on classification menu settings and the ''Laufnummer'' and ''Teil'' fields is implemeted

  1. ''Wer'', ''Ist'' and ''Status'' fields are implemented

  1. Title, scale and other text fields are implemented





=== Restrictions ===



Given the limited test data observe the following restrictions:



  1. You cannot change ''Bereich'' or ''Vorabzug ja/nein'' for a document that has already been saved

  1. You cannot not change ''Ver'' (revision codes)

  1. You can copy entire rows of data and use the ''Check for Duplicats'' to flag them.

     * It will not be possible to save copied rows, unless unique document codes are  assigned to the rows. Enter them manually or use the ''Get Next Sequence'' menu item



== 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

  1. 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  ifferences occur, then the cell contains the server (new) value and a cell note is appended to the cell with the original (old) spre dsheet value.
-Line 286:
+Line 196:
   * ''Excel 2007'' and ''Excel 2010''

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