= Metadata Upload File Format =

<<TableOfContents>>



= Introduction =

This document describes the DrawMGT V11.1 Metadata Upload file format.

For a description of the DrawMGT V9.1 Metadata Upload file format, see Upload File Format V9.1.

== Differences Between V11.0 and V11.1 ==

DrawMGT V11.1 Format is compatible with V11.0 but contains two new fields:

    * plannedTitle0
    * title0

These fields are used to upload free text titles. These can only be used by Document Types that support free text and only as alternatives to the title fields that take code values:

    * plannedTitle
    * title

The field workFlowType has been removed in V11.1 as it is now derived from the Document Type. Data in this column will be ignored.

= Metadata Upload File Format =

The Metadata Upload file uses a simple spreadsheet format which contains columns and rows of data.

Each row represents a single record and may contain information about a document, revision and comment.

Each column represents the individual data fields used to build a database record.

Lines beginning with a '#' character are considered to be user information. These, as well as blank lines, are ignored by the Metadata Uploader.

The first row must contain column header information. The headers must match the column names described in the table in section 3.2. Metadata Uploader ignores data in columns that have misspelled or, otherwise don't match the header list and a warning is given. Thus, additional columns can be placed in the upload file and they will be safely ignored by the Metadata Uploader provided they do not conflict with the column names listed below.

All subsequent rows contain document, revision and comment data. Not all columns need to be defined. Again, section 3.2, describes which columns are required. This, however, is not a complete list and only describes requirements to pass system validation. The site may have additional validation requirements not described here. See the Site Documentation for additional information.

The column headers may be in any order. The data in each column should correspond be in the same order as the headers.

Column headers and values in all rows must be separated using a tab character.

The Metadata Upload format is identical to the Download with the exception that comment data is not included in the download.

The Download does, however, include the comment headers. Downloading an example document and revision may be a good way to create an upload file as this may be used as a valid format.

The upload is limited to 100 rows of data.

The Metadata Uploader sets any defaults defined in the system or site configurations. If a column is not defined in the file, the Metadata Uploader will just use these defaults. If a column is defined but the value is blank, the Metadata Uploader will set the field to blank.

Planned fields (e.g. planned title and scale) are considered a special case. If a document planned field contains a value, this will be used as the default for revision record.

= Upload Record Definition =

== Upload Record Components ==

The upload record consists of a series of fields which can be divided into three categories:

   1. Document definition fields
   1. Revision definition fields
   1. Comment definition fields

A single document, revision and comment may be created in one upload record. Alternatively a revision can be updated. The following restrictions apply:

    * Documents can be created, but existing documents cannot be updated.
    * New revisions can be created or existing revisions can be updated.
    * If a new revision is created, then a single comment can be created.
    * Comments cannot be updated.

The presence or absence of fields in each category lead to a number of possibilities. Section 5 lists all the possibilities and the system's response.

== Upload Record Fields ==

