---
title: "Creating custom integrations for SFTP or API data sources"
slug: "record-capture-framework"
tags: ["record capture framework"]
updated: 2026-05-15T09:59:05Z
published: 2026-05-15T09:59:05Z
canonical: "support.lusid.com/record-capture-framework"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://support.lusid.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Creating custom integrations for SFTP or API data sources

LUSID’s record capture framework enables you to build custom integrations that ingest data into LUSID from your own SFTP servers or external APIs. These self-built integrations function similarly to standard LUSID integrations, allowing you to process data from sources that don’t have dedicated integrations.

This integration feature is ideal for:

- Ingesting trades from external APIs without dedicated LUSID integrations
- Processing data from your own SFTP servers
- Triggering custom workflows based on incoming data
- Building flexible, configurable data pipelines

> [!NOTE]
> Prerequisites
> 
> Before getting started, ensure you have:
> 
> - [x] [Contacted your FINBOURNE representative](https://www.finbourne.com/contact) to enable the custom integrations framework in your LUSID domain
> - [x] An SFTP server or API endpoint with authentication credentials

## Supported data types and methods

Currently, you can map and ingest the following entities into LUSID via the custom integrations framework:

- Instruments
- Portfolio properties
- Holdings
- Transactions
- Quotes
- Legal entities
- Relational datasets

## Setting up the integration

### Step 1: Create an integration instance

An integration instance is a configuration of data points to import from an external SFTP or API.

1. Navigate to **Integrations > Dashboard**.
2. Click **Create instance** and select **Record capture framework** from the available integrations. ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image-8SL33EPE.png)
3. Specify the following in the **Integration details** tab:
  - **Name**: A friendly name for this integration instance
  - **Description**: A detailed description of the instance
4. Go to the **Integration settings** tab and specify the following:
  - **Run date**: Optional; defines the date for which the integration should run, defaulting to yesterday’s date (most recent close of business (COB)). You should only populate this field for adhoc reruns.
  - **File date**: Optional; defines the date for which LUSID should choose a file, defaulting to today’s date. You should only populate this field for adhoc reruns.
  - **Drive root folder**: The location of your control file in LUSID Drive.
  - **Control filename**: The name of your JSON configuration file that tells LUSID how to process the data in a custom integration file.
  - **Additional parameters**: The following additional parameters are currently supported:
    - `row_limit:<integer>`: Limit the number of rows processed from the input file, for example `row_limit:20`
  - **API configuration**: If importing data from an API, specify the following:
    - **API Base URL**
    - **API Username**
    - **API Password**
  - **SFTP configuration**: If importing data via SFTP, provide the SFTP configuration details
5. Click **Save** to create the integration instance.

### Step 2: Supply configuration files to LUSID Drive

The record capture framework uses two types of configuration files stored in LUSID Drive to define mappings for your custom integration:

#### Control file

The control file defines how LUSID should transform data retrieved from an external source. Each control file must contain an array of `fileSpecs` in JSON format specifying:

- Which Drive folder to store the retrieved file
- What to name the retrieved file
- The location of mapping files in Drive
- How to transform the source data

For example:

```json
{
  "FileReference": "positions-file",
  "FileCategory": "primary",
  "SftpServer": 1,
  "SftpSourceFolder": "/acme/positions/Data",
  "DriveSubfolder": "COBDATE:yyyy-MM-dd",
  "FileNameMask": "POSITIONS_COBDATE:yyyyMMdd_*.csv",
  "RenameAs": "positions.csv",
  "DataReaderType": "CsvReader",
  "MappingFile": "positions-mapping.json",
  "ImportTypes": ["Instruments", "Holdings"]
}
```

