Skip to content

Excel Template#

In this section we will run through the Excel template and the different ways you can navigate through it and understand the references and information contained in the template, as well as how to use it to your advantage and thus save time and effort while loading a Master Data Package into the MES.

Helper Sheets#

The first worksheets you see in the Excel template are helper sheets that give you information and are also used as configuration for the entire template. To understand what information they hold and what they are used for, see Help Sheets.

Explaining the Format#

The different names and types of the sheets contained in the Excel template are described below:

Prefix Type
No prefix Sub-sheet of previous prefixed sheet
<DM> Dynamic Model
<SM> Static Model
<GT> Generic Table
<ST> Smart Table
<UP> Update Model
<LOOKUP> Reference for the longer name. ℹ

Note

An example for <UP> could be the following:

Screenshot showing a UI with fields labeled "Name", "Team", and "ShiftPlan Workgroup" in an update model interface.

This tab is used to assign employees to a Shift Plan and then to a Workgroup of the Shift Plan.

Info

See Help Sheets for more information on the <LOOKUP>.

The system reads the Excel file in the order that the worksheets are placed in the file, starting from the left-most sheet and moving rightward. Each sheet is read from left to right and from top to bottom, creating fields as a linear FIFO (First-In-First-Out) sequence.

Screenshot showing a formatting example for master data.

Warning

The system will stop reading if a blank line appears. You can look at each sheet as a specific entity type, with each column representing an entity property or relation and each row representing a new entity.

Screenshot showing a data formatting table with columns and rows, labeled "masterdata 53".

The name of the Excel sheets must match the MES entities being loaded and the name of the columns must match the entity properties exactly. When processing the data, the system will check if the object already exists, thus creating or updating it accordingly.

Note

The template does not have all the objects and fields that are available in the UI. You can, however, add more entity types (sheets) and entity properties (columns), provided they are available in the UI and exactly match the name of the entity and respective properties.

Versioned Entities#

In the case of versioned entities, a new version is always created when loaded into the system. Change Sets are managed automatically by the tool so there is no need to create them manually either in the Excel template or in the GUI. However, the approval process for any versioned object can only be performed using the GUI and you can access any Change Sets currently under approval in the Change Sets.

For more information on loading entity revisions and versions, see Revision Configuration.

Modeling Sequence#

It is highly recommended to follow an appropriate modeling sequence since there are a lot of interdependent fields that have strict rules of precedence.

Flow/Step example#

A Flow is composed of Flow Items of type Step and Flow. When a Flow contains Flow Items of type Flow, they are denoted as Child Flows of the Parent Flow for clarity. To create a Flow structure, you should follow the sequence below:

  1. Create the Steps.

    Screenshot showing two-step process with "Pravess" and "Process No" labels.

  2. Create the Flows.

    Screenshot showing a table with three rows labeled "2 Flows", "2 Flows Sequential", and "4 Flow Sequential" with various checkboxes.

  3. Utilize the FlowItems sheet to create the desired Flow structure.

    Screenshot showing a flow diagram with three sections labeled LS, LN, and NT.

Resource/Service example#

In another quick example, the Service must be created before the Resource creation and only after it is possible to fulfill the ResourceService sheet.

Screenshot showing a table with resource and service data, including entity names and priority settings.

State Model/Protocol example#

In the case of Protocols, you must create the State Model and load both the State Model and the Protocol at the same time.

Screenshot showing a State Model/Protocol example with unclear labels.

Screenshot showing a State Model/Protocol example with various fields and options, including severity, default owner, and override settings.

Smart Table example#

A custom Smart Table is often created through the CM MES interface, but it is also possible to create it through the Master Data Package. To create a Smart Table, you should follow the steps below:

  1. Create the custom Smart Table using the Excel template <ST>SmartTable sheet:

    Screenshot showing a table with data in an Excel spreadsheet, illustrating the step to create a custom Smart Table.

  2. Populate the required Properties for the custom Smart Table using the Excel template SmartTableProperties sheet:

    Screenshot showing a table with data populated from an Excel template.

  3. Define the required Precedence Keys for the custom Smart Table using the Excel template SmartTablePrecedenceKeys sheet:

    Screenshot showing a table with data and settings in the background, related to Smart Table configuration.

  4. If data is to be uploaded directly into the Smart Table via Master Data, create a new Excel sheet named <ST>ST Name. The column names in this sheet must correspond to the properties defined in Step 2:

    Screenshot showing an example of a Smart Table with its associated data upload process.

Moreover, because the fields to fill in the Master Data and in CM MES are the same, for more information on how to create a custom Smart Table, see Smart Tables in the Critical Manufacturing Documentation Portal.

In relation to the creation of custom Generic Tables using Master Data, you should follow steps 1, 2, and 4 above as the creation process is similar.

For Generic Tables, you can find more information in Generic Tables, and your Excel template sheets would be as in the following example:

Screenshot showing a table with data entry fields.

Relations#

Most of the relations between objects are added automatically when two objects require contextual sharing of information due to their current operation state. You can manually add relations between objects by using the EntityType sheet and filling in the SourceEntity and the specific TargetEntity.

Screenshot showing a diagram of relational data structure.

Note

If you want to create a relation between versioned entities, use the EntityTypeProperty sheet to define whether the Source or Target entities belong to the version, to the definition or to the complete entity. The Reference Type field in that sheet defines that relationship and has three possible options:

  • EntityDefinition
  • EntityVersion
  • Entity

Attributes#

If you want to add specific attributes to an entity, you must create the attribute in the EntityTypeProperty sheet and then add the columns with the attribute names in the target Entity.

Screenshot showing a spreadsheet with data, including columns and rows.

Screenshot showing a table or spreadsheet with data related to entity attributes.

Warning

The empty spaces in the entity names are taken into consideration and can cause failures when loading the Master Data Package. Make sure you trim all leading and lagging spaces from the labels of any data element.

External Files#

You can upload specific types of external files that will be included in the Master Data Package. They must be specified manually and included in a .zip file that will be used to upload the data into the system:

  • Images - used in Checklists, Data Collections and Notes in Future Actions, among others.
  • Objects - system objects like Queries, Printable Documents or FabLives, among others.
  • Documents - files can be associated to most entity types, like PDFs or other files that can be displayed in the system.

Screenshot showing a list of external file associations.

To reference the files in a Checklist, you must delimit the file name and the specific format with [[ and ]], like the example below:

Screenshot showing a file association dialog with options for entity types and file formats.

For Document loading the user must fill the appropriate values for RelativeFileLocation and Filename, the latter with the name of the object that will be included in the .zip file.

Screenshot showing a table with columns labeled "BM tame Revision", "Descristion", "Type", and others, illustrating file association options.

The content of a .zip file that reflects the model shown above would look like this:

Screenshot showing a file management interface with a highlighted filename "masterdata 12".