The following tables describe the fields handled by the Metadata Uploader and displays the following information:

    * '''Column Name''': The name used as the header
    * '''Data Type''': The expected data type and maximum length of the field
    * '''Req''': Required (Y or N)
    * '''Form Field Mapping''': The name used on the form if it differs from the column header
    * '''DB Field Mapping''': The database field name
    * '''Ref. Data''': The lookup used in the Reference Data to validate and store the reference value
    * '''Notes''': Additional Notes

  ||<colspan="7" bgcolor="#cddeee"> '''Documents''' ||
  ||<#cddeee> '''Column Name'''  ||<#cddeee> '''Data Type'''  ||<#cddeee> '''Req'''  ||<#cddeee> '''Form Field Mapping'''  ||<#cddeee> '''DB Field Mapping'''  ||<#cddeee> '''Ref. Data'''  ||<#cddeee> '''Notes''' ||
  || drawingCode        || char(64)     || Y    ||      || Drawings.drawingCode         ||      || Required for most document types. Not allowed for document types where the drawing code is automatically defined. ||
  || drawingCodeType    || char(16)     || Y    ||      || Drawings.drawingCodeTypeId   || Code         || Must be set either in the input record or as a default for Contract, Group and Document Type ||
  || classificationStatus       || char(16)     || N    ||      || Drawings.classificationStatusId      || Code         || Defaults to New ||
  || workFlowType       || char(16)     || N    ||      || Drawings.workFlowTypeId      || Code         || Derived from documentType. Ignored in 11.1 ||
  || projectPhase       || char(16)     || N    ||      || Drawings.projectPhaseId      || Code         ||  ||
  || workCategory       || char(16)     || N    ||      || Drawings.workCategoryId      || Code         ||  ||
  || documentType       || char(16)     || Y    ||      || Drawings.documentTypeId      || Code         || Must be set either in the input record or as a default for Contract and Group ||
  || contract   || char(16)     || N    ||      || Drawings.contractId  || Code         || Required if the drawing code doesn't provide the necessary data. Default is the users current contract. ||
  || group      || char(16)     || Y    ||      || Drawings.groupId     || Code         ||  ||
  || category   || char(16)     || N    ||      || Drawings.categoryId  || Code         || Required if the drawing code doesn't provide the necessary data. ||
  || location   || char(16)     || N    ||      || Drawings.locationId  || Code         || Required if the drawing code doesn't provide the necessary data. ||
  || workType   || char(16)     || N    ||      || Drawings.workTypeId  || Code         || Required if the drawing code doesn't provide the necessary data. ||
  || plannedTitle       || char(16)     || Y    || Planned Title 1      || Drawings.plannedTitle0Id     || Code         || Required if defining a new document.<<BR>>Used for Document Types that have a Coded Text Title 1. ||
  || plannedTitle0      || char(128)    || Y    || Planned Title 1      || Drawings.plannedTitle0       ||      || Required if defining a new document.<<BR>>Used for Document Types that have a Free Text Title 1. ||
  || plannedTitle1      || char(128)    || Y    || Planned Title 2      || Drawings.plannedTitle1       ||      || Required if defining a new document. ||
  || plannedTitle2      || char(128)    || Y    || Planned Title 3      || Drawings.plannedTitle2       ||      || Required if defining a new document. ||
  || plannedTitle3      || char(128)    || N    || Planned Title 4      || Drawings.plannedTitle3       ||      ||  ||
  || plannedScale       || char(32)     || N    ||      || Drawings.plannedScale        ||      ||   ||
  || author     || char(128)    || N    ||      || Drawings.author      ||      ||   ||
  || abstract   || text         || N    ||      || Drawings.abstract    ||      || Upload only ||
  || sheetNo    || integer      || N    ||      || Drawings.sheetNo     ||      ||   ||
  || alignment  || char(16)     || N    ||      || Drawings.alignmentId         || Code         ||   ||
  || chainageFrom       || float        || N    ||      || Drawings.chainageFrom        ||      || May also be entered as n+nnn ||
  || chainageTo         || float        || N    ||      || Drawings.chainageTo  ||      || May also be entered as n+nnn ||
  || elevationFrom      || float        || N    ||      || Drawings.elevationFrom       ||      ||   ||
  || elevationTo        || float        || N    ||      || Drawings.elevationTo         ||      ||   ||
  || supplierCompany    || char(16)     || Y    || Supplier     || Drawings.supplierCompanyId   ||      ||   ||
  || kksProcess         || char(64)     || N    ||      || Drawings.kksProcess  ||      ||   ||
  || kksInstallation    || char(64)     || N    ||      || Drawings.kksInstallation     ||      ||   ||
  || kksLocation        || char(64)     || N    ||      || Drawings.kksLocation         ||      ||   ||
  || externalKeyDrawing         || char(64)     || N    || External Key         || Drawings.externalKey         ||      ||   ||
  ||<colspan="7" bgcolor="#cddeee"> '''Revisions''' ||
  ||<#cddeee> '''Column Name'''  ||<#cddeee> '''Data Type'''  ||<#cddeee> '''Req'''  ||<#cddeee> '''Form Field Mapping'''  ||<#cddeee> '''DB Field Mapping'''  ||<#cddeee> '''Ref. Data'''  ||<#cddeee> '''Notes''' ||
  || revisionType       || char(16)     || Y    ||      || Revisions.revisionTypeId     || Code         || Required if revision code not defined. ||
  || revisionCode       || char(64)     || Y    ||      || Revisions.revisionCode       ||      || Required if revision upload/update defined. ||
  || revisionNo         || integer      || N    ||      || Revisions.revisionNo         ||      || Required if revision code not defined. ||
  || startDate  || date         || Y    ||      || Revisions.startDate  ||      ||   ||
  || startResponsibleInitials   || char(8)      || Y    ||      || Revisions.startResponsibleId         || Initials     ||   ||
  || designPlanDate     || date         || N    ||      || Revisions.designPlanDate     ||      ||   ||
  || designCompleteDate         || date         || N    ||      || Revisions.designCompleteDate         ||      ||   ||
  || designResponsibleInitials  || char(8)      || N    ||      || Revisions.designResponsibleId        || Initials     || Required if designCompleteDate defined. ||
  || drafterResponsibleInitials         || char(8)      || N    ||      || Revisions.drafterResponsibleId       || Initials     ||   ||
  || checkPlanDate      || date         || N    ||      || Revisions.checkPlanDate      ||      ||   ||
  || checkCompleteDate  || date         || N    ||      || Revisions.checkCompleteDate  ||      ||   ||
  || checkResponsibleInitials   || char(8)      || N    ||      || Revisions.checkResponsibleId         || Initials     || Required if checkCompleteDate defined. ||
  || receivePlanDate    || date         || N    ||      || Revisions.receivePlanDate    ||      ||   ||
  || receiveCompleteDate        || date         || N    ||      || Revisions.receiveCompleteDate        ||      ||   ||
  || receiverInitials   || char(8)      || N    ||      || Revisions.receiverId         || Initials     || Required if receiveCompleteDate defined. ||
  || approve1PlanDate   || date         || N    ||      || Revisions.approve1PlanDate   ||      ||   ||
  || approve1CompleteDate       || date         || N    ||      || Revisions.approve1CompleteDate       ||      ||   ||
  || approver1Initials  || char(8)      || N    ||      || Revisions.approver1Initials  || Initials     || Required if approve1CompleteDate defined. ||
  || approval1Status    || date         || N    ||      || Revisions.approval1Status    || Code         || Required if approve1CompleteDate defined. Defaults to Open ||
  || approve2PlanDate   || date         || N    ||      || Revisions.approve2PlanDate   ||      ||   ||
  || approve2CompleteDate       || date         || N    ||      || Revisions.approve2CompleteDate       ||      ||   ||
  || approver2Initials  || char(8)      || N    ||      || Revisions.approver2Initials  || Initials     || Required if approve2CompleteDate defined. ||
  || approval2Status    || date         || N    ||      || Revisions.approval2Status    || Code         || Required if approve2CompleteDate defined. Defaults to Open ||
  || approve3PlanDate   || date         || N    ||      || Revisions.approve3PlanDate   ||      ||   ||
  || approve3CompleteDate       || date         || N    ||      || Revisions.approve3CompleteDate       ||      ||   ||
  || approver3Initials  || char(8)      || N    ||      || Revisions.approver3Initials  || Initials     || Required if approve3CompleteDate defined. ||
  || approval3Status    || date         || N    ||      || Revisions.approval3Status    || Code         || Required if approve3CompleteDate defined. Defaults to Open ||
  || approve4PlanDate   || date         || N    ||      || Revisions.approve4PlanDate   ||      ||   ||
  || approve4CompleteDate       || date         || N    ||      || Revisions.approve4CompleteDate       ||      ||   ||
  || approver4Initials  || char(8)      || N    ||      || Revisions.approver4Initials  || Initials     || Required if approve4CompleteDate defined. ||
  || release3Status     || date         || N    ||      || Revisions.release3Status     || Code         || Required if releaseCompleteDate defined. Defaults to Open ||
  || releasePlanDate    || date         || N    ||      || Revisions.releasePlanDate    ||      ||   ||
  || release4CompleteDate       || date         || N    ||      || Revisions.release4CompleteDate       ||      ||   ||
  || releaser4Initials  || char(8)      || N    ||      || Revisions.releaser4Initials  || Initials     || Required if release4CompleteDate defined. ||
  || release4Status     || date         || N    ||      || Revisions.release4Status     || Code         || Required if releaseCompleteDate defined. Defaults to Open ||
  || submitPlanDate     || date         || Y    ||      || Revisions.submitPlanDate     ||      ||   ||
  || submitterInitials  || char(8)      || N    ||      || Revisions.submitterInitials  || Initials     ||   ||
  || title      || char(16)     || N    || Title 1      || Revisions.title0     || Code         || Defaults to Drawing Planned Title 1<<BR>>Used for Document Types that have a Coded Text Title 1. ||
  || title0     || char(128)    || N    || Title 1      || Revisions.title0     ||      || Defaults to Drawing Planned Title 1<<BR>>Used for Document Types that have a Free Text Title 1. ||
  || title1     || char(128)    || N    || Title 2      || Revisions.title1     ||      || Defaults to Drawing Planned Title 2 ||
  || title2     || char(128)    || N    || Title 3      || Revisions.title2     ||      || Defaults to Drawing Planned Title 3 ||
  || title3     || char(128)    || N    || Title 4      || Revisions.title3     ||      || Defaults to Drawing Planned Title 4 ||
  || scale      || char(32)     || N    ||      || Revisions.scale      ||      || Defaults to Drawing Planned Scale ||
  || size       || char(16)     || N    ||      || Revisions.size       || Code         ||   ||
  || orientation        || char(16)     || N    ||      || Revisions.orientation        || Code         ||   ||
  || description        || char(128)    || N    ||      || Revisions.description        ||      ||   ||
  || reasonForIssue     || char(128)    || Y    ||      || Revisions.reasonForIssue     ||      ||   ||
  || requestorCompany   || char(16)     || Y    || Requestor    || Revisions.requestorCompanyId         || Code         ||   ||
  || externalKeyRevision        || char(64)     || N    || External Key         || Revisions.externalKey        ||      ||   ||
  ||<colspan="7" bgcolor="#cddeee"> '''Comments''' ||
  ||<#cddeee> '''Column Name'''  ||<#cddeee> '''Data Type'''  ||<#cddeee> '''Req'''  ||<#cddeee> '''Form Field Mapping'''  ||<#cddeee> '''DB Field Mapping'''  ||<#cddeee> '''Ref. Data'''  ||<#cddeee> '''Notes''' ||
  || Column Name        || Data Type    || Req  || Form Field Mapping   || DB Field Mapping     || Ref. Data    || Notes ||
  || userInitials       || char(8)      || Y    || Issuer       || Comments.issuerUserId        || Initials     || If comment defined. ||
  || summary    || char(255)    || Y    || Summary      || Comments.summary     ||      || If comment defined. ||
  || commentTypeId      || char(16)     || Y    || Type         || Comments.commentTypeId       ||      || Supported Types: 1=RFC; 2=DWG;<<BR>>If comment defined. ||
  || externalKey        || char(64)     || N    ||      || Comments.externalKey         ||      ||   ||




