Skip to content

Example Payload

The following payload example can be used in the apitransform endpoint and the middlewarebase Logic App:

Note: //Comments are not valid json and should not be included in actual payloads.

{
    "entrypointMap": {

        "operationType": "upsert", // or "delete"

        "fieldMaps": [
            {
                "SourceField": "BKG_REFERENCE_CODE",
                "DestinationField": "booking_custom_Reference_code"
            },
            {
                "SourceField": "BKG_REFERENCE_CODE",
                "DestinationField": "booking_guid",
                "LookupLinkField": "booking_custom_Reference_code"
            },
            {
                "SourceField": "BKG_STATUS",
                "DestinationField": "booking_bookingtype_guid",
                "DefaultValue": "Planned",
                "LookupLinkField": "bookingtype_description"
            },
            {
                "SourceField": "BKG_START_DATE",
                "DestinationField": "booking_start"
            },
            {
                "SourceField": "BKG_END_DATE",
                "DestinationField": "booking_end"
            },
            {
                "SOURCEFIELD": "BKG_RESOURCE",
                "DESTINATIONFIELD": "booking_resource_guid",
                "LookupLinkField": "resource_description"
            },
            {
                "SourceField": "BKG_JOB_REFERENCE_CODE",
                "DestinationField": "booking_job_guid",
                "DefaultValue": "LRC",
                "LookupLinkField": "job_code"
            },
            {
                "SourceField": "BKG_LETTER",
                "DestinationField": "booking_custom_Letter_Lookup",
                "DefaultValue": "f"
            },
            {
                "SourceField": "BKG_LETTERS_MV",
                "DestinationField": "booking_custom_Letters_MV"
            },
            {
                "SourceField": "BKG_LETTERS_HISTORY",
                "DestinationField": "booking_custom_Letters_History"
            }
        ]
    },
    "sourceJson": [
        {
            "BKG_REFERENCE_CODE": "001",
            "BKG_START_DATE": "2021-11-10T00:00:00",
            "BKG_END_DATE": "2021-11-12T00:00:00",
            "BKG_RESOURCE": "Joe Bloggs",
            "BKG_LETTER": "a",
            "BKG_LETTERS_MV": ["a", "b"],
            "BKG_LETTERS_HISTORY": [
                { "value": "a", "start": "2021-11-11", "end": "2021-11-12" },
                { "value": "c", "start": "2021-11-12" }
            ]
        },
        {
            "BKG_REFERENCE_CODE": "013",
            "BKG_START_DATE": "2021-11-14T00:00:00",
            "BKG_END_DATE": "2021-11-15T00:00:00",
            "BKG_RESOURCE": "Mary Black",
            "BKG_LETTER": "m",
            "BKG_LETTERS_MV": ["a", "b"],
            "BKG_LETTERS_HISTORY": [
                { "value": "a", "start": "2021-11-11", "end": "2021-11-12" },
                { "value": "c", "start": "2021-11-12" }
            ]
        },
        ...
    ]
}

In addition to the above, the following three headers are needed:

  • tenant
  • environment
  • RetainAuthorization

Example:

environment="dev" tenant="client1" RetainAuthorization="Bearer eyJhbG..."

Understand field mappings

The following section explains the properties and mappings used in the above example.

EntrypointMap

Contains operationType and fieldMaps JSON properties.

OperationType

Accepted values are upsert and delete. Default is upsert.

SourceJson

Holds the input data from a variety of sources like SQL databases, CSV files, JSON payloads and so on. The data is in JSON array of objects format.

FieldMaps

Bridge between the input fields and the Retain Cloud fields. The value is a JSON array of field map objects. Objects are composed based on the type of field mapping.

There are several types of mapping - flat, system lookup, custom lookup, history fields, multi-value fields.

Flat mapping

Simple field-to-field mappings. The source field is mapped to the corresponding destination field in Retain Cloud. No transformation is made and the value is the same in the source and destination.

Example:

{
   "SourceField": "BKG_START_DATE",
   "DestinationField": "booking_start"
}

System lookup

Lookup mappings generally transform the source value (name, code etc) to a Retain Cloud ID. This corresponds to a foreign key in the database.

For example, if you want to import a booking record into Retain Cloud, provide a booking_job_guid. The booking_job_guid provided in the source will have to be transformed to a Job ID. This is done by executing a lookup query by correctly mapping the fields as shown below:

{
   "SourceField": "BKG_JOB_REFERENCE_CODE",
   "DestinationField": "booking_job_guid",
   "LookupLinkField": "job_code"
}

The LookupLinkField points to the column which holds the job code values. This column is queried for the job code provided in the source and returns the Job ID for that record as transformed data.

The output of that lookup transformation will be:

