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.

SQL proposal

• drop table if exists t1;
  create temporary table t1 (
    drawingId int,
    allRevisionIds varchar(255),
    allRevTypeCodes varchar(255),
    revisionIds varchar(255),
    revTypeCodes varchar(255),
    seqType varchar(3)
  );
  
  insert into t1 ( drawingId, allRevisionIds, allRevTypeCodes )
  select
    drw.drawingId,
    group_concat(rev.revisionId order by rev.revisionNo separator ','),
    group_concat(revType.code order by rev.revisionNo separator ',')
  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
  having group_concat(revType.code order by rev.revisionNo separator ',') regexp
    'VA$|VA,ZF$|VA,ZF,FR$|VA,ZF,GA$|ZF$|ZF,FR$|ZF,GA$';
  
  update t1 set seqType =
    case
    when allRevTypeCodes regexp 'VA$'                 then 'A'
    when allRevTypeCodes regexp 'VA,ZF$'              then 'AB'
    when allRevTypeCodes regexp 'VA,ZF,FR$|VA,ZF,GA$' then 'ABC'
    when allRevTypeCodes regexp 'ZF$'                 then 'B'
    when allRevTypeCodes regexp 'ZF,FR$|ZF,GA$'       then 'BC'
    end;
  
  update t1 set
    revTypeCodes = substring_index( allRevTypeCodes, ',', -length(seqType) ),
    revisionIds = substring_index( allRevisionIds, ',', -length(seqType) );
  
  select * from t1;

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 /doucuments/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>