Metadata Upload File Format

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
2. Revision definition fields
3. 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

• Documents
  Column Name	Data Type	Req	Form Field Mapping	DB Field Mapping	Ref. Data	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. 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. 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
  Revisions
  Column Name	Data Type	Req	Form Field Mapping	DB Field Mapping	Ref. Data	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
  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 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 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
  Comments
  Column Name	Data Type	Req	Form Field Mapping	DB Field Mapping	Ref. Data	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; 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:

• Input Field	Database Field	Description
  commentTypeId	Comments.commentTypeId	Currently supports: 1 = Request for Comment (RFC) 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.

• Document	Revision	Comment	System Response
  None	None	None	Blank line ignored.
  None	None	New	Warning: Comment ignored.
  None	New	None	Revision ignored.
  None	New	New	Revision ignored. Comment ignored.
  None	Old	None	Revision ignored.
  None	Old	New	Revision ignored. Comment ignored.
  New	None	None	New document created.
  New	None	New	New document created. Warning: Comment ignored.
  New	New	None	New document created. New revision created.
  New	New	New	New document created. New revision created. New comment created.
  New	Old	None	Impossible.
  New	Old	New	Impossible.
  Old	None	None	Warning: Cannot update document.
  Old	None	New	Warning: Cannot update document. Warning: Comment ignored.
  Old	New	None	New revision created. Document ignored.
  Old	New	New	New revision created. New comment created. Document ignored.
  Old	Old	None	Revision updated. Document ignored.
  Old	Old	New	Revision updated. Document ignored. Warning: Comment ignored.

Field Categories

• Text	Description
  None	The item is not defined. * 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. * Revisions: There is no value for the revision code field. * Comments: None of the Comment table fields have values.
  New	The item is New: * Documents: A New drawing code, not known to the system, is defined. * Revisions: A New revision code, for the given drawing code, is defined. * Comments: One or more of the Comment table fields have values.
  Old	The item is Old: * Documents: A drawing code, already defined in the system, is present. * Revisions: The revision code for the given drawing code already exists in the system. * 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.

• Field Name	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