SDK Bridge

The PayRun.io Bridge enables importing an external data source into the API.

The Bridge extends the PayRun.io SDK to provide a mapping and transformation language (MaTL) that enables any data source to be imported the into PayRun.io API. It also supports the automation of the import process and provides full clear feedback on any issues or conflicts with the data.

Using bespoke MaTL files, you can bring data from any legacy payroll system into the API. This also supports data entry through technologies such as Microsoft Excel or even simple CSV (comma separated variables) files.

Process Overview

The following diagram demonstrates the Bridging process when importing data from a legacy payroll system.

BackOfficeProcessingAgentPayRun.ioAPIBridgingRunnerPayRun.ioAPILegacyPayrollPlatformPayrollExportPre-definedMaTLfilePayRun.ioBridgeImport1.2.3.

Steps

  1. The payroll data is exported from the legacy payroll system
  2. The bridging transformation process is executed
  3. The translated data is imported into the PayRay.io API

Mapping and Transformation Language (MaTL)

In order to convert the payroll data source into a PayRun.io import, you will need to define the mapping and translation definition. This is declared using the MaTL specification as described below.

MaTL overview

MaTL is written using XML notation and follows a hierarical structure that matches the hierarcy of the PayRun.io data objects.

The source data is loaded into a data set consisting of tables, rows and columns. Each row represents a single PayRun.io entity and the MaTL describes how the tables and columns are mapped to the corresponding PayRun.io data objects.

MaTL also provides some advanced features that allow the source column data to be translated into the equivalent object property values.

Translation Map / Child Map Element

The translation map element of the MaTL represents the mapping of a data source table to an entity type. It describes the which table maps to which entity, the key value of the entity and some other optional conditions.

Translation Map Properties

Property Description
SourceTable The name of the data source table to be mapped.
DestinationTypeName The PayRun.io entity type to be targeted.
SourceKeyColumn Optional. The column that holds the entity key value.
Condition Optional. SQL style condition. Used to determine if a row should be mapped.
ParentKeyColumn Optional. Used to filter child elements in nested mappings.
DistinctColumns Optional. Csv list of columns to consider distinct values from only.
Mappings Array of Property Mapping elements used map column values to properties.
ChildMaps Array of Translation Maps elements to be repeated for each data row.

Property Mapping Element

The property mapping element is used to map a column value into a target entity property. This also allows for translations, default values and conditional selections.

Property Mapping Properties

Property Description
DestProperty The name of the destination property on the target entity.
SourceColumn The column name for the mapping.
Condition Optional. SQL style condition. Used to determine if column should be mapped.
DefaultValue Optional. Used to supply a default value if the column is null or empty.
TranslatorMethod Optional. A translator method expression used to translate source values.

Simple Employer Import Example

This super simple example demonstrates the mapping for a single employer record. The source table contains the employer company name and and address.

Example Employer Data File

Table Name: Employer_Info

ID Name Address 1 Address 2 Address 3 PostCode
ER001 Test Ltd 1 The Street Test Town Testershire TE1 1ST

Example Mapping

<?xml version="1.0"?>
<TranslationMap  
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
  SourceTable="Employer_Info"  
  SourceKeyColumn="ID"  
  DestinationTypeName="Employer">
  <Mappings>
    <PropMap DestProperty="Name" SourceColumn="Name" />
    <PropMap DestProperty="Address.Address1" SourceColumn="Address 1" />
    <PropMap DestProperty="Address.Address2" SourceColumn="Address 2" />
    <PropMap DestProperty="Address.Address3" SourceColumn="Address 3" />
    <PropMap DestProperty="Address.Postcode" SourceColumn="PostCode" />
  </Mappings>
</TranslationMap>

This mapping will match the source data set table entitled "Employer_Info". It will use the value of the "ID" column as the unique key of the employer. And source data columns: Name, Address 1, Address 2, Address 3 and PostCode are mapped to the corresponding properties on the PayRun.io Employer object.

Source Key Column

The value of the source key column is very important. It represents the unique key (used within PayRun.io) for the target entity. This identity value is also used to select matching entities within nested child mappings.