= Field Values =

This section defines the formats and values acceptable for each field.

== Drawing Code Syntax ==

Please refer to the drawing code syntax documentation for the site specific drawing code formats.

== Date Field Values ==

Dates must be specified in '''YYYY-MM-DD''' format. Notes:

    * Plan dates must occur in order. E.g.:
          * Start Date <= Design Plan Date <= Check Plan Date <= Approve Plan Date <= Submit Plan Date
    * Complete dates must occur in order. E.g.:
          * Design Complete Date <= Check Complete Date
    * Plan dates are independent of complete dates.
    * No gaps may appear in the series of complete dates. If a date is specified, then all date fields before it must also be specified. I.e. if a date field is left blank, then date fields that follow it must also be left blank.

== Initials Field Values ==

Initials must be specified exactly as defined in the initials column of the users list. Note that the initials include a '/' and a company designation. See the user's list (select from the left hand menu) for the actual values.

== ID & Code Values ==

The following field requires a numerical value:

 ||<#cddeee> '''Input Field'''   ||<#cddeee> '''Database Field'''         ||<#cddeee> '''Description''' ||
 || commentTypeId       || Comments.commentTypeId       || Currently supports:<<BR>> 1 = Request for Comment (RFC)<<BR>> 2 = Drawing Comment (DWG) ||