See [Appendix A](/v1/docs/record-capture-framework#appendix-a-control-file-fields) for the full list of control file fields with detailed descriptions.

#### Mapping file

The mapping file defines how data fields in the source file map to LUSID entities and, if necessary, how LUSID should transform values. Each mapping file can contain the following components in JSON format:

- The source columns from your input data
- Value sources that allow you to combine or transform values from multiple columns
- Map specific source values to target values
- Conditional value sources that enable conditional logic for mapping, including:
  - Instrument type selectors
  - Identifiers that reference value maps
- Load controls that define processing rules for each entity type
- Data quality checks that use [LUSID filtering syntax](/v1/docs/filtering-information-retrieved-from-lusid) to skip rows with specific characteristics
- Plug-in architecture that allows for custom hard-coded behaviour beyond the standard configuration options

For example:

```json
{
  "CsvSeparator": "Comma",
  "ColumnSpecs": [
    { "ColumnHeader": "ISIN", "ColumnReference": "IsinColumn" },
    { "ColumnHeader": "Quantity", "ColumnReference": "QtyColumn" }
  ],
  "ValueSources": [
    {
      "Name": "IsinValue",
      "ColumnReferences": ["IsinColumn"]
    }
  ],
  "InstrumentsLoadControl": {
    "CreateMissingInstruments": true,
    "Identifiers": [
      {
        "IdentifierType": "Isin",
        "IdentifierValueSourceName": "IsinValue",
        "IsUnique": true
      }
    ]
  }
}
```

See [Appendix B](/v1/docs/record-capture-framework#appendix-b-mapping-file-fields) for the full list of mapping file fields with detailed descriptions and more examples.

### Step 3: Configure portfolios for holdings/transactions loads

Before processing holdings or transactions, you must configure the target portfolios with the following properties:

| Property key | Valid values | Description | Required for |
| --- | --- | --- | --- |
| `Portfolio/rcf-config/IsIncluded` | `true` or `false` | Whether the integration should process the portfolio | Portfolio properties, holdings, transactions |
| `Portfolio/rcf-config/LinkedFundCode` | Fund code string | Maps the portfolio to a FundCode in the source data | Portfolio properties, holdings, transactions |
| `Portfolio/rcf-config/LoadPositions` | `true` or `false` | Enables holdings processing for the portfolio | Holdings |
| `Portfolio/rcf-config/LoadTransactions` | `true` or `false` | Enables transactions processing for the portfolio | Transactions |

You can use [PortfoliosLoadControl](/v1/docs/record-capture-framework#appendix-b-mapping-file-fields) to upsert these properties via the record capture framework itself. [Read about other ways to add properties to portfolios.](/v1/docs/how-do-i-add-a-property-to-an-entity)

## Running the integration

[See how to run the integration.](/v1/docs/setting-up-an-integration#running-an-integration)

## Monitoring and troubleshooting integration runs

Logs for LUSID’s record capture framework function like other LUSID integration logs.

[See how to monitor integrations and troubleshoot general issues.](/v1/docs/setting-up-an-integration#monitoring-integration-runs)

## Appendix A: Control file fields

LUSID’s record capture framework uses a control file to define which source files to process and how to retrieve them. Each entry in the control file is a `FileControlSpec` that specifies configuration such as:

- File retrieval
- File processing
- Exception handling tasks in the Workflow Service

### Naming the `FileControlSpec`

The following fields allow you to define the name and categorisation of this `FileControlSpec`:

#### `FileReference`

**Mandatory**; a unique name you can use to reference this `FileControlSpec` in other jobs.

#### `FileCategory`

Category of the `FileControlSpec` (e.g. `primary`, `auxiliary`)

### SFTP file retrieval parameters

If the integration retrieves files via SFTP, the following fields allow you to specify the source of the file to retrieve and how LUSID should transform it:

#### `SftpServer`

1-based index indicating which SFTP server to use (defined in system configuration).

#### `SftpSourceFolder`

The source folder path in the SFTP server (e.g. `/acme/data/transactions`).

#### `DriveSubfolder`

**Mandatory**; the LUSID Drive location to load files into. Supports `COBDATE:<format>` or `FILEDATE:<format>` patterns, for example `COBDATE:yyyy-MM-dd`.

#### `FileNameMask`

**Mandatory**; a matching pattern to match files on (e.g. `TRANSACTIONS_COBDATE:yyyyMMdd_*.csv`). Uses `COBDATE`/`FILEDATE` substitution.

#### `FileAvailabilityMarker`

A file pattern (typically with a `.cntl` extension) to determine if the source file is ready for retrieval. Supports `COBDATE:<format>` or `FILEDATE:<format>` patterns, for example `POSITIONS_COBDATE:yyyy-MM-dd_*.cntl`.

#### `RenameAs`

A name for the file once loaded into LUSID.

### API file retrieval parameters

If the integration retrieves data from an API, the following fields allow you to specify details for the API LUSID should retrieve data from:

#### `ApiRetrievalType`

An enum value for the type of API retrieval out of the following:

- `None`
- `Enfusion`

#### `ReportPath`

The path to append to the API URL for retrieving reports, for example `/api/holdings`.

#### `ReportFilter`

A filter to apply to the report request URL, for example `&valueDate=10/28/2026`.

### File import parameters

The following fields allow you to specify import configuration details:

#### `DataReaderType`

One of the following data reader types:

| Reader type | Description | Required additional [mapping file](/v1/docs/record-capture-framework#appendix-b-mapping-file-fields) field |
| --- | --- | --- |
| `CsvReader` | Process comma- or pipe-delimited text files | [CsvSeparator](/v1/docs/record-capture-framework#csvseparator) |
| `ExcelReader` | Process `.xlsx` workbooks | [ExcelSpec](/v1/docs/record-capture-framework#excelspec1) |
| `FixedWidthReader` | Process text files where fields are identified by character position and length | [FixedWidthRowSpecs](/v1/docs/record-capture-framework#excelspec) |
| `EnfusionApiReader` | Process report rows from Enfusion API | N/A |

#### `MappingFile`

The name of the [mapping file](/v1/docs/record-capture-framework#appendix-b-mapping-file-fields) for this integration in Drive.

#### `ImportTypes`

An array of any of the following data types the files in this integration can import:

- `Instruments`
- `Holdings`
- `Portfolios` (properties to existing portfolios)
- `Transactions`
- `Quotes`
- `LegalEntities`
- `RelationalDatasets`

#### `MaxIngestionFailures`

A limit for the number of ingestion failures that can occur for the file before LUSID stops the import.

### Exception handling parameters

The following fields allow you to specify how LUSID should handle data quality check failures in runs of the integration:

#### `WorkflowTaskScope` and `WorkflowTaskCode`

The scope and code of a [task definition](/v1/docs/how-do-i-create-a-task-definition) in LUSID’s workflow service that LUSID should kick off a run of for DQ check failures.

### LUSID data upsert parameters

The following fields allow you to specify how the integration should configure certain LUSID data points:

#### `PortfolioScope`

Specify a portfolio scope to only upsert holdings data to portfolios in the specified scope. This can be useful if you want to target a specific portfolio scope for holdings data imports, for example:

```json
{
  "FileReference": "Holdings-Finbourne-Examples",
  "MappingFile": "holdings-mapping.json",
  "ImportTypes": ["Holdings"],
  "PortfolioScope": "Finbourne-Examples"
}
```

If no scope is specified, the integration processes all portfolios that match all other criteria, regardless of their scope.

#### `InstrumentScopes`

Specify an ordered list of LUSID scopes to search during instrument matching. The integration creates new instruments in the first scope in the list.

#### `CutLabel`

Specify a LUSID [cut label](/v1/docs/what-is-a-cut-label) to use as the `EffectiveAt` datetime for all the data the integration upserts. Defaults to 00:00 UTC.

### Third party integration parameters

The following fields allow you to specify fields required for certain third party integrations:

#### `BloombergRefreshInstanceId`

Optionally, specify an integration instance ID to populate the value of the [Bloomberg Data Licence Per Security](/v1/docs/bloomberg-data-license-per-security#step-3-select-the-instruments-to-import-data-for) `Instrument/LUSIDConfig-Bloomberg-DLPerSecurity/RefreshReferenceData` property.

## Appendix B: Mapping file fields

LUSID’s record capture framework uses a mapping file to define how data from source files should be parsed, validated, and loaded into LUSID.

The integration guides data through the mapping file configuration in the following order:

1. Reads the file and applies `ColumnSpecs`
2. Validates data using `DQChecks`
3. If `InstrumentsLoadControl` is specified, creates/updates instruments
4. If `PortfoliosLoadControl` is specified, updates portfolio properties
5. If `HoldingsLoadControl` is specified, upserts holdings
6. If `TransactionsLoadControl` is specified, upserts transactions
7. If `QuotesLoadControl` and/or `RelationalDatasetsLoadControl` is specified, upserts quotes and/or relational datasets

### `CsvSeparator`

Required when `DataReaderType` is set to `CsvReader`. Specify one of the following options for the delimiter used in CSV import files:

- `Comma`: Comma-separated (`,`) values (default)
- `Pipe`: Pipe-separated (`|`) values
- `Tab`
- `None`: Uses default behaviour

### `ExcelSpec`

Required when `DataReaderType` is set to `ExcelReader`. Specify the following fields to define how to read data from an Excel file:

- `SheetName`: Case-sensitive worksheet name to process data from
- `HeaderRowNumber`: Row number of the column headers in the Excel worksheet. Note that blank or duplicate headers cause processing to fail.
- `GlobalCells`: An array of cells from the area above the headers (for example, fund name or report date) attached to every data row. Each list item should contain:
  - `CellReference`: The cell name in the Excel worksheet, for example `B2`
  - `Reference`: A name you can use to refer to this column in `ColumnSpecs`/`ValueSources` as usual

For example:

```json
"ExcelSpec": {
  "SheetName": "EUHoldingsSheet",
  "HeaderRowNumber": 1,
  "GlobalCells": [
    { "CellReference": "B2", "Reference": "ReportDateColumn" }
  ]
}
```

### `ColumnSpecs`

Specify an array of the columns in your source file and how to reference them in this mapping file. Each list item should contain:

- `ColumnHeader`: The column name as it appears in the source file
- `ColumnReference`: A reference name ending in `column` to use throughout the mapping file

For example:

```json
"ColumnSpecs": [
  {
    "ColumnHeader": "Instrument ID",
    "ColumnReference": "InstrumentIdColumn"
  },
  {
    "ColumnHeader": "Price",
    "ColumnReference": "PriceColumn"
  }
]
```

### `FixedWidthRowSpecs`

Required when `DataReaderType` is set to `FixedWidthReader`. Specify an array of row specifications for parsing plain-text files where fields are identified by character position and length (rather than delimiters). Each list item should contain:

- `Reference`: A label to identify this record type in the logs
- `Global`: Set to `true` to mark a header or summary row. The framework stores values from this row and makes them available to subsequent rows via `FromGlobal: true`.
- `IdentifierSpec`: Specifies how to recognize this record type
  - `StartPos`: Character position where the identifier starts (usually `1`)
  - `Length`: Number of characters in the identifier. For example, a single-character value `H` requires a length of `1`, while a multi-character value `HDR` requires a length of `3`.
  - `IdentifierValue`: An exact string of characters that identify this record type (including whitespace)
- `RowItemSpecs`: An array of fields to extract from a line - in other words, each list item is a column you can reference in [ValueSources](/v1/docs/record-capture-framework#valuesources)
  - `Reference`: Column name to use in `ValueSources`
  - One of either:
    - `FromGlobal`: Set to `true` to use value from a global row instead of reading from the current line
    - `StartPos` and `Length`: Character position where the field starts and the number of characters to read, respectively

> [!NOTE]
> Note
> 
> All `StartPos` values are 1-indexed (the first character is position 1, not 0).

For example, if you wanted to load data from the following text file:

```plaintext
H20250101ACME FUND
D001     IBM    100   1500.00
D002     MSFT    50    420.00
```

You could use the following configuration:

```json
"FixedWidthRowSpecs": [
  {
    "Reference": "HeaderRow",
    "Global": true,
    "IdentifierSpec": { "StartPos": 1, "Length": 1, "IdentifierValue": "H" },
    "RowItemSpecs": [
      { "Reference": "CobDateColumn",  "StartPos": 2,  "Length": 8 },
      { "Reference": "FundNameColumn", "StartPos": 10, "Length": 20 }
    ]
  },
  {
    "Reference": "DetailRow",
    "Global": false,
    "IdentifierSpec": { "StartPos": 1, "Length": 1, "IdentifierValue": "D" },
    "RowItemSpecs": [
      { "Reference": "CobDateColumn",  "FromGlobal": true },
      { "Reference": "FundNameColumn", "FromGlobal": true },
      { "Reference": "SeqNumColumn",   "StartPos": 2,  "Length": 8 },
      { "Reference": "TickerColumn",   "StartPos": 10, "Length": 6 },
      { "Reference": "QuantityColumn", "StartPos": 16, "Length": 6 },
      { "Reference": "PriceColumn",    "StartPos": 22, "Length": 8 }
    ]
  }
]
```

### `ValueSources`

Specify an array of how to extract and transform values from columns in import files. Each list item should contain a unique reference `Name` ending in `Value` to use throughout the mapping file, and either:

- [Value](/v1/docs/record-capture-framework#value)
- [LogicalExpressions](/v1/docs/record-capture-framework#logicalexpressions)
- [ColumnReferences](/v1/docs/record-capture-framework#columnreferences)
- [MathematicalExpression](/v1/docs/record-capture-framework#mathematicalexpression)

#### `Value`

Hardcode a specific value, for example:

```json
{
  "Name": "InstrumentScopeValue",
  "Value": "default"
}
```

#### `LogicalExpressions`

Allows you to specify an array of complex logic for value transformations based on conditions.

Each list item should contain:

- `Condition`: A LUSID filtering syntax expression to evaluate the value source against. It must use the format `<ValueSource> <Operator> <Specific value/ValueSource>`.

> [!NOTE]
> Supported operators
> 
> The integration framework supports the following operators in addition to the standard [LUSID filtering syntax supported operators](/v1/docs/filtering-information-retrieved-from-lusid#appendix-a-supported-operators):
> 
> - `InMapKeys`: Value is a `key` in the defined [ValueMaps](/v1/docs/record-capture-framework#valuemaps), for example `TypeValue InMapKeys ValidTypesMap`
> - `InMapValues`: Value is a `value` in the defined [ValueMaps](/v1/docs/record-capture-framework#valuemaps), for example `StatusValue InMapValues StatusMap`
- `Result`: The value to return if the condition is met; this can be any of the following:
  - A specific value
  - One of the defined `ValueSources`

Always include an empty condition (`"Condition": "",`) as the last `LogicalExpression`; this ensures unexpected values are handled.

For example:

```json
{
  "Name": "InstrumentTypeSelectorValue",
  "LogicalExpressions": [
    {
      "Condition": "AssetClassValue eq \"Equity\"",
      "Result": "Equity"
    },
    {
      "Condition": "AssetClassValue eq \"Cash\"",
      "Result": "SimpleInstrument"
    },
    {
      "Condition": "",
      "Result": "SimpleInstrument"
    }
  ]
}
```

#### `ColumnReferences`

A list of the columns that the value should be sourced from. You can additionally specify the following:

`ValueConversion`

Specify one of the following options to tell LUSID how to combine multiple columns:

- `None`: Takes the value from the first `ColumnReference`

```json
{
  "Name": "SedolValue",
  "ColumnReferences": ["SedolColumn"],
  "ValueConversion": "None"
}
```
- `Concatenate`: Joins multiple `ColumnReference` values together using the specified `ConcatenationSeparator`

```json
{
  "Name": "FullNameValue",
  "ColumnReferences": ["FirstNameColumn", "LastNameColumn"],
  "ValueConversion": "Concatenate",
  "ConcatenationSeparator": " "
}
```
- `FirstNonEmpty`: Uses the first non-empty `ColumnReference` value

```json
{
  "Name": "PreferredIdValue",
  "ColumnReferences": ["IsinColumn", "SedolColumn", "ClientIdColumn"],
  "ValueConversion": "FirstNonEmpty"
}
```
- `RegexExtraction`: The integration applies a regex pattern to the value from either:

```json
{
	"Name": "IsinFromDescriptionValue",
	"ColumnReferences": ["DescriptionColumn"],
	"ValueConversion": "RegexExtraction",
	"RegexPattern": "ISIN:\\\\s*([A-Z0-9]{12})"
}
```

> [!NOTE]
> Note
> 
> - Regex patterns time out after 1 second; to avoid timeouts, use patterns that target specific locations (for example, `^ISIN:\\s*([A-Z0-9]{12})` rather than `.*ISIN.*`).
> - As the mapping file is JSON formatted, ensure you use double backslashes (for example, write `\\d` for a digit).
> - The framework only extracts the first parenthesised group in your pattern (if used).
  - The first column in `ColumnReferences`
  - If no `ColumnReferences` are specified, the first item listed in `ValueSources`

`ConcatenationSeparator`

The separator to use when ValueConversion is set to `Concatenate`; defaults to a space (`“ “`)

`ValueMapName`

Specify the name of a [ValueMap](/v1/docs/record-capture-framework#valuemaps) to use for the value transformation, for example:

```json
{
  "Name": "TransactionTypeValue",
  "ColumnReferences": ["TxnTypeColumn"],
  "ValueMapName": "TransactionTypeMap"
}
```

> [!NOTE]
> Note
> 
> If the column value is not a key in the specified `ValueMap`, the framework throws an error. You should specify a [DQChecks](/v1/docs/record-capture-framework#dqchecks) check that validates `InMapKeys` before applying the map, or ensure your `ValueMap` includes all possible input values.

`MultiplierValueSourceName`

For numeric values, use this field to specify one of the defined `ValueSources` to multiply the numeric value by, for example:

```json
{
  "Name": "ScaledQuantityValue",
  "ColumnReferences": ["QuantityColumn"],
  "MultiplierValueSourceName": "ContractSizeValue"
}
```

This can be useful for things such as:

- Converting contract quantities to actual units
- Scaling prices by exchange rates
- Calculating total consideration
- Applying percentage conversions

> [!NOTE]
> Note
> 
> - The base value and multiplier value must be parsaeable as decimals.
> - LUSID returns the multiplied result as a string.
> - LUSID throws an error if parsing fails, detailing which value failed.

`IsCurrencyIdentifier`

Set to `true` to add the prefix `CCY_` to each value, for example:

```json
{
  "Name": "CurrencyInstrumentIdValue",
  "ColumnReferences": ["CurrencyCodeColumn"],
  "IsCurrencyIdentifier": true
}
```

With the above example, LUSID might transform an original value of `USD` to `CCY_USD`.

#### `MathematicalExpression`

Specify a formula that can use other ValueSources to output a value. You can use any of the following in your formula:

- Names of `ValueSources`
- Numeric literals
- `+`, `-`, `*`, `/`

> [!NOTE]
> Note
> 
> The framework evaluates strictly left-to-right - there is no operator precedence.

For example:

```json
{
  "Name": "CostValue",
  "MathematicalExpression": "PrincipalAmountValue / UnitsValue * 100"
}
```

### `RowIdentificationSourceName`

Specify which [ValueSources](/v1/docs/record-capture-framework#valuesources) to use for identifying rows in logging and error messages.

### `ConditionalValueSources` (legacy field)

> [!WARNING]
> Important
> 
> You must now supply conditional logic directly on [ValueSources](/v1/docs/record-capture-framework#valuesources) using `LogicalExpressions`. `ConditionalValueSources` is a legacy field and is merged into `ValueSources` at load time.

Allows you to specify an array of complex logic for value transformations based on conditions. See [LogicalExpressions](/v1/docs/record-capture-framework#logicalexpressions).

### `ValueMaps`

> [!NOTE]
> Note
> 
> If the column value is not a key in the specified value map, the integration throws an error. Use a `DQCheck` with an `InMapKeys` operator to validate before applying the map, or ensure your value map includes all possible input values.
> 
> 
> 
> You can set up a conditional `ValueSource` to check a value exists in a value map using the `InMapKeys` or `InMapValues` operators, for example:
> 
> ```json
> {
>   "Condition": "RawTypeValue InMapKeys TransactionTypeMap",
>   "Result": "Pass"
> }
> ```

#### Key-value maps

You can specify an array of key-value pairs for mapping specific values. Each list item should contain:

- A unique reference `Name` suffixed with `Map`
- A dictionary of `Map` key-value pairs

```json
"ValueMaps": [
  {
    "Name": "TransactionTypeMap",
    "Map": {
      "BUY": "Buy",
      "SELL": "Sell",
      "DIVIDEND": "StockDividend"
    }
  },
  {
    "Name": "FrequencyMap",
    "Map": {
      "Monthly": "1M",
      "Quarterly": "3M",
      "Semi-Annual": "6M",
      "Annual": "1Y"
    }
  }
]
```

#### Map from a Luminesce view at runtime

When lookup data lives in LUSID, you can populate a map from a Luminesce view at runtime.

Each list item should contain the following:

- `Name`: A unique reference name suffixed with `Map`
- `View`: The name of the [Luminesce custom view](/v1/docs/creating-custom-views-sysadminsetupview)
- `InputColumn`: The data to pass into the query at runtime
- `OutputColumn`: The data to use from the query output

> [!NOTE]
> Note
> 
> - The view must exist before an integration run starts.
> - Queries timeout after 5 minutes.

For example:

```json
"ValueMaps": [
  {
    "Name": "PolicyToInstrumentMap",
    "View": "PolicyCodeLookup",
    "InputColumn": "PolicyCode",
    "OutputColumn": "LusidInstrumentId"
  }
]
```

### `DQChecks`

Allows you to specify how the integration should validate data before processing in LUSID. You can define filters for how to present the result of a DQ check on a row in the logs.

`DQChecks` apply across all your load controls; a `Fail` result prevents the load controls from processing the row. The `Filter` field available on individual load controls (such as [HoldingsLoadControl](/v1/docs/record-capture-framework#holdingsloadcontrol)) is scoped to that entity only, so a row filtered out at the holdings stage can still be processed as a transaction, for example.

Each list item should contain:

- A unique reference `Name`
- A list of ordered `LogicalExpressions` to evaluate for each row of data containing:
  - `Condition`: A [LUSID filtering syntax expression](/v1/docs/filtering-information-retrieved-from-lusid) to evaluate the value source against, as with conditional [ValueSources](/v1/docs/record-capture-framework#valuesources)

> [!NOTE]
> Note
> 
> - You cannot mix `AND` and `OR` inside a single condition.
> - If no condition matches, the integration sets the `Result` value to `Fail`.
  - `Result`: The value to return in the logs if the `Condition` is met; this can be any of the following:

> [!NOTE]
> Note
> 
> If a check hits a `SkipWithMessage` or `SkipSilently` result, the integration stops any further DQ checking for that row of data.
    - `Pass`: No error recorded in the logs
    - `Fail`: Logged as error and row marked as failed
    - `SkipWithMessage`: Row is skipped and recorded in the logs as skipped
    - `SkipSilently`: Row is skipped but nothing is recorded in the logs
- Optionally, a `Message` to log if a check fails

For example:

```json
"DQChecks": [
  {
    "Name": "RequireInstrumentId",
    "Message": "Instrument ID is required",
    "LogicalExpressions": [
      {
        "Condition": "InstrumentIdValue neq \"\"",
        "Result": "Pass"
      },
      {
        "Condition": "",
        "Result": "Fail"
      }
    ]
  },
  {
  "Name": "SkipClosedPositions",
  "Message": "Skipping closed positions",
  "LogicalExpressions": [
    {
      "Condition": "QuantityValue eq \"0\" AND StatusValue eq \"Closed\"",
      "Result": "SkipWithMessage"
    },
    {
      "Condition": "",
      "Result": "Pass"
    }
  ]
}
]
```

### Load controls

Each entity type has its own load control section defining how the entity should be processed. You only need to include sections for the entity types you specified in your control file `ImportTypes` list.

#### `InstrumentsLoadControl`

Required when `ImportTypes` includes `Instruments`.

> [!NOTE]
> Note
> 
> The record capture framework can only create instruments of type `SimpleInstrument` and `Equity`. The framework can match against and update other instrument types, but not create them.

| Field | Required? | Description |
| --- | --- | --- |
| `CreateMissingInstruments` | ✅ | `true`: Create new instruments `false`: Only update existing instruments |
| `Identifiers` | ✅ | A list of identifier types to extract from data; at least one must include `IsUnique: true` |
| `InstrumentMatchingMethod` | ❌ | Define how the framework identifies existing intruments using one of the following values: - Default: Match on the identifier with `IsUnique: true` - `OrderedIdentifierCascade`: Try each identifier in list order, ignoring the value of `IsUnique`. Note this option does not support `CreateMissingInstruments: true` - `ValueMap`: Look up a [LUID](/v1/docs/what-is-a-lusid-instrument-id-or-luid) using `InstrumentIdentifierValueSourceName` - note you must define the `InstrumentIdentifierValueSourceName` in a `ValueMap` |
| `InstrumentDefControls` | ✅ | Type-specific instrument definitions for instrument creation. Supported instrument types: - `Equity` - required fields: - `DomesticCurrencySourceName` - `SimpleInstrument` - required fields: - `SimpleInstrumentTypeSourceName` - source must return one of the following: - `None` - `Bond` - `Credit` - `Equity` - `Fx` - `Future` - `Other` - `DomesticCurrencySourceName` - (Optional) `ForeignCurrencySourceName` |
| `InstrumentTypeSelectorSourceName` | ❌ | A conditional `ValueSource` that determines the instrument type |
| `CommonProperties` | ❌ | A list of properties to apply to all instrument types; [see Reference: Properties](/v1/docs/record-capture-framework#load-controls-properties) |
| `UnderlyingInstrumentHandlingSourceName` | ❌ | A conditional `ValueSource` for handling underlying instruments for derivatives; the value source must return one of the following values: - `CreateUnderlyingInstruments`: Create underlying instrument if missing - `SetUnderlyingIdToClientInternal`: Set underlying ID to `ClientInternal` identifier - `None` |
| `BloombergDataRefreshSourceName` | ❌ | A conditional `ValueSource` that resolves to `true` or `false` - this specifies whether to add the [Bloomberg](/v1/docs/bloomberg-data-license-per-security#step-3-select-the-instruments-to-import-data-for) `RefreshReferenceData` property and instance ID (defined in the [control file](/v1/docs/record-capture-framework#control-file)) to the instrument |

For example:

```json
"InstrumentsLoadControl": {
  "InstrumentTypeSelectorSourceName": "InstrumentTypeSelectorValue",
  "UnderlyingInstrumentHandlingSourceName": "UnderlyingHandlerValue",
  "BloombergDataRefreshSourceName": "BloombergDataRefreshValue",
  "CreateMissingInstruments": true,
  "Identifiers": [
    {
      "IdentifierType": "Isin",
      "IdentifierValueSourceName": "IsinValue",
      "IsUnique": true
    },
    {
      "IdentifierType": "Sedol",
      "IdentifierValueSourceName": "SedolValue",
      "IsUnique": false
    },
    {
      "IdentifierType": "ClientInternal",
      "IdentifierValueSourceName": "ClientIdValue",
      "IsUnique": false
    }
  ],
  "InstrumentDefControls": [
    {
      "InstrumentType": "SimpleInstrument",
      "InstrumentNameSourceName": "InstrumentNameValue",
      "DomesticCurrencySourceName": "CurrencyValue",
      "ForeignCurrencySourceName": "ForeignCcyValue",
      "SimpleInstrumentTypeSourceName": "SimpleTypeValue",
      "Properties": []
    },
    {
      "InstrumentType": "Equity",
      "InstrumentNameSourceName": "InstrumentNameValue",
      "DomesticCurrencySourceName": "CurrencyValue",
      "Properties": [
        {
          "LusidPropertyName": "Instrument/default/Sector",
          "ValueSourceName": "SectorValue"
        }
      ]
    }
  ],
  "CommonProperties": [
    {
      "LusidPropertyName": "Instrument/default/AssetClass",
      "ValueSourceName": "AssetClassValue"
    }
  ]
}
```

#### `PortfoliosLoadControl`

Required when `ImportTypes` includes `Portfolios`.

> [!NOTE]
> Note
> 
> All portfolios you wish to include in integration runs must be decorated with the following properties:
> 
> - `Portfolio/rcf-config/LinkedFundCode` matching the `FundCode` value in your source data
> - `Portfolio/rcf-config/IsIncluded` set to `true`

| Field | Required? | Description |
| --- | --- | --- |
| `FundCodeSourceName` | ✅ | One of the `ValueSources` that specifies which existing portfolio to update |
| `Properties` | ✅ | A list of portfolio properties to apply to all portfolios specified; [see Reference: Properties](/v1/docs/record-capture-framework#load-controls-properties) |

For example:

```json
"PortfoliosLoadControl": {
  "FundCodeSourceName": "FundCodeValue",
  "Properties": [
    {
      "LusidPropertyName": "Portfolio/default/Strategy",
      "ValueSourceName": "StrategyValue"
    },
    {
      "LusidPropertyName": "Portfolio/default/Manager",
      "ValueSourceName": "ManagerValue"
    }
  ]
}
```

#### `HoldingsLoadControl`

Required when `ImportTypes` includes `Holdings`.

> [!NOTE]
> Note
> 
> All portfolios you wish to include in holdings integration runs must be decorated with the following properties:
> 
> - `Portfolio/rcf-config/LinkedFundCode` matching the `FundCode` value in your source data
> - `Portfolio/rcf-config/IsIncluded` set to `true`
> - `Portfolio/rcf-config/LoadPositions` set to `true`

| Field | Required? | Description |
| --- | --- | --- |
| `FundCodeSourceName` | ✅ | One of the `ValueSources` that specifies which existing portfolio to update |
| `CurrencySourceName` | ✅ | One of the `ValueSources` that specifies the currency for holdings |
| `CostSourceName` | ✅ | One of the `ValueSources` specifying the total cost for holdings |
| `QuantitySourceName` | ✅ | One of the `ValueSources` specifying the number of units for holdings |
| `TaxlotPriceSourceName` | ✅ | One of the `ValueSources` specifying the price per unit for holdings |
| `EffectiveDateSourceName` | ❌ | One of the `ValueSources` specifying an effective date for holdings; defaults to the file date |
| `SubHoldingKeys` | ❌ | A list of [SHKs](/v1/docs/what-is-a-sub-holding-key-shk) with `ValueSources` to apply to holdings |
| `Properties` | ❌ | A list of holding properties to apply to all portfolios specified; [see Reference: Properties](/v1/docs/record-capture-framework#load-controls-properties) |
| `Filter` | ❌ | `DQChecks` to only process specific rows of holdings data; as with `DQChecks`, each check should contain the following: - `Name` - `LogicalExpressions` containing: - `Condition` - `Result` |
| `Aggregator` | ❌ | Specify how to combine multiple rows into a single holding; see [Reference: Aggregators](/v1/docs/record-capture-framework#reference-aggregators) |

For example:

```json
"HoldingsLoadControl": {
  "FundCodeSourceName": "FundCodeValue",
  "EffectiveDateSourceName": "EffectiveDateValue",
  "CurrencySourceName": "CurrencyValue",
  "CostSourceName": "CostValue",
  "QuantitySourceName": "UnitsValue",
  "TaxlotPriceSourceName": "PriceValue",
  "SubHoldingKeys": [
    {
      "LusidPropertyName": "Transaction/default/Strategy",
      "ValueSourceName": "StrategyValue"
    }
  ],
  "Properties": [
    {
      "LusidPropertyName": "Holding/default/MarketValue",
      "LusidPropertyDataType": "number",
      "ValueSourceName": "MarketValueUSDValue"
    }
  ],
  "Filter": [
  {
    "Name": "ExcludeZeroQuantity",
    "LogicalExpressions": [
      {
        "Condition": "QuantityValue eq \"0\"",
        "Result": "SkipSilently"
      },
      {
        "Condition": "",
        "Result": "Pass"
      }
    ]
  }
]
  "Aggregator": {
    "KeyValueSources": ["FundCodeValue", "InstrumentIdValue", "StrategyValue"],
    "SumValueSources": ["QuantityValue", "CostValue"]
  }
}
```

#### `TransactionsLoadControl`

Required when `ImportTypes` includes `Transactions`.

> [!NOTE]
> Note
> 
> All portfolios you wish to include in transactions integration runs must be decorated with the following properties:
> 
> - `Portfolio/rcf-config/LinkedFundCode` matching the `FundCode` value in your source data
> - `Portfolio/rcf-config/IsIncluded` set to `true`
> - `Portfolio/rcf-config/LoadTransactions` set to `true`

| Field | Required? | Description |
| --- | --- | --- |
| `FundCodeSourceName` | ✅ | One of the `ValueSources` that specifies which existing portfolio to update |
| `TransactionIdSourceName` | ✅ | One of the `ValueSources` specifying the unique identifier for transactions |
| `TransactionTypeSourceName` | ✅ | One of the `ValueSources` specifying the LUSID [transaction type](/v1/docs/transactions) for transactions |
| `TransactionDateSourceName` | ✅ | One of the `ValueSources` specifying the trade date for transactions |
| `SettlementDateSourceName` | ✅ | One of the `ValueSources` specifying the settlement date for transactions |
| `UnitsSourceName` | ✅ | One of the `ValueSources` specifying the number of units for transactions |
| `TotalConsiderationCurrencySourceName` | ✅ | One of the `ValueSources` specifying the currency for transactions |
| `TotalConsiderationAmountSourceName` | ✅ | One of the `ValueSources` specifying the total consideration amount for transactions |
| `TransactionPriceSourceName` | ✅ | One of the `ValueSources` specifying the price per unit for transactions |
| `Properties` | ❌ | A list of transaction properties to apply to all transactions; [see Reference: Properties](/v1/docs/record-capture-framework#load-controls-properties) |
| `SubHoldingKeys` | ❌ | A list of [SHKs](/v1/docs/what-is-a-sub-holding-key-shk) with `ValueSources` to apply to transactions |
| `Filter` | ❌ | `DQChecks` to only process specific rows of transactions data; as with `DQChecks`, each check should contain the following: - `Name` - `LogicalExpressions` containing: - `Condition` - `Result` |
| `Aggregator` | ❌ | Specify how to combine multiple rows into a single transaction; see [Reference: Aggregators](/v1/docs/record-capture-framework#reference-aggregators) |

For example:

```json
"TransactionsLoadControl": {
  "FundCodeSourceName": "FundCodeValue",
  "TransactionIdSourceName": "TransactionIdValue",
  "TransactionTypeSourceName": "TransactionTypeValue",
  "TransactionDateSourceName": "TradeDateValue",
  "SettlementDateSourceName": "SettlementDateValue",
  "UnitsSourceName": "UnitsValue",
  "TotalConsiderationCurrencySourceName": "CurrencyValue",
  "TotalConsiderationAmountSourceName": "ConsiderationValue",
  "TransactionPriceSourceName": "PriceValue",
  "Properties": [
    {
      "LusidPropertyName": "Transaction/default/Broker",
      "ValueSourceName": "BrokerValue"
    }
  ],
  "Filter": [
  {
    "Name": "OnlyEquities",
    "LogicalExpressions": [
      {
        "Condition": "AssetClassValue eq \"Equity\"",
        "Result": "Pass"
      },
      {
        "Condition": "",
        "Result": "SkipSilently"
      }
    ]
  }
  ],
  "Aggregator": {
    "KeyValueSources": ["FundCodeValue", "InstrumentIdValue", "StrategyValue"],
    "SumValueSources": ["QuantityValue", "CostValue"]
  }
}
```

#### `QuotesLoadControl`

Required when `ImportTypes` includes `Quotes`.

| Field | Required? | Description |
| --- | --- | --- |
| `ScopeSourceName` | ✅ | One of the `ValueSources` that specifies which scope to upload quotes to |
| `EffectiveDateSourceName` | ✅ | One of the `ValueSources` specifying the effectiveAt date for quotes |
| `ProviderSourceName` | ✅ | One of the `ValueSources` specifying the data vendor for quotes |
| `InstrumentIdTypeSourceName` | ✅ | One of the `ValueSources` that specifies the instrument identifier type for quotes |
| `InstrumentIdSourceName` | ✅ | One of the `ValueSources` that specifies the instrument identifier for quotes |
| `QuoteTypeSourceName` | ✅ | One of the `ValueSources` that specifies the [quote type](/v1/docs/retrieving-data-from-the-quote-store) for quotes, for example `Price`/`Rate` |
| `FieldSourceName` | ✅ | One of the `ValueSources` that specifies the field for quotes, for example `mid`/`bid`/`ask` |
| `PriceValueSourceName` | ✅ | One of the `ValueSources` that specifies the price value for quotes |
| `CurrencySourceName` | ✅ | One of the `ValueSources` that specifies the currency or unit for quotes |
| `Filter` | ❌ | `DQChecks` to only process specific rows of transactions data; as with `DQChecks`, each check should contain the following: - `Name` - `LogicalExpressions` containing: - `Condition` - `Result` |
| `Aggregator` | ❌ | Specify how to combine multiple rows into a single quote; see [Reference: Aggregators](/v1/docs/record-capture-framework#reference-aggregators) |

For example:

```json
"QuotesLoadControl": {
  "ScopeSourceName": "QuoteScopeValue",
  "EffectiveDateSourceName": "QuoteDateValue",
  "ProviderSourceName": "ProviderValue",
  "InstrumentIdTypeSourceName": "IdTypeValue",
  "InstrumentIdSourceName": "InstrumentIdValue",
  "QuoteTypeSourceName": "QuoteTypeValue",
  "FieldSourceName": "FieldValue",
  "PriceValueSourceName": "PriceValue",
  "CurrencySourceName": "CurrencyValue",
  "Filter": []
}
```

#### `LegalEntitiesLoadControl`

Required when `ImportTypes` includes `LegalEntities`.

| Field | Required | Description |
| --- | --- | --- |
| `DisplayNameSourceName` | ✅ | One of the `ValueSources` that specifies the legal entity display name |
| `DescriptionSourceName` | ❌ | One of the `ValueSources` that contains the legal entity description |
| `Identifiers` | ✅ | An array of identifier types and value sources to extract from data |
| `Properties` | ❌ | An array of legal entity properties to apply to all legal entities; [see Reference: Properties](/v1/docs/record-capture-framework#load-controls-properties) |
| `Filter` | ❌ | `DQChecks` to only process specific rows of legal entities data; as with `DQChecks`, each check should contain the following: - `Name` - `LogicalExpressions` containing: - `Condition` - `Result` |

For example:

```json
"LegalEntitiesLoadControl": {
  "DisplayNameSourceName": "DisplayNameValue",
  "DescriptionSourceName": "DescriptionValue",
  "Identifiers": [
    {
      "IdentifierType": "LegalEntity/default/LEI",
      "IdentifierValueSourceName": "LeiValue"
    },
    {
      "IdentifierType": "LegalEntity/default/InternalId",
      "IdentifierValueSourceName": "InternalIdValue"
    }
  ],
  "Properties": [
    {
      "LusidPropertyName": "LegalEntity/default/Country",
      "ValueSourceName": "CountryValue"
    }
  ]
}
```

#### `RelationalDatasetsLoadControl`

Required when `ImportTypes` includes `RelationalDatasets`.

| Field | Required | Description |
| --- | --- | --- |
| `RelationalDatasetDefinitionScope` | ✅ | A string containing the scope for the [relational dataset definition](/v1/docs/how-do-i-create-a-relational-dataset-definition) |
| `RelationalDatasetDefinitionCode` | ✅ | A string containing the code for relational dataset definition |
| `SeriesScopeSourceName` | ❌ | One of the `ValueSources` specifying the series scope |
| `EntityTypeSourceName` | ❌ | One of the `ValueSources` specifying the entity type for the dataset record |
| `EntityIdentifierScopeSourceName` | ❌ | One of the `ValueSources` specifying the entity identifier scope |
| `Identifiers` | ❌ | A list of identifier types to extract from data; at least one must include `IsUnique: true` |
| `EffectiveAtSourceName` | ❌ | One of the `ValueSources` specifying the effectiveAt date for the data |
| `ValueFieldsMap` | ❌ | Map numeric/value dataset field names to `ValueSources` |
| `MetaDataFieldsMap` | ❌ | Map metadata dataset field names to `ValueSources` |
| `Filter` | ❌ | `DQChecks` to only process specific rows of transactions data; as with `DQChecks`, each check should contain the following: - `Name` - `LogicalExpressions` containing: - `Condition` - `Result` |
| Aggregator | ❌ | Specify how to combine multiple rows into a single record; see [Reference: Aggregators](/v1/docs/record-capture-framework#reference-aggregators) |

For example:

```json
"RelationalDatasetsLoadControl": {
  "RelationalDatasetDefinitionScope": "my-scope",
  "RelationalDatasetDefinitionCode": "my-dataset-code",
  "SeriesScopeSourceName": "SeriesScopeValue",
  "EntityTypeSourceName": "EntityTypeValue",
  "EntityIdentifierScopeSourceName": "EntityIdScopeValue",
  "Identifiers": [
    {
      "IdentifierType": "ClientInternal",
      "IdentifierValueSourceName": "RecordIdValue",
      "IsUnique": true
    }
  ],
  "EffectiveAtSourceName": "EffectiveDateValue",
  "ValueFieldsMap": {
    "Amount": "AmountValue",
    "Price": "PriceValue"
  },
  "MetaDataFieldsMap": {
    "Description": "DescriptionValue",
    "Category": "CategoryValue"
  },
  "Filter": []
}
```

#### Reference: Properties

Specify the following fields to define how to apply properties to a load control:

- `LusidPropertyName`: 3-stage [property key](/v1/docs/properties) in the format `<domain>/<scope>/<code>`
- `LusidPropertyDataType`: One of the following data types for property values:
  - `string` (default)
  - `number`
  - `date`
- `ValueSourceName`: Where to source the property value from; see [ValueSources](/v1/docs/record-capture-framework#valuesources)
- `IsPerpetual`: Set to `true` if the property is perpetual, or `false` if time-variant; [read more](/v1/docs/understanding-perpetual-and-time-variant-properties)
- `EffectiveFromName` and `EffectiveUntilName`: Where to source the effective date range from for time-variant properties; see [ValueSources](/v1/docs/record-capture-framework#valuesources)
- `DateFormats`: An array of date formats (for example, `["M/d/yyyy", "MM/dd/yyyy", "yyyy-MM-dd"]` ); the record capture framework attempts to apply each format to the value in order until it’s successful. The framework supports the following date formats:
  - `yyyy-MM-dd` (recommended)
  - `yyyy-MM-ddTHH:mm:ssZ` (recommended)
  - `dd/MM/yyyy`
  - `MM/dd/yyyy`
  - `yyyy-MM-ddTHH:mm:ss`
  - `dd-MMM-yyyy`
  - `yyyyMMdd`

> [!NOTE]
> Note
> 
> The framework combines the effective dates with the [CutLabel](/v1/docs/record-capture-framework#cutlabel) specified in the control file.

#### Reference: Aggregators

Specify the following fields to define how to combine rows into a single entity:

- `KeyValueSources`: A list of `ValueSources` names that define the grouping key; rows are combined when they have matching values for all these fields
- `SumValueSources`: A list of `ValueSources` names; when rows are combined, these values are added together

For example:

```json
"Aggregator": {
  "KeyValueSources": ["FundCodeValue", "InstrumentIdValue", "StrategyValue"],
  "SumValueSources": ["QuantityValue", "CostValue"]
}
```

### Examples

****Example A: Simple equity holdings file mapping****

Let’s imagine you want to load the following equity holdings from a CSV file:

```plaintext
ISIN,Name,Quantity,Price,Currency,Fund,Date
GB0002374006,Tesco PLC,1000,2.50,GBP,FUND001,2025-03-10
US0378331005,Apple Inc,500,150.00,USD,FUND001,2025-03-10
```

You might create the following mapping file:

```json
{
  "CsvSeparator": "Comma",
  "ColumnSpecs": [
    { "ColumnHeader": "ISIN", "ColumnReference": "IsinColumn" },
    { "ColumnHeader": "Name", "ColumnReference": "NameColumn" },
    { "ColumnHeader": "Quantity", "ColumnReference": "QtyColumn" },
    { "ColumnHeader": "Price", "ColumnReference": "PriceColumn" },
    { "ColumnHeader": "Currency", "ColumnReference": "CcyColumn" },
    { "ColumnHeader": "Fund", "ColumnReference": "FundColumn" },
    { "ColumnHeader": "Date", "ColumnReference": "DateColumn" }
  ],
  "RowIdentificationSourceName": "RowIdValue",
  "ValueSources": [
    {
      "Name": "RowIdValue",
      "ColumnReferences": ["IsinColumn", "FundColumn"],
      "ValueConversion": "Concatenate",
      "ConcatenationSeparator": "_"
    },
    {
      "Name": "IsinValue",
      "ColumnReferences": ["IsinColumn"]
    },
    {
      "Name": "InstrumentNameValue",
      "ColumnReferences": ["NameColumn"]
    },
    {
      "Name": "UnitsValue",
      "ColumnReferences": ["QtyColumn"]
    },
    {
      "Name": "PriceValue",
      "ColumnReferences": ["PriceColumn"]
    },
    {
      "Name": "CurrencyValue",
      "ColumnReferences": ["CcyColumn"]
    },
    {
      "Name": "FundCodeValue",
      "ColumnReferences": ["FundColumn"]
    },
    {
      "Name": "DateValue",
      "ColumnReferences": ["DateColumn"]
    },
    {
      "Name": "CostValue",
      "ColumnReferences": ["PriceColumn"],
      "MultiplierValueSourceName": "UnitsValue"
    }
  ],
  "DQChecks": [
    {
      "Name": "RequireIsin",
      "Message": "ISIN is required",
      "LogicalExpressions": [
        { "Condition": "IsinValue neq \"\"", "Result": "Pass" },
        { "Condition": "", "Result": "Fail" }
      ]
    },
    {
      "Name": "RequireQuantity",
      "Message": "Quantity must be greater than zero",
      "LogicalExpressions": [
        { "Condition": "UnitsValue gt \"0\"", "Result": "Pass" },
        { "Condition": "", "Result": "Fail" }
      ]
    }
  ],
  "InstrumentsLoadControl": {
    "CreateMissingInstruments": true,
    "Identifiers": [
      {
        "IdentifierType": "Isin",
        "IdentifierValueSourceName": "IsinValue",
        "IsUnique": true
      }
    ],
    "InstrumentDefControls": [
      {
        "InstrumentType": "Equity",
        "InstrumentNameSourceName": "InstrumentNameValue",
        "DomesticCurrencySourceName": "CurrencyValue",
        "Properties": []
      }
    ],
    "CommonProperties": []
  },
  "HoldingsLoadControl": {
    "FundCodeSourceName": "FundCodeValue",
    "EffectiveDateSourceName": "DateValue",
    "CurrencySourceName": "CurrencyValue",
    "CostSourceName": "CostValue",
    "QuantitySourceName": "UnitsValue",
    "TaxlotPriceSourceName": "PriceValue",
    "SubHoldingKeys": [],
    "Properties": []
  }
}
```

****Example B: Complex multi-asset transaction file mapping****

```json
{
  "CsvSeparator": "Pipe",
  "ColumnSpecs": [
    { "ColumnHeader": "TxnId", "ColumnReference": "TxnIdColumn" },
    { "ColumnHeader": "AssetType", "ColumnReference": "AssetTypeColumn" },
    { "ColumnHeader": "Identifier", "ColumnReference": "IdColumn" },
    { "ColumnHeader": "TxnType", "ColumnReference": "TxnTypeColumn" },
    { "ColumnHeader": "TradeDate", "ColumnReference": "TradeDateColumn" },
    { "ColumnHeader": "SettleDate", "ColumnReference": "SettleDateColumn" },
    { "ColumnHeader": "Units", "ColumnReference": "UnitsColumn" },
    { "ColumnHeader": "Price", "ColumnReference": "PriceColumn" },
    { "ColumnHeader": "CCY", "ColumnReference": "CcyColumn" },
    { "ColumnHeader": "Portfolio", "ColumnReference": "PortColumn" }
  ],
  "RowIdentificationSourceName": "TxnIdentifierValue",
  "ValueSources": [
    {
      "Name": "TxnIdentifierValue",
      "ColumnReferences": ["TxnIdColumn"]
    },
    {
      "Name": "AssetTypeValue",
      "ColumnReferences": ["AssetTypeColumn"]
    },
    {
      "Name": "IdValue",
      "ColumnReferences": ["IdColumn"]
    },
    {
      "Name": "TxnTypeRawValue",
      "ColumnReferences": ["TxnTypeColumn"]
    },
    {
      "Name": "TradeDateValue",
      "ColumnReferences": ["TradeDateColumn"]
    },
    {
      "Name": "SettleDateValue",
      "ColumnReferences": ["SettleDateColumn"]
    },
    {
      "Name": "UnitsValue",
      "ColumnReferences": ["UnitsColumn"]
    },
    {
      "Name": "PriceValue",
      "ColumnReferences": ["PriceColumn"]
    },
    {
      "Name": "CcyValue",
      "ColumnReferences": ["CcyColumn"]
    },
    {
      "Name": "PortValue",
      "ColumnReferences": ["PortColumn"]
    },
    {
      "Name": "StandardTxnTypeValue",
      "ColumnReferences": ["TxnTypeColumn"],
      "ValueMapName": "TxnTypeMap"
    },
    {
      "Name": "TotalConsiderationValue",
      "ColumnReferences": ["PriceColumn"],
      "MultiplierValueSourceName": "UnitsValue"
    },
    {
      "Name": "InstrumentTypeSelectorValue",
      "LogicalExpressions": [
        {
          "Condition": "AssetTypeValue eq \"EQ\"",
          "Result": "Equity"
        },
        {
          "Condition": "AssetTypeValue eq \"CASH\"",
          "Result": "SimpleInstrument"
        },
        {
          "Condition": "",
          "Result": "SimpleInstrument"
        }
      ]
    }
  ],
  "ValueMaps": [
    {
      "Name": "TxnTypeMap",
      "Map": {
        "B": "Buy",
        "S": "Sell",
        "DIV": "StockDividend"
      }
    }
  ],
  "DQChecks": [
    {
      "Name": "ValidTxnType",
      "Message": "Transaction type must be valid",
      "LogicalExpressions": [
        { "Condition": "TxnTypeRawValue InMapKeys TxnTypeMap", "Result": "Pass" },
        { "Condition": "", "Result": "Fail" }
      ]
    },
    {
      "Name": "RequirePrice",
      "Message": "Price must be greater than zero",
      "LogicalExpressions": [
        { "Condition": "PriceValue gt \"0\"", "Result": "Pass" },
        { "Condition": "", "Result": "Fail" }
      ]
    }
  ],
  "InstrumentsLoadControl": {
    "InstrumentTypeSelectorSourceName": "InstrumentTypeSelectorValue",
    "CreateMissingInstruments": true,
    "Identifiers": [
      {
        "IdentifierType": "ClientInternal",
        "IdentifierValueSourceName": "IdValue",
        "IsUnique": true
      }
    ],
    "InstrumentDefControls": [
      {
        "InstrumentType": "SimpleInstrument",
        "InstrumentNameSourceName": "IdValue",
        "DomesticCurrencySourceName": "CcyValue",
        "SimpleInstrumentTypeSourceName": "AssetTypeValue",
        "Properties": []
      },
      {
        "InstrumentType": "Equity",
        "InstrumentNameSourceName": "IdValue",
        "DomesticCurrencySourceName": "CcyValue",
        "Properties": []
      }
    ],
    "CommonProperties": []
  },
  "TransactionsLoadControl": {
    "FundCodeSourceName": "PortValue",
    "TransactionIdSourceName": "TxnIdentifierValue",
    "TransactionTypeSourceName": "StandardTxnTypeValue",
    "TransactionDateSourceName": "TradeDateValue",
    "SettlementDateSourceName": "SettleDateValue",
    "UnitsSourceName": "UnitsValue",
    "TotalConsiderationCurrencySourceName": "CcyValue",
    "TotalConsiderationAmountSourceName": "TotalConsiderationValue",
    "TransactionPriceSourceName": "PriceValue",
    "Properties": []
  }
}
```

### Best practice

#### Follow naming conventions

Always append suffixes to distinguish reference types:

- `ColumnReference`: Append `"Column"` → `"IsinColumn"`, `"PriceColumn"`
- `ValueSources` `Name`: Append `"Value"` → `"IsinValue"`, `"PriceValue"`

This makes it immediately clear what type of reference you're working with.

#### Validate early with DQ checks

Put critical validation at the beginning of your `DQChecks` array:

```json
"DQChecks": [
  {
    "Name": "RequireIdentifier",
    "Message": "Instrument identifier is required",
    "LogicalExpressions": [
      { "Condition": "IsinValue neq \"\"", "Result": "Pass" },
      { "Condition": "", "Result": "Fail" }
    ]
  },
  {
    "Name": "ValidateBeforeMapping",
    "Message": "Transaction type must be valid",
    "LogicalExpressions": [
      { "Condition": "TxnTypeValue InMapKeys TransactionTypeMap", "Result": "Pass" },
      { "Condition": "", "Result": "Fail" }
    ]
  }
]
```

#### Reuse `ValueSources`

Define `ValueSources` once and reference them multiple times:

```json
"ValueSources": [
  {
    "Name": "IsinValue",
    "ColumnReferences": ["IsinColumn"]
  }
]
```

Then use `"IsinValue"` in:

- DQChecks
- Conditional `ValueSources`
- Load controls
- Other `ValueSources`

#### Use `ValueMaps` for transformations

Always use `ValueMaps` instead of multiple conditional checks:

✅ **Do:**

```json
"ValueMaps": [
  {
    "Name": "TransactionTypeMap",
    "Map": {
      "B": "Buy",
      "S": "Sell",
      "D": "Dividend"
    }
  }
],
"ValueSources": [
  {
    "Name": "StandardTxnTypeValue",
    "ColumnReferences": ["TxnTypeColumn"],
    "ValueMapName": "TransactionTypeMap"
  }
]
```

❌ **Don’t:**

```json
"ValueSources": [
  {
    "Name": "StandardTxnTypeValue",
    "LogicalExpressions": [
      { "Condition": "TxnTypeRawValue eq \"B\"", "Result": "Buy" },
      { "Condition": "TxnTypeRawValue eq \"S\"", "Result": "Sell" },
      { "Condition": "TxnTypeRawValue eq \"D\"", "Result": "Dividend" }
    ]
  }
]
```

#### Always include default cases

Every conditional `ValueSource` should have a default case (empty condition):

```json
{
  "Name": "InstrumentTypeSelectorValue",
  "LogicalExpressions": [
    {
      "Condition": "AssetClassValue eq \"Equity\"",
      "Result": "Equity"
    },
    {
      "Condition": "",
      "Result": "SimpleInstrument"
    }
  ]
}
```

This prevents errors when encountering unexpected values.

#### Test incrementally

Build your mapping file in stages:

1. Start with basic structure: `ColumnSpecs` and simple `ValueSources`
2. Add DQ checks: Validate required fields
3. Add one `LoadControl`: Test with a small file
4. Add complexity: conditional `ValueSources`, `ValueMaps`, additional `LoadControls`
5. Add advanced features: `Filters`, [Aggregators](/v1/docs/record-capture-framework#reference-aggregators), `sub-holding keys`

#### Use descriptive names

Make names self-documenting:

✅ **Do:**

- `"InstrumentTypeSelectorValue"`
- `"SettlementDaysCalculatorValue"`
- `"ValidTransactionType"` (DQ check)

❌ **Don’t:**

- `"TypeValue"`
- `"Value1"`
- `"Check1"`

#### Document complex logic

For sophisticated conditional `ValueSources`, add comments in your source control commit messages explaining the business logic.

### Common snippets

#### Multi-identifier fallback

Use the first available identifier from multiple options:

```json
{
  "Name": "BestIdentifierValue",
  "ColumnReferences": ["IsinColumn", "SedolColumn", "ClientIdColumn"],
  "ValueConversion": "FirstNonEmpty"
}
```

#### Conditional property setting

Only set a property when certain conditions are met:

```json
{
  "Name": "MaturityDateValue",
  "ColumnReferences": ["MaturityColumn"]
},
{
  "Name": "MaturityPropertyValue",
  "LogicalExpressions": [
    {
      "Condition": "AssetClassValue eq \"Bond\"",
      "Result": "MaturityDateValue"
    },
    {
      "Condition": "",
      "Result": ""
    }
  ]
}
```

Then reference `"MaturityPropertyValue"` in properties - it returns empty string for non-bonds.

#### Composite key for row identification

Create a unique identifier by combining multiple fields:

```json
{
  "Name": "RowIdValue",
  "ColumnReferences": ["FundColumn", "IsinColumn", "TradeDateColumn"],
  "ValueConversion": "Concatenate",
  "ConcatenationSeparator": "|"
}
```

Use this as your `RowIdentificationSourceName` for clear logging.

#### Nested conditional logic

Build reusable logic blocks and compose them:

```json
{
  "Name": "USSettlementDaysValue",
  "LogicalExpressions": [
    { "Condition": "AssetClassValue eq \"Equity\"", "Result": "3" },
    { "Condition": "", "Result": "1" }
  ]
},
{
  "Name": "UKSettlementDaysValue",
  "LogicalExpressions": [
    { "Condition": "AssetClassValue eq \"Equity\"", "Result": "2" },
    { "Condition": "", "Result": "1" }
  ]
},
{
  "Name": "SettlementDaysValue",
  "LogicalExpressions": [
    { "Condition": "CountryValue eq \"US\"", "Result": "USSettlementDaysValue" },
    { "Condition": "CountryValue eq \"UK\"", "Result": "UKSettlementDaysValue" },
    { "Condition": "", "Result": "1" }
  ]
}
```

#### Validate before mapping

Always validate that a value exists in a `ValueMap` before applying it:

```json
"DQChecks": [
  {
    "Name": "ValidateTransactionType",
    "Message": "Transaction type must be valid",
    "LogicalExpressions": [
      { "Condition": "RawTxnTypeValue InMapKeys TransactionTypeMap", "Result": "Pass" },
      { "Condition": "", "Result": "Fail" }
    ]
  }
],
"ValueSources": [
  {
    "Name": "StandardTxnTypeValue",
    "ColumnReferences": ["TxnTypeColumn"],
    "ValueMapName": "TransactionTypeMap"
  }
]
```

#### Conditional multiplier for scaling

Use conditional `ValueSources` to dynamically scale values:

```json
{
  "Name": "ContractMultiplierValue",
  "LogicalExpressions": [
    { "Condition": "AssetTypeValue eq \"Future\"", "Result": "100" },
    { "Condition": "AssetTypeValue eq \"Option\"", "Result": "1000" },
    { "Condition": "", "Result": "1" }
  ]
},
{
  "Name": "ActualQuantityValue",
  "ColumnReferences": ["RawQuantityColumn"],
  "MultiplierValueSourceName": "ContractMultiplierValue"
}
```

### Troubleshooting tips

****Row failing DQ check unexpectedly****

- Check the order of `LogicalExpressions` - first match wins
- Verify ValueSource names are spelled correctly (case-sensitive)
- Check for empty string vs missing value handling

****Instrument not matched****

- Verify at least one `Identifier` has `IsUnique: true`
- Check identifier values are being extracted correctly
- Confirm instrument exists in LUSID with that identifier

****Conditional not working****

- Cannot mix `AND`/`OR` in single condition
- Verify `ValueSource` names exist and are spelled correctly
- Check quotes around absolute values in conditions

****Property not set****

- Verify property key format is correct (`Domain/Scope/Code`)
- Check `LusidPropertyDataType` for numeric properties (use `number`)
- For date properties, use `date` and provide `DateFormats` array
- Confirm `ValueSource` returns non-empty value

**`ValueMap` **lookup failing****

- Validate value is in map keys before applying with `DQChecks`
- Verify key spelling matches exactly (case-sensitive)
- Ensure all possible source values are mapped

For bitemporal data, the **effectiveAt** timestamp reflects the point in time at which a data item becomes valid in LUSID from a business perspective. You can specify the field when you make a read or update request to LUSID, indicating that you want to retrieve or update data that is effective at the specified date.