If the mapping excludes the "SourceKeyColumn", generated entities will be created using a HTTP POST command. This results in the entities receiving an auto generated unique key within the PayRun.io API.

Conditions

It is often necessary to selectively filter row and column mappings. The Condition mapping properties allow you to define filtering conditions using a SQL style expression.

Conditions can be applied at both the row (Translation Map) and column (Property Mapping) levels. Row level filters allow for the conditional exclusion of entire row maps. Column level filters allow individual properties to mapped.

Example Row Condition

Test Employee Data A

Table Name: Employee_Info

ID First Name Last Name Is Active
EE001 John Smith True
EE002 Jane Smith True
EE003 Joe Bloggs False
EE004 Julia Bloggs False

Condition Example A

The example TranslationMap condition only selects employee rows that are marked as "Is Active": [Is Active] = 'True'

The following translation map will only match active employee rows.

<?xml version="1.0"?>
<TranslationMap  
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
  SourceTable="Employee_Info"  
  SourceKeyColumn="ID"  
  DestinationTypeName="Employee"
  Condition="[Is Active] = 'True'">
  <Mappings>
    <PropMap DestProperty="FirstName" SourceColumn="First Name" />
    <PropMap DestProperty="LastName" SourceColumn="Last Name" />
  </Mappings>
</TranslationMap>

Example Column Condition

Test Employee Data B

Table Name: Employee_Info

ID First Name Last Name Gender
EE001 John Smith Male
EE002 Jane Smith Female
EE003 Joe Bloggs Male
EE004 Julia Bloggs Female

Condition Example B

The example PropMap conditions set a property value based on gender:

  • [Gender] = 'Male'
  • [Gender] = 'Female'

The following conditional property maps will only be applied to the PayRun.io employee when the gender is matched.
This means that when the source data row gender is Male, the employee title will be set to "Mr" and "Ms" if Female.

If the Property Mapping condition is not matched, it is skipped. This allows you to specify multiple conditional maps.

<?xml version="1.0"?>
<TranslationMap  
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
  SourceTable="Employee_Info"  
  SourceKeyColumn="ID"  
  DestinationTypeName="Employee">
  <Mappings>
    <PropMap DestProperty="FirstName" SourceColumn="First Name" />
    <PropMap DestProperty="LastName" SourceColumn="Last Name" />
    <PropMap DestProperty="Title" SourceColumn="-" Condition="[Gender] = 'Male'" DefaultValue="Mr" />
    <PropMap DestProperty="Title" SourceColumn="-" Condition="[Gender] = 'Female'" DefaultValue="Ms" />
  </Mappings>
</TranslationMap>

Default Values

The above example makes uses of the default value attribute. Default values are applied when the following conditions are matched:

  • The specified column does not exist
  • The column value is null
  • The column value is empty

Using a non-existent column name (for example "-") forces the Bridge to use the default value.
If no default value is set, an invalid column name will result in an error.

Nested Translation Maps

Data entities (within the PayRun.io API) have a hierarchical structure. Employers have a sub collection of employees, employees have collections of pay instructions, etc.

The MaTL is designed to provide the ability to import hierarchical data structures. This is accomplished using nested mappings and Parent Key Columns.

Employer and Pay Schedule Example

The following example demonstrates importing an employer with two pay schedules. The source data (for the employer and schedules) exists in two data tables: EmployerInfo and ScheduleInfo.
Using the example data results in the creation of a single employer having 2 linked pay schedules.

Table Name: EmployerInfo

ID Name
ER001 Test Ltd

Table Name: ScheduleInfo

EmployerID Name Frequency
ER001 Staff Weekly Weekly
ER001 Directors Monthly
<?xml version="1.0"?>
<TranslationMap  
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  SourceTable="EmployerInfo"  
  SourceKeyColumn="ID"  
  DestinationTypeName="Employer">
  <Mappings>
    <PropMap DestProperty="Name" SourceColumn="Name" />
  </Mappings>
  <ChildMaps>
    <ChildMap  
      SourceTable="ScheduleInfo"  
      ParentKeyColumn="EmployerID"  
      DestinationTypeName="PaySchedule">
      <Mappings>
        <PropMap DestProperty="Name" SourceColumn="Name" />
        <PropMap DestProperty="PayFrequency" SourceColumn="Frequency" />
      </Mappings>
    </ChildMap>
  </ChildMaps>