All other database reference table fields are handled using the reference data type defined in section 3. For example, approval status uses the 'code' reference data value. Therefore, setting the value in column approval1Status to "0" will set the revision to "Open".

For a complete list of reference data ID values and codes, please consult the Reference Data List.

= Upload Record Types =

This section lists all the possible combinations of upload records and the system's response to them.

== Upload Record Types and System Response ==

The following tables describes the system's response to all possible combinations of documents, revisions and comments. The exact meaning of none, new and old is described in the next section.

  ||<#cddeee> '''Document''' ||<#cddeee> '''Revision'''         ||<#cddeee> '''Comment'''       ||<#cddeee> '''System Response''' ||
  ||<#e0ffff> None      ||<#e0ffff> None        ||<#e0ffff> None        || Blank line ignored. ||
  ||<#e0ffff> None      ||<#e0ffff> None        ||<#ffffe0> New         || '''Warning:''' Comment ignored. ||
  ||<#e0ffff> None      ||<#ffffe0> New         ||<#e0ffff> None        || Revision ignored. ||
  ||<#e0ffff> None      ||<#ffffe0> New         ||<#ffffe0> New         || Revision ignored. Comment ignored. ||
  ||<#e0ffff> None      ||<#ffe0ff> Old         ||<#e0ffff> None        || Revision ignored. ||
  ||<#e0ffff> None      ||<#ffe0ff> Old         ||<#ffffe0> New         || Revision ignored. Comment ignored. ||
  ||<#ffffe0> New       ||<#e0ffff> None        ||<#e0ffff> None        || New document created. ||
  ||<#ffffe0> New       ||<#e0ffff> None        ||<#ffffe0> New         || New document created. '''Warning:''' Comment ignored. ||
  ||<#ffffe0> New       ||<#ffffe0> New         ||<#e0ffff> None        || New document created. New revision created. ||
  ||<#ffffe0> New       ||<#ffffe0> New         ||<#ffffe0> New         || New document created. New revision created. New comment created. ||
  ||<#ffffe0> New       ||<#ffe0ff> Old         ||<#e0ffff> None        || Impossible. ||
  ||<#ffffe0> New       ||<#ffe0ff> Old         ||<#ffffe0> New         || Impossible. ||
  ||<#ffe0ff> Old       ||<#e0ffff> None        ||<#e0ffff> None        || '''Warning:''' Cannot update document. ||
  ||<#ffe0ff> Old       ||<#e0ffff> None        ||<#ffffe0> New         || '''Warning:''' Cannot update document. '''Warning:''' Comment ignored. ||
  ||<#ffe0ff> Old       ||<#ffffe0> New         ||<#e0ffff> None        || New revision created. Document ignored. ||
  ||<#ffe0ff> Old       ||<#ffffe0> New         ||<#ffffe0> New         || New revision created. New comment created. Document ignored. ||
  ||<#ffe0ff> Old       ||<#ffe0ff> Old         ||<#e0ffff> None        || Revision updated. Document ignored. ||
  ||<#ffe0ff> Old       ||<#ffe0ff> Old         ||<#ffffe0> New         || Revision updated. Document ignored. '''Warning:''' Comment ignored. ||


