• ExcelIntegrationHandlingServerUpdates

Excel Integration - Handling Server Updates

• 2011-07-25, AH

Introduction

This section describes a proposal for handling updates made to plan dates in revision workflows using DrawMGT's web interface. This is to solve the problem that plan dates entered via a browser are lost when they are overwritten by the plan date calculated in the Excel spreadsheet.

Plan Date Calculations

In the Excel spreadsheet, the plan date for a revision's workflow step is calculated based on:

1. The plan date field associated with the following workflow step, or the T0 field for last workflow step

2. The delta field associated with the workflow step

Normally the plan date calculations are performed in the Excel spreadsheet and the results are stored in the DrawMGT database. Using the Excel spreadsheet, the plan dates fields are always calculated and the results are displayed in read-only columns. The plan dates can only be changed indirectly by changing the T0 and delta fields. This means than the plan dates and delta fields for a series of revisions are always consistent when created/updated using the Excel spreadsheet.

The plan dates and delta information can also be viewed and updated using using DrawMGT's web interface. When using DrawMGT's web interface it is possible to save plan dates and delta fields without regard to the calculations performed by the Excel spreadsheet. This results in inconsistent data, where plan dates do not match those that would be calculated by the Excel spreadsheet.

When workflow information is read into the Excel spreadsheet, plan dates values from the database are ignored and replaced by values calculated locally in the spreadsheet. The result is the loss of any plan date information entered via the browser that is not consistent with the T0 and delta values.

The problem is compounded because the T0 field, the initial value from which all the plan dates are calculated, is stored in a used defined field in the DrawMGT database and can only be entered and updated via the Excel spreadsheet. This means that all currently entered plan date values are at risk of being overwritten by the Excel spreadsheet.

Proposed Solution

The proposed solution has two parts:

1. Migrating existing plan date fields and making corrections to ensure consistent T0 and delta values

2. Highlighting inconsistent plan dates in the Excel spreadsheet, giving users the chance to make corrections

Migrating Existing Plan Dates

The plan date data currently stored in the production database does not have any T0 or delta values associated with it. The T0 and delta values are stored in user defined fields and are only available in DrawMT V11.5.

The migration of the current V11.3 database to V11.5 should attempt to set T0 and delta values where possible.

Conditions for setting T0 and delta values in a revision:

1. The revision must be of one of the following types:

   • gut zur Ausführung

   • Freigegeben

   • zur Freigabe

   • Vorabzug

2. The revisions must have one or more plan date values set

Assumptions for setting workflow plan dates, delta and T0 values:

1. Plan dates in the database take precedence over delta values

   • Delta values can be adjusted to ensure the consistency between the plan dates

2. Default delta values can be used to calculate the T0 date and any missing plan dates

3. Documents which have consistency errors that cannot be easily be resolved, will be placed in a list for manual checking and correction

Suggested algorithm for setting the plan T0, date and effort fields (will be part of the V11.3 to V11.5 database migration):

1. Create a temporary table to contain, in a single record, all the revision ids, revision type ids and the following workflow fields:
   • All plan date fields

   • All delta fields (effort fields)

   • T0 field

   • T0wv field (?)

2. In a revision sequence, find the first and last workflow steps that have plan dates set

   • Set default delta values and calculate plan dates backwards from the first plan date

   • Set default delta values and calculate plan dates and the T0 date forwards from the last plan date

   • To do: Define an algorithm for setting the delta values and plan dates between the first and last plan dates that are already set. It must be able to handle:

     • Intermediate plan dates set

     • Intermediate delta values set

3. Use the completed temporary table, which will now have consistent T0, delta values and plan dates, to update corresponding fields in the original revisions records

Migration Issues

1. Current only the revision type Vorabzug is defined in the V11.3 database. There are no revisions of type zur Freigabe, gut zur Auzführung or Freigegeben. Need to determine if any existing revisions should have their revision types changed so that they are recognized as planning documents.

Highlighting Inconsistent Plan Date in Excel

It will always be possible to save inconsistent and incomplete delta values and plan dates using a web browser. Therefore the Excel spreadsheet must be able to highlight inconsistent data for manual checking and correction.

• Proposal: Create cell comments containing the plan dates read from the database, when they do not match those calculated by the Excel spreadsheet

Notes:

1. The normal date calculations will always be performed

2. The user manually enters/adjusts the T0 and delta values to match the plan dates in the cell comments