About

The Maintenance Connection DataHub is an advanced "simple" integration system. The DataHub provides basic functionality enabling outside systems (Financial, Inventory, Monitoring, etc.) to interface with Maintenance Connection data without writing extensive customizations.

The DataHub is for more than a 1 time importer. While it can do an excellent job of that, it's real power comes from it's ability to bring data into live running systems, not just initial setup, and it can handle 'mistakes' where the sending system sends the same data multiple times.

To this end the DataHub has simple rules and capability limits that enable easy integration of basic data systems without adding excessive complexity. The technical details of what is supported by the DataHub are contained within this document.

Pick CSV if you can

Given your choice: Pick CSV, if not XLSX is your next best bet. The CSV recommendation is partly because XLSX may convert. For example, if you have a string "FE-1" it will be converted into a DATE format as "February-1st" possibly "Current Year". (Scientists have officially renamed some proteins because XLS and XLSX corrupt the old names.)

We do many things to try and recover the data, but can't always. So be careful using XLS and XLSX

Contents

About 1

Data Transfer Formats 3

Comma Separated Values (CSV) 3

Microsoft Excel (XLS) 97-2003 4

Date Conversion 5

Data Transfer Document/Spreadsheet 6

Configurable Date String Parsing 6

Boolean Conversion 8

True Values 8

False Values 8

Asset Meter 9

Details 9

Fields 9

Asset Specification 10

Details 10

Fields 10

Inventory Detail 11

Details 11

Fields 11

Inventory Cost 12

Details 12

Fields 12

Inventory Quantity 13

Details 13

Fields 13

Inventory Vendor 14

Details 14

Fields 14

Company Detail 15

Details 15

Fields 15

Customer Detail 16

Details 16

Fields 16

Account Detail 17

Details 17

Fields 17

People Detail 18

Details 18

Fields 18

Labor Cost 20

Details 20

Fields 20

Material Cost 22

Details 22

Fields 22

Other Cost 24

Details 24

Fields 24

Data Transfer Formats

The MC DataHub supports several different data transfer formats. Each transfer format has different requirements and capabilities making them suitable depending upon the source or destination system.

This information is intended for people with technical understanding of data transfer methods and explains the specifics of our use of standard file formats such as CSV (Comma Separated Values ) and XLS (Microsoft Excel format).

In this version there are two supported data transfer formats:

• Comma Separated Values (CSV) and
• Microsoft Excel (XLSX).
• Microsoft Excel Classic (XLS).

Comma Separated Values (CSV)

Comma separated values or CSV as it is commonly known is a common transfer format used in any number of systems. The format is quite capable and compact making it suitable for many different types of data transfers. The format also suffers from not having an official definition and many implementations are incompatible with each other.

If you are using software in a country that uses a comma for the decimal spot, be sure that you set the software to use a period as the decimal spot when exporting files for importing by the DataHub.

The MC DataHub supports a general baseline set of CSV functionality without any special customization that may be present in many different implementations. The requirements and features supported are:

• Column Headers Column headers must be specified as the first line of the CSV.
• Field Separators The separator between fields (or columns) must be a comma ( , ). No other separator is supported.
• Special Characters in Fields Fields may be quoted using a double quote ( " ). Within the field any number of extra spaces or commas will be accepted as part of the field rather than used for their official purposes. Double quotes can also appear within a quoted field by escaping them with a double quote ( "" ). Multi-line fields are also supported when included inside a double quote.
• Whitespace Whitespaces (spaces, tabs, etc.) are considered insignificant if they appear before or after a field separator (the beginning or end of a field). Whitespace inside a field is kept. If whitespace surrounding a field is important it must be contained inside a quoted field.
• Line Breaks Lines must be terminated with a standard line break. Windows, Mac and Linux/UNIX use different standard line breaks. All standard line breaks are supported.
• Commented lines If a line begins with a number sign ( # ) the line is considered to have been commented out and will be ignored.
• Column Order There is no enforced column order. Columns can be reordered and inserted however it makes the most sense to you.

Since CSVs are essentially text files there are some data format concerns that apply when data is formatted as text and must be recognized and properly imported.

• Dates Dates are read in using fixed format parsing. The fixed format is described globally for all imports and can be overridden for each individual import. Multiple or dynamic date formats are not supported. Reference help in the configuration area for more assistance.
• Numbers Numbers are recognized as numbers ( 0-9 ), an optional positive or negative leading symbol ( - + ), an optional positive or negative trailing symbol ( - + ), the decimal separator ( . ), leading or trailing whitespace is ignored. A field can optionally be wrapped in parenthesis () to indicate a negative number. Any other symbol or text item will cause the field to not be recognized as a number.
  • Known Issues (these issues may be fixed in a future release):
    • Leading dollar sign is ignored and does not raise an error
    • A comma in a number is ignored and does not raise an error

Microsoft Excel Classic (XLS) 97-2003 and XLSX

Microsoft Excel files are commonly found around almost any office environment. Excel is a flexible and capable format able to support almost any type of columnar data.

The MC DataHub supports most of the features that can be used for formatting data within Excel. The specific requirements of an Excel formatted spreadsheet are:

• Single Worksheet per DataHub connector If you leave the worksheet name blank - we use the first one. Only the first worksheet is read by default. The first worksheet can be named however you please. All other worksheets will be ignored. If you fill the worksheet in - we use that one. (This feature was added with 12.0)
• Column Headers The first row of the work sheet must be the column headers. The column lettering in Excel is not used.
• Irrelevant Columns Column headers that are unknown or not configured by the XLS reader are ignored. This allows data contained within to be used as source material for a formula driven column.
• Column Order There is no enforced column order. Columns can be reordered and inserted however it makes the most sense to you.
• Date formatting Dates are specifically understood by Excel and stored in a special manner. As long as Excel is able to reformat a date column to any other date format the XLS reader will be able to understand it.
  • Unsupported feature. If a pure text date is discovered the XLS reader will attempt to convert using the fixed format date parsing described in the CSV documentation. This is not recommended as Excel's own date functions are far more capable than this feature.

Date Conversion

The MC DataHub supports many different date formats. Since every data source may represent dates with different layouts and field orders the conversion of dates is configurable.

This information is intended for people with technical understanding of data transfer methods and explains the specifics of how date formats are converted into dates that are properly understood by Maintenance Connection.

In this version there are two places where date conversion takes place:

• Data Transfer Document/Spreadsheet and
• Configurable Date String Parser

Note regarding custom dates. Some dates are not represented with a day, month and year; these date formats are not currently supported and must be converted to a day, month, and year representation for date conversion to work. Also currently only dates represented on the Gregorian calendar are supported.

Data Transfer Document/Spreadsheet

Some data transfer documents have built in support for dates. An example of a document format with proper date support is Microsoft Excel (XLS). Documents with built in date format support store dates in a fashion that can be consistently retrieved without need for custom parsing.

Configurable Date String Parsing

For any dates that were not loaded from a date supporting document and additional date conversion will be attempted. Configurable on a per-import basis you can specify the string parsing to convert a string representing a date into a converted date.

The MC DataHub date parsing can be customized using a date and time format string. The following table describes the custom date and time format specifiers and an example of the result produced by each format specifier.

Boolean Conversion

The MC DataHub supports several different text representations of a Yes/No field. Since many systems can represent Yes/No using different formats the DataHub will understand many standard representations.

This information is intended for people with technical understanding of data transfer methods and explains the specifics of how Boolean text formats are converted into boolean/bit fields understood by Maintenance Connection.

Conversion is matched in a case insensitive manor. Matching must exactly match; partial matching or words containing these values will not be considered valid.

Note regarding custom Yes/No. This version of the MC DataHub does not understand any Boolean representation other than those specified below.

True Values

False Values

Asset Meter

This module is used to update the "Current Reading" value of meters assigned to your assets in the Maintenance Connection database. All other values must be maintained directly in Maintenance Connection. Values from this import replace the Current Reading.

Details

The purpose of this module is to synchronize an asset monitoring system with the Maintenance Connection asset database. Updating asset meter records is supported. All Maintenance Connection automation will function as if this was a manual update of a meter.

Fields

• No surrounding spaces
• Must be unique (AssetID + MeterNumber) per import
• Must exist | | MeterNumber | Numeric | | Yes |
• Must be valid meter number (1 or 2)
• Must be unique (AssetID + MeterNumber) per import | | MeterValue | Numeric | 19.6 | Yes |
• Positive number only
• No automatic meter rollover supported |

Asset Specification

This module is used to update the "Text", "Date" and/or "Numeric Value" of existing specifications assigned to your assets in the Maintenance Connection database. All other values must be maintained directly in Maintenance Connection. Values from this import replace the current values in the database.

Details

The purpose of this module is to synchronize an asset monitoring system with the Maintenance Connection asset database. Updating asset specification records is supported. All Maintenance Connection automation will function as if this was a manual update of a specification.

Fields

• Date Conversion |

Inventory Detail

This module is used to create and/or update inventory item details in the Maintenance Connection database. All other values (Quantity and Vendors) must be imported separately or maintained directly in Maintenance Connection. Values from this import create or replace values in the database.

Details

The purpose of this module is to synchronize 1 or more part lists with the Maintenance Connection inventory database. Creating and updating inventory items is supported. All Maintenance Connection automation will function as if this was a manual create or update of an inventory item.

Fields

• No surrounding spaces | | PartDescription | Text | 2000 | No | | | Active | Yes/No | | No |
• Boolean Conversion
• New record will default to True | | DirectIssue | Yes/No | | No |
• Boolean Conversion
• New record will default to False | | Model | Text | 50 | No | | | ManufacturerID | Text | 25 | No |
• No surrounding spaces
• Must exist | | MfgNumber | Text | 50 | No | | | Cost | Number | 15.4 | No |
• Positive number only
• New record will default to 0 | | Charge | Number | 15.4 | No |
• Positive number only
• New record will default to 0 | | ChargePercent | Number | 19.6 | No |
• Positive number only
• New record will default to 0 | | UDFChar1 | Text | 50 | No | | | UDFChar2 | Text | 50 | No | | | UDFChar3 | Text | 50 | No | | | UDFChar4 | Text | 50 | No | | | UDFChar5 | Text | 50 | No | | | UDFDate1 | Date | | No |
• Date Conversion | | UDFDate2 | Date | | No |
• Date Conversion | | UDFBit1 | Yes/No | | No |
• Boolean Conversion | | UDFBit2 | Yes/No | | No |
• Boolean Conversion |

Inventory Cost

This module is used to update the "Cost" and/or "Charge" of existing inventory items in the Maintenance Connection database. All other values must be maintained directly in Maintenance Connection. Values from this import replace the current values in the database.

Details

The purpose of this module is to overwrite existing "Cost" and "Charge" values for inventory items. This module does not perform any creation of inventory items. All Maintenance Connection automation will function as if this was a manual update of an inventory item.

Fields

• No surrounding spaces
• Must be unique per import
• Must exist | | Cost | Number | 15.4 | No |
• Positive number only | | Charge | Number | 15.4 | No |
• Positive number only | | ChargePercent | Number | 19.6 | No |
• Positive number only |

Inventory Quantity

This module is used to create and/or update inventory quantity in the Maintenance Connection database. All other values (Details and Vendors) must be imported separately or maintained directly in Maintenance Connection. Values from this import create or replace values in the database.

Details

The purpose of this module is to synchronize 1 or more stockroom quantity lists with the Maintenance Connection inventory database. Creating and updating quantities is supported. All Maintenance Connection automation will function as if this was a manual create or update of an inventory items part location.

Fields

• No surrounding spaces
• Must be unique (PartID + StockroomID) per import
• Must exist | | StockroomID | Text | 25 | Yes |
• No surrounding spaces
• Must be unique (PartID + StockroomID) per import
• Must exist | | IssueUnits | Text | 25 | Yes |
• No surrounding spaces
• Configurable default value of Each
• Automatically added to database list as required | | OnHand | Number | 19.6 | No |
• New record will default to 0 | | OnOrder | Number | 19.6 | No |
• New record will default to 0 | | Bin | Text | 100 | No | | | Lot | Text | 25 | No | | | Comments | Text | 4000 | No | |

Inventory Vendor

This module is used to create and/or update inventory vendor attachments in the Maintenance Connection database. The vendors must already exist. All other values (Details and Quantity) must be imported separately or maintained directly in Maintenance Connection. Values from this import create or replace values in the database.

Details

The purpose of this module is to synchronize 1 or more inventory vendor lists with the Maintenance Connection inventory database. Creating and updating inventory vendors is supported. All Maintenance Connection automation will function as if this was a manual create or update of an inventory items vendor attachment.

Fields

• No surrounding spaces
• Must be unique (PartID + VendorID) per import
• Must exist | | VendorID | Text | 25 | Yes |
• No surrounding spaces
• Must be unique (PartID + VendorID) per import
• Must exist | | OrderUnits | Text | 25 | Yes |
• No surrounding spaces
• Configurable default value of Each
• Automatically added to database list as required | | Price | Number | 15.4 | Yes |
• Positive number only
• Configurable default value of 0 | | CatalogItemID | Text | 100 | Yes | | | CatalogNumber | Text | 50 | No | | | CatalogURL | Text | 200 | No | | | Comments | Text | 4000 | No | |

Company Detail

This module is used to create and/or update companies (Vendors, Manufacturers and Internal) in the Maintenance Connection database. Values from this import create or replace values in the database.

Details

The purpose of this module is to synchronize 1 or more company lists with the Maintenance Connection database. Creating and updating companies is supported. All Maintenance Connection automation will function as if this was a manual create or update of a company.

Fields

• No surrounding spaces
• No single quotes ( ' )
• No special characters ( & % # " * + - < > )
• Must be unique per import | | CompanyName | Text | 100 | Yes |
• No surrounding spaces | | Type | Text | | Yes |
• No surrounding spaces
• Configurable default value of V
• Available options:
  • V or Vendor
  • I or Internal
  • M or Manufacturer
• Case insensitive | | URL | Text | 200 | No | | | DunsNum | Text | 50 | No | | | Attention | Text | 50 | No | | | Address | Text | 50+50 | No |
• Automatically split into 2 lines
• Maximum length of 50 per line | | City | Text | 50 | No | | | State | Text | 50 | No | | | Zip | Text | 50 | No | | | Phone | Text | 20 | No | | | Fax | Text | 20 | No | | | Comments | Text | 2000 | No | |

Customer Detail

This module is used to create and/or update customers in the Maintenance Connection database. Values from this import create or replace values in the database.

Details

The purpose of this module is to synchronize 1 or more customer lists with the Maintenance Connection database. Creating and updating customers is supported. All Maintenance Connection automation will function as if this was a manual create or update of a customer.

Fields

• No surrounding spaces
• No single quotes ( ' )
• No special characters ( & % # " * + - < > )
• Must be unique per import | | CustomerName | Text | 100 | Yes |
• No surrounding spaces | | Type | Text | 25 | Yes |
• No surrounding spaces
• Configurable default value of C
• Case insensitive
• Automatically added to database list as required | | RepairCenterID | Text | 25 | No |
• No surrounding spaces
• Must exist | | URL | Text | 200 | No | | | Attention | Text | 50 | No | | | Address | Text | 50+50 | No |
• Automatically split into 2 lines
• Maximum length of 50 per line | | City | Text | 50 | No | | | State | Text | 50 | No | | | Zip | Text | 50 | No | | | Phone | Text | 20 | No | | | Fax | Text | 20 | No | | | Comments | Text | 2000 | No | |

Account Detail

This module is used to create and/or update accounts in the Maintenance Connection database. Values from this import create or replace values in the database.

Details

The purpose of this module is to synchronize 1 or more account lists with the Maintenance Connection database. Creating and updating accounts is supported. All Maintenance Connection automation will function as if this was a manual create or update of an account.

Fields

• No surrounding spaces
• No single quotes ( ' )
• No special characters ( & % # " * + - < > )
• Must be unique per import | | AccountName | Text | 50 | Yes |
• No surrounding spaces | | Type | Text | 25 | Yes |
• No surrounding spaces
• Configurable default value of GL
• Case insensitive
• Automatically added to database list as required | | Description | Text | 150 | No | | | RepairCenterID | Text | 25 | No |
• No surrounding spaces
• Must exist | | DepartmentID | Text | 25 | No |
• No surrounding spaces
• Must exist | | SupervisorID | Text | 25 | No |
• No surrounding spaces
• Must exist | | Budget | Number | 15.4 | No | | | Comments | Text | 4000 | No | |

People Detail

This module is used to create and/or update "People" (Employees, Requesters, Contacts, and Contractors) in the Maintenance Connection database. Values from this import create or replace values in the database.

Details

The purpose of this module is to synchronize 1 or more people lists with the Maintenance Connection database. Creating and updating people is supported. All Maintenance Connection automation will function as if this was a manual create or update of an Employee, Requester, Contact or Contractor record.

Fields

• No surrounding spaces
• No single quotes ( ' )
• No special characters ( & % # " * + - < > )
• Must be unique per import | | LaborName | Text | 50 | Yes |
• No surrounding spaces | | Type | Text | | Yes |
• No surrounding spaces
• Configurable default value of REQ
• Case insensitive
• Available options:
  • Contact
  • Req or Requester
  • Emp or Employee
  • Con or Contractor | | Initials | Text | 5 | No | | | JobTitle | Text | 50 | No | | | Comments | Text | 2000 | No | | | RepairCenterID | Text | 25 | No |
• No surrounding spaces
• Must exist
• Required if Type is:
  • Emp or Employee
  • Con or Contractor | | CompanyID | Text | 25 | No |
• No surrounding spaces
• Must exist
• Ignored if Type is:
  • Contact | | DepartmentID | Text | 25 | No |
• No surrounding spaces
• Must exist
• Ignored if Type is:
  • Contact | | CraftID | Text | 25 | No |
• No surrounding spaces
• Must exist
• Required if Type is:
  • Emp or Employee
  • Con or Contractor
• Ignored if Type is:
  • Contact
  • Req or Requester | | Contact | Text | 50 | No |
• Ignored if Type is:
  • Contact
  • Req or Requester | | Address | Text | 80 | No | | | City | Text | 50 | No | | | State | Text | 50 | No | | | Zip | Text | 15 | No | | | Country | Text | 50 | No | | | Home | Text | 30 | No | | | Work | Text | 30 | No | | | Mobile | Text | 30 | No | | | Fax | Text | 30 | No | | | Email | Text | 50 | No | | | Pager | Text | 50 | No | | | PagerEmail | Text | 50 | No |
• Ignored if Type is:
  • Contact
  • Req or Requester | | PDAID | Text | 25 | No |
• Ignored if Type is:
  • Contact
  • Req or Requester | | URL | Text | 200 | No | |

Labor Cost

This module is used to create new labor cost actuals for existing work orders in the Maintenance Connection database. Costs are checked for uniqueness to prevent duplication. All other values must be maintained directly in Maintenance Connection. Values from this import create new values in the database.

Details

The purpose of this module is to import work order labor costs into the Maintenance Connection database. Creating work order labor costs is supported. All Maintenance Connection automation will function as if this was a manual create of a work order cost.

Costs are scanned for uniqueness in the database, if an identical cost has already been imported or are within the source spreadsheet the cost will be considered invalid.

Change work order status settings (if set) are applied after all records have been successfully imported. A status change of Close will take precedence over any other status change applied to the same work order in the same spreadsheet.

Fields

• No surrounding spaces
• Must exist
• Work Order must be Open | | ChangeWOStatus | Text | | No |
• No surrounding spaces
• Available options:
  • C, Close, or Closed
  • F, Final, or Finalized | | LaborID | Text | 25 | Yes |
• No surrounding spaces
• Must exist | | CostDate | Date | | Yes |
• Date Conversion | | Cost | Number | 15.4 | No | | | Charge | Number | 15.4 | No | | | RegularHours | Number | 19.6 | No |
• Positive number only | | OvertimeHours | Number | 19.6 | No |
• Positive number only | | OtherHours | Number | 19.6 | No |
• Positive number only | | AccountID | Text | 25 | No |
• No surrounding spaces
• Must exist | | CategoryID | Text | 25 | No |
• No surrounding spaces
• Must exist | | Comments | Text | 7000 | No | |

Material Cost

This module is used to create new material cost actuals for existing work orders in the Maintenance Connection database. Costs are checked for uniqueness to prevent duplication. All other values must be maintained directly in Maintenance Connection. Values from this import create new values in the database.

Details

The purpose of this module is to import work order material costs into the Maintenance Connection database. Creating work order material costs is supported. All Maintenance Connection automation will function as if this was a manual create of a work order material cost.

Costs are scanned for uniqueness in the database, if an identical cost has already been imported or are within the source spreadsheet the cost will be considered invalid.

Change work order status settings (if set) are applied after all records have been successfully imported. A status change of Close will take precedence over any other status change applied to the same work order in the same spreadsheet.

Fields

• No surrounding spaces
• Must exist
• Work Order must be Open | | ChangeWOStatus | Text | | No |
• No surrounding spaces
• Available options:
  • C, Close, or Closed
  • F, Final, or Finalized | | ItemID | Text | 25 | Yes |
• No surrounding spaces
• Must exist | | LocationID | Text | 25 | Yes |
• No surrounding spaces
• Must exist
• Alternative fixed locations:
  • (Out of Pocket)
  • (Directly Issued) | | CostDate | Date | | Yes |
• Date Conversion | | Quantity | Number | 19.6 | Yes | | | OtherCost | Number | 15.4 | No |
• Positive number only | | ActualCost | Number | 15.4 | No |
• Positive number only | | Charge | Number | 15.4 | No |
• Positive number only | | AccountID | Text | 25 | No |
• No surrounding spaces
• Must exist | | CategoryID | Text | 25 | No |
• No surrounding spaces
• Must exist | | Comments | Text | 7000 | No | |

Other Cost

This module is used to create new other/misc. cost actuals for existing work orders in the Maintenance Connection database. Costs are checked for uniqueness to prevent duplication. All other values must be maintained directly in Maintenance Connection. Values from this import create new values in the database.

Details

The purpose of this module is to import work order other costs into the Maintenance Connection database. Creating work order other costs is supported. All Maintenance Connection automation will function as if this was a manual create of a work order other cost.

Costs are scanned for uniqueness in the database, if an identical cost has already been imported or are within the source spreadsheet the cost will be considered invalid.

Change work order status settings (if set) are applied after all records have been successfully imported. A status change of Close will take precedence over any other status change applied to the same work order in the same spreadsheet.

Fields

• No surrounding spaces
• Must exist
• Work Order must be Open | | ChangeWOStatus | Text | | No |
• No surrounding spaces
• Available options:
  • C, Close, or Closed
  • F, Final, or Finalized | | CostName | Text | 50 | Yes |
• No surrounding spaces | | CostDescription | Text | 1000 | No | | | InvoiceNumber | Text | 50 | No | | | ActualCost | Number | 15.4 | Yes |
• Positive number only | | Charge | Number | 15.4 | No |
• Positive number only | | Other1 | Text | 500 | No | | | Other2 | Text | 500 | No | | | Comments | Text | 7000 | No | | | CostDate | Date | | Yes |
• Date Conversion |