--- title: "Lusid.Relationship.Writer" slug: "lusidrelationshipwriter" updated: 2026-01-26T16:45:44Z published: 2026-01-26T16:45:44Z canonical: "support.lusid.com/lusidrelationshipwriter" --- > ## 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. # Lusid.Relationship.Writer | **Type** | **Read/write** | **Author** | **Availability** | | --- | --- | --- | --- | | [Data provider](/v1/docs/what-are-a-data-provider-and-a-direct-provider) | Write | Finbourne | Provided with LUSID | The `Lusid.Relationship.Writer` provider enables you to write a [Luminesce SQL query](/v1/docs/understanding-the-luminesce-sql-query-syntax) that either upserts or deletes relationships between [entities that support relationships](/v1/docs/creating-relationships-between-certain-types-of-entity), for example a person and a portfolio. You must construct a valid table of data to write, one relationship per record. `Lusid.Relationship.Writer` lists the fields (columns) available to populate with values for each record, and has a set of parameters to help you construct a valid table. Your query can use the `WriteAction` field to perform one of the following operations: - Upsert a relationship; that is, create one if it does not exist, and update it if it does. This is the default operation if you omit `WriteAction`. - Delete a relationship. **See also:** [Lusid.Relationship](/v1/docs/lusidrelationship) ## Basic usage ```sql @table_of_data = ; select * from Lusid.Relationship.Writer where toWrite = @table_of_data; ``` ## Query parameters `Lusid.Relationship.Writer` has parameters that help you construct a valid table of data to write. > [!NOTE] > **Note**: The `toWrite` parameter is mandatory and used to actually write the table of data into LUSID. To create a relationship, you must unambiguously identify the two entities at either end. Note that identifiers for different entity types have different components: | **Entity type** | **Components of identifier** | | --- | --- | | `LegalEntity`, `Person` | `idTypeScope`, `idTypeCode`, `code` | | `Instrument` | `identifierType`, `identifierValue`, `scope` (if not in the `default` scope) | | Custom entity | `identifierType`, `identifierScope`, `identifierValue` | | All other types, including `Portfolio` and `PortfolioGroup` | `scope`, `code` | For more information, consult [this table of entities and their identifiers](/v1/docs/what-is-an-entity). Use the following data fields in the `select` statement for `toWrite` to identify the two entities at either end and provide other mandatory information, such as the relationship type: | **Data field(s)** | **Status** | **Explanation** | | --- | --- | --- | | `EntityType`, `RelatedEntityType` | Required | The types of the two entities. Allowed values are the entity types supporting relationships, for example `Portfolio`, `PortfolioGroup`, `LegalEntity`, `Person`, `Instrument` or the name of a [custom entity](/v1/docs/extending-lusids-data-model-using-custom-entities) preceded by `~`, for example `~Office`. Both entities can be of the same type, for example to connect two people. | | `EntityScope`, `RelatedEntityScope` | Required for all except `Instrument`. If omitted for `Instrument`, uses the `default` instrument scope. | Part of the identifier for either entity. The origin of this value depends on the entity type: \| **Entity type** \| **Origin of field value** \| \| --- \| --- \| \| `LegalEntity`, `Person` \| `idTypeScope` \| \| `Instrument` (optional) \| `scope` \| \| Custom entity \| `identifierScope` \| \| All other types, including `Portfolio` and `PortfolioGroup` \| `scope` \| | | **Entity type** | **Origin of field value** | | `LegalEntity`, `Person` | `idTypeScope` | | `Instrument` (optional) | `scope` | | Custom entity | `identifierScope` | | All other types, including `Portfolio` and `PortfolioGroup` | `scope` | | `EntityCode`, `RelatedEntityCode` | Required | Part of the identifier for either entity. The origin of this value depends on the entity type: \| **Entity type** \| **Origin of field value** \| \| --- \| --- \| \| `LegalEntity`, `Person` \| `idTypeCode` \| \| `Instrument` \| `identifierType` \| \| Custom entity \| `identifierType` \| \| All other types, including `Portfolio` and `PortfolioGroup` \| `code` \| | | **Entity type** | **Origin of field value** | | `LegalEntity`, `Person` | `idTypeCode` | | `Instrument` | `identifierType` | | Custom entity | `identifierType` | | All other types, including `Portfolio` and `PortfolioGroup` | `code` | | `EntityValue`, `RelatedEntityValue` | Required for `LegalEntity`, `Person`, `Instrument` and custom entities. Do not specify for other types. | Part of the identifier for either entity. The origin of this value depends on the entity type: \| **Entity type** \| **Origin of field value** \| \| --- \| --- \| \| `LegalEntity`, `Person` \| `code` \| \| `Instrument` \| `identifierValue` \| \| Custom entity \| `identifierValue` \| \| All other types, including `Portfolio` and `PortfolioGroup` \| N/A. Do not specify this field. \| | | **Entity type** | **Origin of field value** | | `LegalEntity`, `Person` | `code` | | `Instrument` | `identifierValue` | | Custom entity | `identifierValue` | | All other types, including `Portfolio` and `PortfolioGroup` | N/A. Do not specify this field. | | `RelationshipScope`, `RelationshipCode` | Required | The `scope` and `code` of the [relationship type](/v1/docs/creating-relationships-between-certain-types-of-entity#creating-a-relationship-type) to which the relationship belongs. | To list all available parameters, their data types, default values, and an explanation for each, run the following query using a [suitable tool](/v1/docs/what-tools-are-available-to-write-luminesce-queries): ```sql select FieldName, DataType, ParamDefaultValue, Description from Sys.Field where TableName = 'Lusid.Relationship.Writer' and FieldType = 'Parameter'; ``` ## Data fields `Lusid.Relationship.Writer` lists the fields you can populate in your table of data to write. > [!NOTE] > **Note**: Some of these fields are mandatory to specify in your query; see the section above. To list all available fields, their data types, whether fields are considered 'main', and an explanation for each, run the following query using a [suitable tool](/v1/docs/what-tools-are-available-to-write-luminesce-queries): ```sql select FieldName, DataType, IsMain, IsPrimaryKey, SampleValues, Description from Sys.Field where TableName = 'Lusid.Relationship.Writer' and FieldType = 'Column'; ``` ## Write errors We recommend examining the results of every write query using one or more of the `WriteError`, `WriteErrorCode` and `WriteErrorDetail` fields. For each record in the table of data to write, `Lusid.Relationship.Writer` returns an error code. If the operation is successful, the error code is `0`. If unsuccessful, a positive error code and explanation help you discover why LUSID considers the operation invalid. ## Examples > [!NOTE] > **Note:** For more example Luminesce SQL queries, visit our [Github repo](https://github.com/finbourne/luminesce-examples/tree/master/examples). ### Example 1: Create a relationship between a portfolio and a person You can create a relationship by supplying all mandatory fields for both entities and using `'upsert' as WriteAction`. Note that if a relationship has either a portfolio or a portfolio group at either end (or at both ends), then you must specify the `EffectiveFrom` field with a date that is later than the creation date of these [time-variant entities](/v1/docs/what-is-bitemporal-data#understanding-perpetual-and-time-variant-data-items). This is not required if the relationship only connects instrument, person, custom or legal entities, which are perpetual, although you can optionally specify this field if the [relationship itself is time-variant](/v1/docs/creating-relationships-between-certain-types-of-entity#creating-a-relationship-type). ```sql @table_of_data = select  'PortfolioManagementTeam' as RelationshipScope,  'Managers' as RelationshipCode,  'Portfolio' as EntityType,  'US-Investments' as EntityScope,  'US-Equities' as EntityCode,  #2022-06-01# as EffectiveFrom,  'Person' as RelatedEntityType,  'PortfolioManagers' as RelatedEntityScope,  'ManagerId' as RelatedEntityCode,  'PortMan1' as RelatedEntityValue,  'upsert' as WriteAction; select * from Lusid.Relationship.Writer  where ToWrite = @table_of_data; ``` ### Example 2: Update a time-variant relationship effective from/until a certain date You can update the `EffectiveFrom` and/or `EffectiveUntil` dates of a time-variant relationship by supplying all mandatory fields for both entities and using `'upsert' as WriteAction`. Note you cannot update a perpetual relationship. ```sql @table_of_data = select  'PortfolioManagementTeam' as RelationshipScope,  'Managers' as RelationshipCode,  'Portfolio' as EntityType,  'US-Investments' as EntityScope,  'US-Equities' as EntityCode,  #2023-01-01# as EffectiveFrom,  'Person' as RelatedEntityType,  'PortfolioManagers' as RelatedEntityScope,  'ManagerId' as RelatedEntityCode,  'PortMan1' as RelatedEntityValue,  'upsert' as WriteAction; select * from Lusid.Relationship.Writer   where ToWrite = @table_of_data; ``` ### Example 3: Delete a relationship You can delete a relationship by specifying all mandatory fields for both entities and using `'delete' as WriteAction`. ```sql @table_of_data = select  'PortfolioManagementTeam' as RelationshipScope,  'Managers' as RelationshipCode,  'Portfolio' as EntityType,  'US-Investments' as EntityScope,  'US-Equities' as EntityCode,  ​​​​​​​'Person' as RelatedEntityType,  'PortfolioManagers' as RelatedEntityScope,  'ManagerId' as RelatedEntityCode,  'PortMan1' as RelatedEntityValue,  'delete' as WriteAction; select * from Lusid.Relationship.Writer  where ToWrite = @table_of_data; ``` ### Example 4: Create multiple relationships at the same time You can create all your relationships at the same time in one Luminesce query. In this example, the query defaults to an upsert operation as no explicit `WriteAction` is specified. ```sql @vals = values ('PortfolioManagementTeam', 'Managers', 'Portfolio', 'US-Investments', 'US-Equities', Null, 'Person', 'PortfolioManagers', 'ManagerId', 'PortMan1'), ('InstrumentIssuer', 'IssuerLEI', 'Instrument', 'Ibor', 'LusidInstrumentId', 'LUID_00003D93', 'LegalEntity', 'InternationalBanks', 'BankId', 'AcmeInc') ; @table_of_data = select  column1 as RelationshipScope,  column2 as RelationshipCode,  column3 as EntityType,  column4 as EntityScope,  column5 as EntityCode,  column6 as EntityValue,  column7 as RelatedEntityType,  column8 as RelatedEntityScope,  column9 as RelatedEntityCode,  column10 as RelatedEntityValue  from @vals; select * from Lusid.Relationship.Writer  where ToWrite = @table_of_data; ```