</TranslationMap>

Parent Key Column

When working with nested data, the ParentKeyColumn property (of the Child Map object) is used to filter child rows.
Only child rows having a column value matching the parent map source key column value will be selected.

When no ParentKeyColumn value is specified, then every row in in the child map will be matched.

Translators

Payroll is a complex domain and there are always differences in how payroll products are modelled. This means that the source data values often need to be translated into PayRunIO compatible data.

The PayRunIO Bridge Mapping and Translator Language includes the ability to specify translations. The translation expressions determine how source values should be changed to fit the target property values.

Translation expressions are specified on the property map object and can be chained using the translator separate ~~ (double tilde) characters. Chaining translators allows multiple translations to be performed against a single column value.

How Translator Expressions Work

There are many different types of translator methods, but they all follow a similar patter. The translation expression is represented by a string of characters. The first left hand side of the string identifies the type of translation to be performed. The identifier is followed by a single colon character (:). Additional arguments are suffixed on the right.

Appender Translator

This translator appends the source column value onto an existing property value. It is also possible to supply an optional separation string to be used between the appended values.

Translator Prefix: Appender:

Appender Translator Demo Example

The following example data tables and translator map result in each employee having their working week pattern set to: Monday,Tuesday,Wednesday,Thursday,Friday

Note this example uses a comma separator in the appended data. This pattern is required in order to translate a concatenated string of day names into the PayRunIO working week property

Appender Translator Demo Example Data

Table Name: Employees

ID First Name Surname
EE001 John Smith
EE002 Jane Smith
EE003 Joe Bloggs
EE004 Julia Bloggs

Table Name: StdWeek

DayOfWeek
Monday
Tuesday
Wednesday
Thursday
Friday

Appender Translator Example Map

<?xml version="1.0"?>
<TranslationMap 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  SourceTable="Employees"
  SourceKeyColumn="ID"
  DestinationTypeName="Employee">
  <Mappings>
    <PropMap DestProperty="FirstName" SourceColumn="First Name" />
    <PropMap DestProperty="LastName" SourceColumn="Surname" />
  </Mappings>
  <ChildMaps>
    <ChildMap SourceTable="StdWeek" SourceKeyColumn="{TargetParent}" DestinationTypeName="Employee">
      <Mappings>
        <PropMap  
          DestProperty="WorkingWeek"  
          SourceColumn="DayOfWeek"  
          TranslatorMethod="Appender:," />
      </Mappings>
    </ChildMap>
  </ChildMaps>
</TranslationMap>

Source Key Column {TargetParent}

Close inspection of the above (appender translator map example) reveals a special value in the SourceKeyColumn attribute: {TargetParent}.

Setting this special value forces the translation service to apply the child map values against the parent row. So in this example, each employee has the standard working week pattern applied.

{TargetParent}
Use the special {TargetParent} value to apply sub tables to the parent element. This allows the translator to use multiple source data tables against a single target PayRunIO entity. Don't forget the curly braces!

Default Appender

The default appender is used to insert the default value of a property into the target PayRunIO entity. The purpose of this is to ensure that required properties recieve the default value even when there is no source column in the data table.

Translator Prefix: Default:

Default Translator Demo Example

The following example data table contains employee names and start date, but Jane Smith's start date it omitted. Employees must have a non-null start date, so the default appender is used to ensure that the default date value is inserted.

Default Translator Demo Example Data

Table Name: Employees

ID First Name Surname StartDate
EE001 John Smith 2019-04-06
EE002 Jane Smith

Default Translator Example Map