== Field Categories ==


 ||<#cddeee> '''Text'''         ||<#cddeee> '''Description''' ||
 ||<#e0ffff> '''None'''                 || The item is '''not defined'''.<<BR>><<BR>>  * '''Documents:''' There is no value for the drawing code field. If the drawing code is missing, the entire record is ignored. A drawing code is always required.<<BR>>  * '''Revisions:''' There is no value for the revision code field.<<BR>>  * '''Comments:''' None of the Comment table fields have values. ||
 ||<#ffffe0> '''New'''          || The item is '''New''':<<BR>><<BR>>  * '''Documents:''' A New drawing code, not known to the system, is defined.<<BR>>   * '''Revisions:''' A New revision code, for the given drawing code, is defined.<<BR>>   * '''Comments:''' One or more of the Comment table fields have values. ||
 ||<#ffe0ff> '''Old'''          || The item is '''Old''':<<BR>><<BR>>  * '''Documents:''' A drawing code, already defined in the system, is present.<<BR>>  * '''Revisions:''' The revision code for the given drawing code already exists in the system.<<BR>>  * '''Comments:''' Not applicable. ||


= List of Fields Which Cannot Be Uploaded =

For completeness here is a list of the fields in the Drawings, Revisions and Comments tables which cannot be uploaded.

  ||<#cddeee> '''Field Name'''  ||<#cddeee> '''Value''' ||
  || Drawings.drawingId         || Automatically assigned ||
  || Drawings.obsoleteFlag      || Set to 0 ||
  || Drawings.lockedFlag        || Set to 0 ||
  || Revisions.revisionId       || Automatically assigned ||
  || Revisions.drawingId        || Automatically assigned ||
  || Revisions.lockedFlag       || Set to 0 ||
  || Revisions.supersededFlag   || Set to 0 ||
  || Revisions.submitCompleteDate       || Set to null ||
  || Revisions.submittalId      || Set to null ||
  || Revisions.submittalCode    || Set to null ||
  || Revisions.publishFilename  || Set to null ||
  || Revisions.publishFilesize  || Set to null ||
  || Revisions.publishFileChecksum      || Set to null ||
  || Comments.commentId         || Assigned automatically ||
  || Comments.revisionId        || Assigned automatically ||
  || Comments.lockedFlag        || Set to 0 ||
  || Comments.commentDatetime   || Set to current date/time at upload ||
  || Comments.responsibleUserId         || Set to null ||
  || Comments.attachmentFilename        || Set to null ||