Template columns have the option of being defined as multi-mappable. More than one uploaded column can be mapped to such a template column.

Use cases

There are two primary use cases for multi-mappable columns.

Aggregating columns

If you want to aggregate multiple columns in your file into a single column, you can ask your user to map them all to the same multi-mappable column. For example, you are uploading a file with uncollected payments. Your customer uploads a file with a column for overdue payments by region (eg. overdue_payments_US, overdue_payments_CA, overdue_payments_MX). Your customer could map all of the columns to a multi-mappable column called overdue_payments and you could use a postmapping code hook to sum the values together into a single column.

Timeseries data / Validating multiple columns of the same type

If multiple columns represent the same "type" of data, you can validate them using a multi-mappable column. For example, if there are a number of columns representing sensor data collected at different points in time, you may want to define a Number template column with validation options specific to the sensor data (eg. decimals or numeric range). Each relevant uploaded column can be mapped to this template column, and they can be validated and transformed in the same way using OneSchema's pre-built validations, webhooks, and code hooks. The column values can be collected separately on import, or they can be aggregated using postmapping code hooks.

Example

To define a template column as multi-mappable, enable the additional option from template column options modal.

These columns are able to be mapped multiple times in the mapping pane.

The Review & Finalize pane shows each column with the template column validations applied. All column values will be reported on import.

How it looks in JSON imports

When receiving the validated data via JSON (eg. through front-end passthrough or importer webhooks), multi-mappable columns are denoted in the columns array with the field is_multi_mappable: true.

Multi-mappable column values are reported as an array of objects; the column_index field identifies the index of the column in the columns array so that the original uploaded column can be identified. Note that multi-mappable columns values will always reported in array form even if there is only one corresponding uploaded column.

{
  "embed_id": <EMBED_ID>,
  "template_key": "temperature_sensors",
  "sheet_id": <SHEET_ID>,
  "columns": [
    {
      "sheet_column_name": "Area name",
      "template_column_name": "Area name",
      "template_column_key": "area_name"
    },
    {
      "sheet_column_name": "Ground temp 1",
      "template_column_name": "Ground Temperature",
      "template_column_key": "ground_temperature",
      "is_multi_mappable": true
    },
    {
      "sheet_column_name": "Ground temp 2",
      "template_column_name": "Ground Temperature",
      "template_column_key": "ground_temperature",
      "is_multi_mappable": true
    },
    {
      "sheet_column_name": "Ground temp 3",
      "template_column_name": "Ground Temperature",
      "template_column_key": "ground_temperature",
      "is_multi_mappable": true
    }
  ],
  "count": 6,
  "records": [
    {
      "area_name": "Area 1",
      "ground_temperature": [
        {
          "value": "59.40",
          "column_index": 1 // Corresponds to the "Ground temp 1" column
        },
        {
          "value": "59.90",
          "column_index": 2 // Corresponds to the "Ground temp 2" column
        },
        {
          "value": "60.90",
          "column_index": 3 // Corresponds to the "Ground temp 3" column
        }
      ]
    },
    {
      "ground_temperature": [
        {
          "value": "56.80",
          "column_index": 1
        },
        {
          "value": "57.00",
          "column_index": 2
        },
        {
          "value": "57.60",
          "column_index": 3
        }
      ],
      "area_name": "Area 2"
    },
    {
      "ground_temperature": [
        {
          "value": "61.70",
          "column_index": 1
        },
        {
          "value": "62.20",
          "column_index": 2
        },
        {
          "value": "62.90",
          "column_index": 3
        }
      ],
      "area_name": "Area 3"
    },
    {
      "ground_temperature": [
        {
          "value": "53.80",
          "column_index": 1
        },
        {
          "value": "54.50",
          "column_index": 2
        },
        {
          "value": "55.40",
          "column_index": 3
        }
      ],
      "area_name": "Area 4"
    },
    {
      "area_name": "Area 5",
      "ground_temperature": [
        {
          "value": "58.50",
          "column_index": 1
        },
        {
          "value": "59.00",
          "column_index": 2
        },
        {
          "value": "59.40",
          "column_index": 3
        }
      ]
    }
  ],
  "error_records": [
    {
      "data": {
        "area_name": "Area 6",
        "ground_temperature": [
          {
            "value": "63.1",
            "column_index": 1
          },
          {
            "value": "63.7",
            "column_index": 2
          },
          {
            "value": "64.40",
            "column_index": 3
          }
        ]
      },
      "errors": {
        "ground_temperature": [
          {
            "error_codes": [2],
            "column_index": 1
          },
          {
            "error_codes": [2],
            "column_index": 2
          }
        ]
      }
    }
  ]
}

In this example, ground_temperature is a multi-mappable column. Uploaded columns Ground temp 1, Ground temp 2, and Ground temp 3 are mapped to this template column. The first record contains three objects in the array for ground_temperature - the value 59.40 comes from the Ground temp 1 column, value 59.90 comes from the Ground temp 2 column, and value 60.90 comes from the Ground temp 3 column. Errors are reported similarly with a column_index in the errors field, alongside the error_codes.