{
   ...
   "booking_job_guid": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
   ...
}

System lookups are executed against database tables in Retain Cloud. If a defined list of values has to be looked up in the Retain Cloud, then use a custom lookup.

Custom lookup

Custom lookups differ from System lookup in the lookup object. System lookup performs a lookup on a database table and custom lookup on a list of defined values in Retain Cloud . The Custom lookups can only map a source field to a custom destination field since only custom fields in the application can hold values from the value lists.

Example:

{
   "SourceField": "BKG_LETTER",
   "DestinationField": "booking_custom_Letter_Lookup"
}

The destination field is a custom field of type Lookup and holds a custom value list defined in the application. The value provided in the source should be one of these predefined list values.

History fields

History field mapping is done over custom history fields or the resource charge rate or the diary history field. The source needs to be provided in a specific format. There is a specific field mapping for a specific source format. History fields are composed of:

  • a value (mandatory)
  • start of the period (mandatory)
  • end of the period

Examples of source formats with specific field mappings:

Source example one:

Source is provided as an array of JSON objects with fixed property names (value, start, end).

...,
{
    "BKG_LETTERS_HISTORY": [
        { "value": "a", "start": "2021-11-11", "end": "2021-11-12" },
        { "value": "c", "start": "2021-11-12" }
    ],
},
...

Specific example mapping for that source:

{
    "SourceField": "BKG_LETTERS_HISTORY",
    "DestinationField": "booking_custom_Letters_History"
}

Source example two:

Source is provided as an array of JSON objects with named properties. The mapping should respect the source properties.

Example source:

{
    ...,
    "RES_CHARGERATE": [
        {
            "RES_CHARGERATEHISTORY_CHARGERATE": "Senior Consultant",
            "RES_CHARGERATEHISTORY_START": "2021-01-01T00:00:00"
        },
        {
            "RES_CHARGERATEHISTORY_CHARGERATE": "Auditor",
            "RES_CHARGERATEHISTORY_START": "2021-03-15T00:00:00"
        }
    ],
    ...
}

Since the source history properties are not fixed, their names should be provided in the field mapping.

The specific example mapping for such a source is:

{
    "SourceField": "RES_CHARGERATE",
    "DestinationField": "Charge rate",
    "HistoryFieldMap": {
        "SourceValueField": "RES_CHARGERATEHISTORY_CHARGERATE",
        "SourceStartField": "RES_CHARGERATEHISTORY_START"
    }
}

The source is provided as an escaped stringified JSON. This case is specially developed for data fetched from an SQL query. The fixed and non-fixed history fields are supported.

{
   ...,
   "BKG_HISTORY": "[{\"value\":\"119\",\"Start\":\"2021-01-01T00:00:00\"}, \"value\":\"120\",\"Start\":\"2021-03-15T00:00:00\",\"end\":\"2021-03-15T00:00:00\"}]"
   ...
}
{
   ...,
   "BKG_HISTORY": "[{\"BKG_HIS_VAL\":\"119\",\"BKG_HIS_START\":\"2021-01-01T00:00:00\"},{\"BKG_HIS_VAL\":\"120\",\"BKG_HIS_START\":\"2021-03-15T00:00:00\",\"BKG_HIS_END\":\"2021-03-15T00:00:00\"}]",
   ...
}

Field mappings are according to the type of the source and are the same as shown in the above cases.

An example of the SQL query that can be used to fetch data in the above source format:

select RES_INTEGRATION.*, (
    select * from RES_CHARGERATEHISTORY
    where RES_CHARGERATEHISTORY_RES_CODE = RES_EMPLOYEE_CODE
    for json path) as RES_CHARGERATE
from RES_INTEGRATION

Multivalue

The multi-value field mappings are provided as an array of strings in the source.

An example of the source:

{
    ...,
    "BKG_LETTERS_MV": ["X", "Y", "Z"],
    ...
}

The field mapping is as follows:

{
    "SourceField": "BKG_LETTERS_MV",
    "DestinationField": "booking_custom_Letters_MV"
}

The default value:

{
    "SourceField": "BKG_END_DATE",
    "DestinationField": "booking_end",
    "DefaultValue": "2021-11-10T00:00:00Z",
},
{
    "SourceField": "BKG_JOB_REFERENCE_CODE",
    "DestinationField": "booking_job_guid",
    "DefaultValue": "My new job",
    "LookupLinkField": "job_code"
},
{
    "SourceField": "BKG_LETTER",
    "DestinationField": "booking_custom_Letter_Lookup",
    "DefaultValue": "Y"
},

The default value is used when the specific field cannot be found in the source record. In this case, the transformed record will take the default value. If the mapping is a lookup, a lookup query is executed against the provided default value.