<?xml version="1.0"?>
<TranslationMap 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  SourceTable="Employees"
  SourceKeyColumn="ID"
  DestinationTypeName="Employee">
  <Mappings>
    <PropMap DestProperty="FirstName" SourceColumn="First Name" />
    <PropMap DestProperty="LastName" SourceColumn="Surname" />
    <PropMap  
      DestProperty="StartDate"  
      SourceColumn="StartDate"  
      TranslatorMethod="Default:" />
  </Mappings>
</TranslationMap>

Expression Translator

The expression translator provides a powerful SQL style language to calculate output values. Expressions are specified using SQL styled language that includes several functions for manipulating output values.

Translator Prefix: Expression:

Expression Functions

The following functions are supported.

CONVERT

  • Description Converts particular expression to a specified .NET Framework Type.
  • Syntax Convert(expression, type)
  • Arguments:
    • expression -- The expression to convert.
    • type -- The .NET Framework type to which the value will be converted.

Example: TranslatorMethod="Expression:CONVERT(total, 'System.Int32')"

LEN

  • Description Gets the length of a string.
  • Syntax LEN(expression)
  • Arguments:
    • expression -- The string to be evaluated.

Example: TranslatorMethod="Expression:LEN(ItemName)"

ISNULL

  • Description Checks an expression and either returns the checked expression or a replacement value.
  • Syntax ISNULL(expression, replacementvalue)
  • Arguments:
    • expression -- The expression to check.
    • replacementvalue -- If expression is null, replacementvalue is returned.

Example: TranslatorMethod="Expression:ISNULL(price, -1)"

IIF

  • Description Gets one of two values depending on the result of a logical expression.
  • Syntax IIF(expression, truepart, falsepart)
  • Arguments:
    • expression -- The expression to evaluate.
    • truepart -- The value to return if the expression is true.
    • falsepart -- The value to return if the expression is false.

Example: TranslatorMethod="Expression:IIF(total > 1000, 'expensive', 'cheap')"

TRIM

  • Description Removes all leading and trailing blank characters like \r, \n, \t, ' '
  • Syntax TRIM(expression)
  • Arguments:
    • expression -- The expression to trim.

Example: TranslatorMethod="Expression:TRIM(FirstName)"

SUBSTRING

  • Description Gets a sub-string of a specified length, starting at a specified point in the string.
  • Syntax SUBSTRING(expression, start, length)
  • Arguments:
    • expression -- The source string for the substring.
    • start -- Integer that specifies where the substring starts.
    • length -- Integer that specifies the length of the substring.

Example: TranslatorMethod="Expression:SUBSTRING(phone, 7, 8)"

HREF Builder

The hypertext reference translator allows for the insertion of entity links on the PayRunIO objects. Some entities have linkage properties that describe a relationship between objects.
For example; the employee entity requires a linkage property to determine the employees selected pay schedule.

The HREF Builder translator enables the value of a source data table column to be used in a linkage property.

Translator Prefix: HrefBuilder:

In order to create the correct type of link, you will need specify the type of entity being linked to. Use the following suffixes to select the desire link type.

Translator Suffix: Employer
Example TranslatorMethod="HrefBuilder:Employer"
Translator Suffix: PaySchedule
Example TranslatorMethod="HrefBuilder:PaySchedule"
Translator Suffix: NominalCode
Example TranslatorMethod="HrefBuilder:NominalCode"
Translator Suffix: Pension
Example TranslatorMethod="HrefBuilder:Pension"
Translator Suffix: HolidayScheme
Example TranslatorMethod="HrefBuilder:HolidayScheme"

Employee

Translator Suffix: Employee
Example TranslatorMethod="HrefBuilder:Employee"

Href Builder Translator Demo Example

The following example data table contains the source data for employees including the pay schedule unique key.

Href Builder Translator Demo Example Data

Table Name: Employees

ID First Name Surname ScheduleName
EE001 John Smith Weekly
EE002 Jane Smith Monthly

Href Builder Translator Example Map

