Export Template

Export Templates are a part of our data integration technologies. It is frequently the case that you want to import data into your system from a variety of 3rd party sources. LemonEdge automatically provides a standard format for importing from csv and xls files. If your legacy 3rd party provider is unable to provide data in the required format you can always use our Data Mappings to translate the custom file to an import the system understands.

Export Template Features

Templates are provided by the system for every single entity in the system, including custom entities and any entities you've created through our API. They enable you to quickly import data into the system and can also be used when exporting data from the system. By ensuring data is exported in the same format you guarantee the data can easily be modified and the changes uploaded back into the system with ease.

Template IDs

Columns to import will often refer to unique IDs. The templates we produce have a method of ensuring you can link to related data without having to know the system id of the entity you need to link to. For instance if an entity has a field CurrencyID in it the template will have the following import fields:

  • CurrencyID
    This is the column that holds the unique ID of the currency you are referring to. This is an optional column in a template. If you don't have the ID (which you won't unless you're exporting data from the system itself into a template to import again) you can remove this column from the template.
  • CurrencyID_ThreeLetterCode
    For each column that is part of the Key field of that entity the system generates an _KeyField column for importing. In this instance the column ThreeLetterCode is the key field for currencies, so the system automatically generates a column called CurrencyID_ThreeLetterCode. The system uses these _KeyField columns (there could be more than 1 if the entity you're referring to has more than 1 key field) to retrieve the entity from the system instead of using the unique internal ID. If you have the ID though and are not using the key fields you can remove these columns from the template. In other words if you want to reference a relationship in a template then either the ...ID field or ...ID_KeyFields must exist as columns in a template. If both exist the system checks the ...ID field, if it is blank it then uses the ...ID_KeyFields.

Using this mechanism templates allow you to refer to any other entity in the system using relationship IDs (if you have them) or the key properties of the entity. This applies to all ID fields, including the standard ID field for the entity itself. So when referring to the entity being imported the ID column can be used, or can be left blank (if unknown or the column entirely removed from the template) and the key fields used instead.

This format is used recursively, so if a key field of an entity happens to be a relationship field itself then the "underscore keyfields" format will continue on to the next related entity in the chain of key fields. For instance if you had a entity called Security with a key field of Name and OwningEntityID, and the data you were importing had a column referring to security you would end up with columns like this:

  • SecurityID
  • SecurityID_Name
  • SecurityID_OwningEntityID
  • SecurityID_OwningEntityID_Name

Again if you don't want the ID fields you can just keep SecurityID_Name and SecurityID_OwningEntityID_Name as the two fields needed to uniquely identify a security id. This process continues to create new columns in the template for a key column until all keys can be identified using just data instead of ids.

Important

If the entity you're referring to is a base entity type for other entities that inherit from it then you will also have an EntityID_Type (and associated unique ID) column. This is used to specify the type of the inheriting object to use when linking to this data. For instance ObjectEntities are a base entity type that many different types of entities inherit from, such as companies and people. If we had a record that had a relationship to an ObjectEntityID, instead of just a CompanyID, then we have an extra import field allowing us to specify the inheriting type that is being linked to:

  1. ObjectEntityID
  2. ObjectEntityID_ObjectEntityIDType
    This would hold the entity type the key field refers to, such as Companies if the ObjectEntityID linked to a Company. This isn't required as a column and is for information when exporting data.
  3. ObjectEntityID_InheritingObjectTypeID
    This holds the unique ID (all entities have a unique type ID) of the entity type. The system would use either this field or the ObjectEntityIDType if this one is missing or empty. Again it is not a required column and is for information when exporting data.
  4. ObjectEntityID_Name

Template Column Types:

Any system generated template always includes the following fields for importing:

  • Import Data Action:
    This column is always included in a template to indicate what you want to do with the row of data. If this column doesn't exist then it assumes that it holds the value 'Import' for every record.
    This provides an advanced mechanism for importing changes only - when creating a template you can assign formulas to the cells in this column to only contain 'Import' if data has changed, and keep 'None' otherwise. With this simple formula you can ensure only changes made by users are imported back in keeping data imports to a minimum.
    The column has the following valid values:
    • None
      Indicates this row should be ignored during the import.
    • Import
      Indicates this row should be run through the import routine
    • Delete
      Indicates this row should be deleted from the system (using the keys) if it exists
  • Import Data Type
    This holds the name of the type of data (the entity collection name which can be a system entity, custom entity or one created through our API) that the system is importing. If this column isn't included in the data set then you need to tell the system what type of data is being imported either via using a Data Mapping or when prompted.
  • ID
    The actual unique internal id for the record.
    See "Template IDs" above for how this field is used or can be removed from the template.
  • Keys
    All fields that are marked as being a key field of the entity appear as a column in the template. If the ID column doesn't exist in a template (or has a blank value) then it must contain these key fields. The system uses these key fields to query the database to see if a unique row already exists containing these values. If it does the system will automatically take the import data to update the record, if it doesn't it will be creating a new record. If the ImportDataAction is set to Delete then it will do neither and will delete the existing record instead.
    See "Template IDs" above for how these fields are used or can be removed from the template.
  • Fields
    Contains all other fields in the entity as columns in the import template.
    If any of these fields are ID fields holding relationships to other entities then the system will hold an ID column as well as ID_KeyFields columns too. See "Template IDs" above for how these fields are used or can be removed from the template.
  • Data Sources
    For each Data Source Type you have setup the system will generate an IMPORT_EXTERNAL_DATA_SOURCE_DataSourceType column where DataSourceType is the name of your unique data source type.
    This enables you to update/utilise 3rd party key references in your import templates as well. If you're using Data Mappings you can also use these as key fields for looking up the record to update itself.
  • Custom Actions
    If the entity being imported supports custom actions (such as custom algorithms, calculations or processes) the template will include this column header with an enumerated list of all possible custom actions you can invoke from this import line.

Template Format

When generated the template can either be in csv or (if using the Full Desktop Client) xls format. The data generated includes the following:

  • A header row that is named with the user friendly property name and holds information about the types of columns. For instance includes '(Key)' in the column header name for columns that are part of key fields.
  • A second header row that holds the actual column name looked for when importing, this does not include any extra info about the column (such as '(Key)'). you can use the first row to understand what data is needed, but it is the second row that the system expects to be the actual template header column names - so delete the first row when you understand what the columns are all for.
  • 2 dummy rows of data each column holds the following dummy information:
    • Any column that holds an enumerated field, the dummy data will hold a complete enumeration of all the possible valid values. For instance with the Import Data Action field, the dummy data will be:
      "Delete, Import, None"
    • IMPORT_DATA_TYPE will hold the actual type of data expected to be imported
    • Any column that holds a date, or date time field, will have dummy data showing how to format the date, or datetime, in an international compatible format.
    • All other fields will just contain blank dummy data

Text Encoding Formats

Data can be imported from xls or csv file formats. A template csv file can be saved as any of the following text formats:

  • ASCII
  • BigEndianUnicode
  • Unicode
  • UTF32
  • UTF7
  • UTF8
Caution

By default the system will assume csv files are being imported using the unicode format - this is to ensure the system deals with all possible culture formatting automatically. If you're using a different text file format you will need to use a Data Mapping to specify what text encoding to use when opening the file.

Removing Columns

You can take this template and remove all the fields you don't require in order to create a streamlined template appropriate for your requirements. Removing columns has the following impacts:

  • Import Data Action:
    You'll loose the ability to individually ignore certain rows from the import, and specify those rows should be deleted from the system. The system will automatically assume all data is to be imported
  • Import Data Type:
    Removing this column means the system can't automatically determine what type of data you want to import. Therefore you will be prompted for the type of data you're using or you will need to use a Data Mapping to automatically inform the system.
  • ID:
    The actual internal unique id of the item to be imported. If you don't have this, or the 3rd party data source doesn't have it, you can remove this column. You will be able to utilise this field when exporting data from the system into a format compatible for modification and immediate re-importing.
  • Keys:
    The actual fields that determine the unique key for this entity type. If you have don't have the ID column present you must have these fields included in the template (so the system can identify the record). If you have the ID column these fields will only be used if the column contains blank data.
  • Other Fields:
    Any of these can be deleted if you don't want them. You should have at least one other field in order to actually be updating/creating data. If any of these fields hold relationships to other entities you'll have ...ID and ...ID_KeyFields columns. Again you can delete the ...ID field and keep only the ...ID_KeyFields or remove them all if you don't want this column in the import at all.
  • Data Sources
    Just like standard fields these can be deleted too if they're not required as part of your import
  • Custom Actions
    Just like standard fields these can be deleted too if they're not required as part of your import

Default Exports

Our standard grid export functionality ensures that when you are exporting data from a grid the system exports it in the export template format for that entity type. This means it has all the right headers and columns in the correct format for importing, including the Import Data Action and Import Data Type columns.

Tip

Optional information to help a user be more successful By simply exporting from any grid in the system you automatically have that data in an export template format enabling you to make changes in that file/excel and with one click import that back into the system. Never has it been easier to work with all of your data, in tools like excel, the way you want to.

Excel Integration

Note

Excel Integration is only possible if you are using the Full Desktop Application which runs on Windows only. When exporting to Excel the application gives you extra options on how to interact with excel such as creating a new instance, exporting into existing excel workbooks, etc.

When exported to excel the system not only has the headers and columns as described in the Template Format section above, but also creates named ranges for the import. In excel the following named ranges are created:

  • HeaderAll
    A named range that just encompasses the first header rows
  • DataAll
    A named range that encompasses all of the data named ranges
  • HeaderDataAll
    A named range that contains all the header and data rows.
  • Data_ColumnName
    For every column there is an associated Data_ColName named range that holds the data only for this column.

By using named ranges we provide a consistent mechanism for you to refer to data in excel reports, and also an advanced method of creating complex templates.

For instance if we have a entity with a Name, ShortCode and Description fields (such as the system standard Industry entity in Financial Services) the system would create the following named ranges:

  • Data_Description
  • Data_ID
  • Data_Import_Data_Action
  • Data_Import_Data Type
  • Data_Name
  • Data_ShortCode
  • DataAll
  • HeaderAll
  • DataHeaderAll
Warning

Note: When importing from excel the system only imports using the named ranges - it doesn't matter what the column headers are named or anything else. See Importing for more information.

The default template provides 2 rows of dummy data, if you need to import more than two rows of data (which you will) you need to increase the named ranges to cover more rows. By default it will only import the data in the named ranges.

Tip

By creating a template with two dummy rows we provide an easy mechanism to expand the named ranges. Simply select the last row of the named range, drag the row down to as many (or thousands more) rows as you require, right click and select Insert. This will insert that number of rows between the last and second last rows and by doing so it will expand all the Data_... named ranges by that number of rows.

By using named ranges we greatly increase the templating capability using excel. You don't have to have certain headers or text/etc, you don't have to have the data to be imported in specific sheets, places, etc. You simply need to ensure the data you want imported is covered by the correct Data_ named ranges. This means with an excel template you can create any kind of user friendly header information, selections, etc. You can create help information in columns on the left and everywhere else all around the data you care about importing. That data can then have columns that aren't needed deleted, or hidden, in excel. The remaining data cells can be formatted, locked, etc to create a far greater template experience for users in excel.

Tip

Combined with our excel reporting integration you can create incredibly advanced export/import templates. For instance if you had a DataSet that exported all Transactions for an Owning Entity into excel you could do so where all the named ranges are different from the named ranges expected for importing.
You can then create a new sheet and export the Export Template format for transactions into it. This would have the named ranges for importing transactions now in your new excel sheet. you can then place formulas referring back to the DataSet named ranges to populate your import named ranges. You could combine this with editing options (for instance a selection of other Owning Entities) and formatting (as it just looks at named ranges), etc. This template then provides an incredibly powerful end user tool - they can now export all transactions against an owning entity with one click, change the owning entity (or any other options you put in the template) and import it back in with one click to copy all the transactions from one owning entity to another. See our Reporting section for more information.

Views

The Export Template functionality doesn't have any associated views. Clicking this menu option will bring up a popup dialog asking which type of entity you want to get the template for. The system will then prompt you if you want the template in csv or xls (if you are running the Full Windows Desktop Client) and download/open it for you.