• ExcelIntegrationInternals

Excel Integration - Internal Documentation and Implementation Notes

• Back

Client Site

Header Structure

Formula definitions

Example formula:

•     _^^revCT0_^^row_-_^^revCVersandDelta1_^^row

The formula is split on the '_' character, which results in the following array:

• Formula Component	Meaning	Example Value
  ^^revCT0	The column for the field revCT0	AB
  ^^row	The current row number	27
  -	Minus sign	-
  ^^revCVersandDelta1	The column for the field revCVersandDelta1	BD
  ^^row	The current row number	27

For the example values given above, the result is the following formula:

•      AB27-BD27

Forms

VBA Modules

Server Side

Proposal for processing valid revisions server side

• Legacy browser data is problematic for the new excel tool. The excel tool expects the following revision pattern.

Using sql, we have produced a prototype query that could be used as a design spec for a new rest transaction. The tool currently uses the following rest api to return a list of drawings /dev/linthal115dev/documents/list?&maxRecords=50&con tractId=310&siteClassification2Id=2&allRevisions=1&revisionTypeIds=8,14,13,15&maxRecords=50. The prototype query consists of an outer query with 4 sub queries.

Qry1 (most constrained)

select drw.drawingId,group_concat(revType.code separator ', ') concatsequence from Drawings drw, Revisions rev,RevisionTypeRef revType WHERE rev.drawingId = drw.drawingId and revType.revisionTypeId = rev.revisionTypeId and drw.contractId = 120 and drw.siteClassification2Id = 2 group by drw.drawingId

The first query constrains the data by user parameters as well as a right join to RevisionTypeRef. The aggregate function group_concat is used to print the revision code sequence.

Qry2

Qry2 uses Qry1 and constrains the data by the axpo revision pattern.

select tblRevisionSquence.drawingId from (Qry1) where concatsequence in ('VA',

• 'VA, ZF', 'VA, ZF, FR', 'VA, ZF, GA', 'ZF', 'ZF, FR', 'ZF, GA'))

Qry3