<?xml version="1.0"?>
<TranslationMap 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  SourceTable="Employees"
  SourceKeyColumn="ID"
  DestinationTypeName="Employee">
  <Mappings>
    <PropMap DestProperty="FirstName" SourceColumn="First Name" />
    <PropMap DestProperty="LastName" SourceColumn="Surname" />
    <PropMap  
      DestProperty="PaySchedule.Href"  
      SourceColumn="ScheduleName"  
      TranslatorMethod="HrefBuilder:PaySchedule" />
  </Mappings>
</TranslationMap>

Lower Case

The lower case translator is used to ensure that string values are set to lower case. This translator does not support additional suffixes.

Translator Prefix: LowerCase:

Replace

The replace translator supports string replacement within the source data value. This translator requires additional suffix values to specify the value to be replaced and the replacement value.

Translator Prefix: Replace:

Replace Translator Example

The following property map example demonstrates replacing one word with another. The word source will be replaced with the word replacement.

<?xml version="1.0"?>
<TranslationMap 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  SourceTable="Employees"
  SourceKeyColumn="ID"
  DestinationTypeName="Employee">
  <Mappings>
    <PropMap  
      DestProperty="FirstName"  
      SourceColumn="First Name"  
      TranslatorMethod="Replace:('source', 'replacement')" />
  </Mappings>
</TranslationMap>

Chained Replacements
The replace translator supports the specification of multiple replacements. Each replacement is described using a new bracket wrapped expression.
Example: Replace:(' ','')('A','a')('B','b')

Round Down

The round down translator allows you to round numerical fractions to a set number of decimal places. This translator will only works on numerical values. If the source value is non-numerical, no translation is applied.

Translator Prefix: RoundDown:

Setting Round Down Decimal Precision

The default decimal precision is 2 places. However, you can choose alternative precision by setting a numerical suffix to the translation expression.

Example: TranslatorMethod="RoundDown:4"

Round Up

The round up translator allows you to round numerical fractions to a set number of decimal places. This translator will only works on numerical values. If the source value is non-numerical, no translation is applied.

Translator Prefix: RoundUp:

Setting Round Up Decimal Precision

The default decimal precision is 2 places. However, you can choose alternative precision by setting a numerical suffix to the translation expression.

Example: TranslatorMethod="RoundUp:4"

Truncate

The truncate translator allows you to truncate numerical fractions to a set number of decimal places. This translator will only works on numerical values. If the source value is non-numerical, no translation is applied.

Translator Prefix: Truncate:

Setting Truncate Decimal Precision

The default decimal precision is 2 places. However, you can choose alternative precision by setting a numerical suffix to the translation expression.

Example: TranslatorMethod="Truncate:4"

Unique Key

PayRunIO is a RESTful api meaning that entities are inserted, edited, retreived and deleted using a resource locator (like a web page address).
Each resource is stored in a hierarchically constructed resource locator having the parent element followed by a unique resource identifier value.
We reffer to the unique identifier as a Unique Key.

Unique keys are formed of alphanumeric characters and hyphens. The common convention is to only use upper case letters. The Unique Key translator can be used to ensure that resource identifier values are consistently formatted.

This convention is also used when specifying pay component codes.

Translator Prefix: UniqueKey:

Unique Key Translator Example

<?xml version="1.0"?>
<TranslationMap 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  SourceTable="PayCodes"
  SourceKeyColumn="ID"
  DestinationTypeName="PayCode">
  <Mappings>
    <PropMap  
      DestProperty="Code"  
      SourceColumn="Pay Code Name"  
      TranslatorMethod="UniqueKey:" />
  </Mappings>
</TranslationMap>

Upper Case

The upper case translator is used to ensure that string values are set to upper case. This translator does not support additional suffixes.

Translator Prefix: UpperCase:

Value Map

The value map translator enables the translation of multiple values into alternative results. This is useful when needed to translate source values into PayRunIO enumerations or true/false values specified in alternative ways.

Translator Prefix: ValueMap:

Specifying a Value Map

The mapped values are specified using a key value pair comma separated list. Source values not found in the mapping specification are immited as is without translation.

Example: TranslatorMethod="ValueMap:y=True,f=False"