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

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.

A server side solution should help performance and reduce hard to maintain excel vba code.

q1 (q1 == query 1 == most constrained)

• select 
    drw.drawingId,
    group_concat(revType.code order by rev.revisionNo 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

q1 constrains the data by rest parameters and a join to RevisionTypeRef. The aggregate function group_concat is used to print the revision code sequence.

q2

q2 uses q1 and constrains the data by the axpo revision pattern.

select tblRevisionSquence.drawingId  from
  (q1)
where concatsequence in
 ('VA',
  'VA, ZF',
  'VA, ZF, FR',
  'VA, ZF, GA',
  'ZF',
  'ZF, FR',
  'ZF, GA'))

q3

q3 uses q2 and aggregates the data to get the leading edge. The leading edge is derived by the max function.

select
  drw.drawingId,rev.revisionId,max(rev.revisionNo)  
from
  Drawings drw, Revisions rev 
where 
  rev.drawingId = drw.drawingId
  and drw.drawingId in
  (q2)
group by drw.drawingId,rev.RevisionId

q4

q4 uses q3. q4 concatenates both revisionId and RevisionTypeCode

select 
  drw.drawingId,rev.revisionId,revType.code,group_concat(revType.code order by rev.revisionNo separator ', ') concatsequence,group_concat(rev.revisionId order by rev.revisionNo separator ',')     revsequence
from
  Drawings drw, Revisions rev,RevisionTypeRef revType 
where 
 rev.drawingId = drw.drawingId
 and  revType.revisionTypeId = rev.revisionTypeId
 and drw.drawingId in
 (q3)
group by drw.drawingId

q5

q5 uses q4 and decodes the data into the rev ABC pattern that Excel understands

select
  drawingId, CAST(revsequence AS CHAR CHARACTER SET utf8), CASE concatsequence when "VA" then "A"
when "VA, ZF" then "AB"
when "VA, ZF, FR" then "ABC"
when "VA, ZF, GA" then "ABC"
when "ZF" then "B"
when "ZF, FR" then "BC"
when "ZF, GA" then "BC"
END
FROM
(q4)

The complete query (q1 + q2 + q3 + q4 + q5)

select
  drawingId, CAST(revsequence AS CHAR CHARACTER SET utf8), CASE concatsequence when "VA" then "A"
when "VA, ZF" then "AB"
when "VA, ZF, FR" then "ABC"
when "VA, ZF, GA" then "ABC"
when "ZF" then "B"
when "ZF, FR" then "BC"
when "ZF, GA" then "BC"
END
FROM
(select drw.drawingId,rev.revisionId,revType.code,group_concat(revType.code order by rev.revisionNo separator ', ') concatsequence,group_concat(rev.revisionId order by rev.revisionNo separator ',') revsequence
from Drawings drw, Revisions rev,RevisionTypeRef revType 
where 
rev.drawingId = drw.drawingId
and  revType.revisionTypeId = rev.revisionTypeId
and drw.drawingId in (
select leadingEdgeDrawingIds.drawingId from
(select drw.drawingId,rev.revisionId,max(rev.revisionNo)  
from Drawings drw, Revisions rev 
where 
rev.drawingId = drw.drawingId
and drw.drawingId in 
 (select tblRevisionSquence.drawingId  from
(select drw.drawingId,group_concat(revType.code order by rev.revisionNo 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) as tblRevisionSquence
where concatsequence in
('VA',
 'VA, ZF',
 'VA, ZF, FR',
 'VA, ZF, GA',
 'ZF',
 'ZF, FR',
 'ZF, GA'))
group by drw.drawingId,rev.RevisionId) leadingEdgeDrawingIds
)
group by drw.drawingId) as leadingEdge

Proposal for new rest api /revisionSequences

Excel currently uses /documents/list to produce a search response for vba code to process. This requires some ugly vba to parse the response in reverse, attempting to identify the latest revision sequence. Tibor has produced some sql that accurately identifies the latest revisions in a revision sequence. I would propose the sql be wrapped by a rest api called /revisionSequences (working title, open to suggestion). The api would take the same parameters as /documents/list and return an xml structure similar to the one below.

<?xml version="1.0" encoding="UTF-8"?>
<recordSet
  name="revisionSequences"
  idFieldName="drawingId">
    <record type="" key="drawingId">
       <drawingCode> LA2-E-6-03-330-211-10_1002 </drawingCode>
       <drawingId>      33099    </drawingId>
        <updateCountDoc>      0    </updateCountDoc>
        <revisionIdA>      27058   </revisionIdA>
        <revisionIdB/>
        <revisionIdC/>
      </record>
</recordSet>