Provides the ability to join exactly two datasets using a defined set of common fields. The function supports both the LEFT and INNER join operations.
Version 1
HTTP RequestPOST /ado/v1/Join
Header
| Parameter | Description |
|---|---|
| Ocp-Apim-Subscription-Key | The subscription key you received when you purchased a plan. |
Request Body
Mandatory
| Parameter | Type | Description |
|---|---|---|
| joinType | string | Enumeration with one of two values, Left or Inner.A Left join will return all records from the first dataset and only those from the second that match the join criteria.An Inner join will return all records that match the join condition across both datasets. |
| joinFields | string[] | An array of strings which are the name of the fields to join on. This property does not support the dynamic property selection syntax. |
| fields | string[] | The fields to be included in the resulting output. Supports the dynamic property selection syntax. Dynamic Property Selections Please refer to the Notes section to get a better understanding of how each property will be represented in the output if a requested property exists with the same name in both datasets. |
| data | object | Object containing exactly two properties that represent the datasets to join. |
Optional
| Parameter | Type | Description |
|---|---|---|
| forceFullyQualifiedFieldNames | boolean | true will ensure all field names in the resulting output are prefixed with the dataset name.false will only result in a field name being fully qualified if it exists in the resulting dataset more than once. |
Common
Further Documentation: Common Parameters
| Parameter | Type | Description |
|---|---|---|
| filter | String | A fully functioning SQL based WHERE statement that will filter the outgoing dataset to the records it determines as being included. |
| sortOrder | Object | Sorts the resulting dataset by the criteria provided. The object should contain property names that represent properties contained within the resulting dataset with a value corresponding to the desired sort direction (i.e. ASC or DESC). |
| schema | Object | You can use this parameter to override the inferred schema for properties in the incoming dataset(s). A field will be inferred unless specific explicitly within this object. |
| advancedOptions | Object | Is an object with the following properties. cultureName (String) The specified culture determines the behaviour for aspects related to formatting numeric values and dates. Is extremely important when converting strings to dates, e.g. 05/03/2022 will be treated differently between locales. For more information on the accepted values for this property, please consult the documentation from Microsoft … https://docs.microsoft.com/en-us/dotnet/api/system.globalization.cultureinfo?view=net-6.0#culture-names-and-identifiers isBoolean (string[]) A list of strings that are the names of all properties in the resulting dataset that should be treated as boolean values. By default, the framework treats boolean values as 1 (true) or 0 (false). In order to differentiate the integer as a boolean, the property in question needs to be called out explicitly in this list. |
Notes
Because the same properties have the ability to (and will due to the join fields) exist in both datasets, the resulting output will look very different if not controlled through the forceFullyQualifiedFieldNames parameter.
It is advised to use this parameter explicitly unless you can guarantee that there will be no changes to the schema at a future date.
Examples
Left
Inner
Force FQ Field Names
This example demonstrates two datasets, the first being header records and the second being the associated line items.
Request
{
"joinType": "Left",
"joinFields": [
"DocumentNumber",
"CompanyCode",
"FiscalYear"
],
"fields": [
"+FieldName(%)"
],
"forceFullyQualifiedFieldNames": false,
"data": {
"Header": [
{
"DocumentNumber": "00001",
"CompanyCode": "1000",
"FiscalYear": "2022",
"Text": "This is a test document (00001)",
"Date": "25-May-2022"
},
{
"DocumentNumber": "00002",
"CompanyCode": "1000",
"FiscalYear": "2022",
"Text": "Join on this document (00002)",
"Date": "11-Aug-2022"
},
{
"DocumentNumber": "00001",
"CompanyCode": "2000",
"FiscalYear": "2023",
"Text": "This is another test document (00001)",
"Date": "17-Oct-2023"
},
{
"DocumentNumber": "00002",
"CompanyCode": "2000",
"FiscalYear": "2023",
"Text": "This is another test document (00002)",
"Date": "24-Oct-2023"
}
],
"LineItems": [
{
"DocumentNumber": "00001",
"CompanyCode": "1000",
"FiscalYear": "2022",
"ItemNo": "001",
"GLAccount": "602000",
"CostCenter": "10001000",
"Text": "Item Text 1",
"Amount": 3123.32
},
{
"DocumentNumber": "00001",
"CompanyCode": "1000",
"FiscalYear": "2022",
"ItemNo": "002",
"GLAccount": "320000",
"Text": "Item Text 2",
"Amount": -3123.32
},
{
"DocumentNumber": "00001",
"CompanyCode": "2000",
"FiscalYear": "2023",
"ItemNo": "001",
"GLAccount": "6182000",
"CostCenter": "10002000",
"Text": "Item Text 3",
"Amount": 432.43
},
{
"DocumentNumber": "00001",
"CompanyCode": "2000",
"FiscalYear": "2023",
"ItemNo": "002",
"GLAccount": "120000",
"Text": "Item Text 4",
"Amount": -432.43
},
{
"DocumentNumber": "00001",
"CompanyCode": "1000",
"FiscalYear": "2022",
"ItemNo": "001",
"GLAccount": "602000",
"CostCenter": "10001000",
"Text": "Item Text 5",
"Amount": 100.00
},
{
"DocumentNumber": "00001",
"CompanyCode": "1000",
"FiscalYear": "2022",
"ItemNo": "002",
"GLAccount": "320000",
"Text": "Item Text 6",
"Amount": -110.00
},
{
"DocumentNumber": "00002",
"CompanyCode": "1000",
"FiscalYear": "2022",
"ItemNo": "003",
"GLAccount": "270000",
"Text": "Item Text 7",
"Amount": 10.00
}
]
}
}
Code language: JSON / JSON with Comments (json)Response
[
{
"Header.DocumentNumber": "00001",
"Header.CompanyCode": "1000",
"Header.FiscalYear": "2022",
"Header.Text": "This is a test document (00001)",
"Date": "25-May-2022",
"LineItems.DocumentNumber": "00001",
"LineItems.CompanyCode": "1000",
"LineItems.FiscalYear": "2022",
"ItemNo": "001",
"GLAccount": "602000",
"CostCenter": "10001000",
"LineItems.Text": "Item Text 1",
"Amount": 3123.32
},
{
"Header.DocumentNumber": "00001",
"Header.CompanyCode": "1000",
"Header.FiscalYear": "2022",
"Header.Text": "This is a test document (00001)",
"Date": "25-May-2022",
"LineItems.DocumentNumber": "00001",
"LineItems.CompanyCode": "1000",
"LineItems.FiscalYear": "2022",
"ItemNo": "001",
"GLAccount": "602000",
"CostCenter": "10001000",
"LineItems.Text": "Item Text 5",
"Amount": 100.0
},
{
"Header.DocumentNumber": "00001",
"Header.CompanyCode": "1000",
"Header.FiscalYear": "2022",
"Header.Text": "This is a test document (00001)",
"Date": "25-May-2022",
"LineItems.DocumentNumber": "00001",
"LineItems.CompanyCode": "1000",
"LineItems.FiscalYear": "2022",
"ItemNo": "002",
"GLAccount": "320000",
"CostCenter": null,
"LineItems.Text": "Item Text 2",
"Amount": -3123.32
},
{
"Header.DocumentNumber": "00001",
"Header.CompanyCode": "1000",
"Header.FiscalYear": "2022",
"Header.Text": "This is a test document (00001)",
"Date": "25-May-2022",
"LineItems.DocumentNumber": "00001",
"LineItems.CompanyCode": "1000",
"LineItems.FiscalYear": "2022",
"ItemNo": "002",
"GLAccount": "320000",
"CostCenter": null,
"LineItems.Text": "Item Text 6",
"Amount": -110.0
},
{
"Header.DocumentNumber": "00002",
"Header.CompanyCode": "1000",
"Header.FiscalYear": "2022",
"Header.Text": "Join on this document (00002)",
"Date": "11-Aug-2022",
"LineItems.DocumentNumber": "00002",
"LineItems.CompanyCode": "1000",
"LineItems.FiscalYear": "2022",
"ItemNo": "003",
"GLAccount": "270000",
"CostCenter": null,
"LineItems.Text": "Item Text 7",
"Amount": 10.0
},
{
"Header.DocumentNumber": "00001",
"Header.CompanyCode": "2000",
"Header.FiscalYear": "2023",
"Header.Text": "This is another test document (00001)",
"Date": "17-Oct-2023",
"LineItems.DocumentNumber": "00001",
"LineItems.CompanyCode": "2000",
"LineItems.FiscalYear": "2023",
"ItemNo": "001",
"GLAccount": "6182000",
"CostCenter": "10002000",
"LineItems.Text": "Item Text 3",
"Amount": 432.43
},
{
"Header.DocumentNumber": "00001",
"Header.CompanyCode": "2000",
"Header.FiscalYear": "2023",
"Header.Text": "This is another test document (00001)",
"Date": "17-Oct-2023",
"LineItems.DocumentNumber": "00001",
"LineItems.CompanyCode": "2000",
"LineItems.FiscalYear": "2023",
"ItemNo": "002",
"GLAccount": "120000",
"CostCenter": null,
"LineItems.Text": "Item Text 4",
"Amount": -432.43
},
{
"Header.DocumentNumber": "00002",
"Header.CompanyCode": "2000",
"Header.FiscalYear": "2023",
"Header.Text": "This is another test document (00002)",
"Date": "24-Oct-2023",
"LineItems.DocumentNumber": null,
"LineItems.CompanyCode": null,
"LineItems.FiscalYear": null,
"ItemNo": null,
"GLAccount": null,
"CostCenter": null,
"LineItems.Text": null,
"Amount": null
}
]
Code language: JSON / JSON with Comments (json)This example demonstrates two datasets, the first being header records and the second being the associated line items.
Request
{
"joinType": "Inner",
"joinFields": [
"DocumentNumber",
"CompanyCode",
"FiscalYear"
],
"fields": [
"+FieldName(%)"
],
"forceFullyQualifiedFieldNames": false,
"data": {
"Header": [
{
"DocumentNumber": "00001",
"CompanyCode": "1000",
"FiscalYear": "2022",
"Text": "This is a test document (00001)",
"Date": "25-May-2022"
},
{
"DocumentNumber": "00002",
"CompanyCode": "1000",
"FiscalYear": "2022",
"Text": "Join on this document (00002)",
"Date": "11-Aug-2022"
},
{
"DocumentNumber": "00001",
"CompanyCode": "2000",
"FiscalYear": "2023",
"Text": "This is another test document (00001)",
"Date": "17-Oct-2023"
},
{
"DocumentNumber": "00002",
"CompanyCode": "2000",
"FiscalYear": "2023",
"Text": "This is another test document (00002)",
"Date": "24-Oct-2023"
}
],
"LineItems": [
{
"DocumentNumber": "00001",
"CompanyCode": "1000",
"FiscalYear": "2022",
"ItemNo": "001",
"GLAccount": "602000",
"CostCenter": "10001000",
"Text": "Item Text 1",
"Amount": 3123.32
},
{
"DocumentNumber": "00001",
"CompanyCode": "1000",
"FiscalYear": "2022",
"ItemNo": "002",
"GLAccount": "320000",
"Text": "Item Text 2",
"Amount": -3123.32
},
{
"DocumentNumber": "00001",
"CompanyCode": "2000",
"FiscalYear": "2023",
"ItemNo": "001",
"GLAccount": "6182000",
"CostCenter": "10002000",
"Text": "Item Text 3",
"Amount": 432.43
},
{
"DocumentNumber": "00001",
"CompanyCode": "2000",
"FiscalYear": "2023",
"ItemNo": "002",
"GLAccount": "120000",
"Text": "Item Text 4",
"Amount": -432.43
},
{
"DocumentNumber": "00001",
"CompanyCode": "1000",
"FiscalYear": "2022",
"ItemNo": "001",
"GLAccount": "602000",
"CostCenter": "10001000",
"Text": "Item Text 5",
"Amount": 100.00
},
{
"DocumentNumber": "00001",
"CompanyCode": "1000",
"FiscalYear": "2022",
"ItemNo": "002",
"GLAccount": "320000",
"Text": "Item Text 6",
"Amount": -110.00
},
{
"DocumentNumber": "00002",
"CompanyCode": "1000",
"FiscalYear": "2022",
"ItemNo": "003",
"GLAccount": "270000",
"Text": "Item Text 7",
"Amount": 10.00
}
]
}
}
Code language: JSON / JSON with Comments (json)Response
[
{
"Header.DocumentNumber": "00001",
"Header.CompanyCode": "1000",
"Header.FiscalYear": "2022",
"Header.Text": "This is a test document (00001)",
"Date": "25-May-2022",
"LineItems.DocumentNumber": "00001",
"LineItems.CompanyCode": "1000",
"LineItems.FiscalYear": "2022",
"ItemNo": "001",
"GLAccount": "602000",
"CostCenter": "10001000",
"LineItems.Text": "Item Text 1",
"Amount": 3123.32
},
{
"Header.DocumentNumber": "00001",
"Header.CompanyCode": "1000",
"Header.FiscalYear": "2022",
"Header.Text": "This is a test document (00001)",
"Date": "25-May-2022",
"LineItems.DocumentNumber": "00001",
"LineItems.CompanyCode": "1000",
"LineItems.FiscalYear": "2022",
"ItemNo": "001",
"GLAccount": "602000",
"CostCenter": "10001000",
"LineItems.Text": "Item Text 5",
"Amount": 100.0
},
{
"Header.DocumentNumber": "00001",
"Header.CompanyCode": "1000",
"Header.FiscalYear": "2022",
"Header.Text": "This is a test document (00001)",
"Date": "25-May-2022",
"LineItems.DocumentNumber": "00001",
"LineItems.CompanyCode": "1000",
"LineItems.FiscalYear": "2022",
"ItemNo": "002",
"GLAccount": "320000",
"CostCenter": null,
"LineItems.Text": "Item Text 2",
"Amount": -3123.32
},
{
"Header.DocumentNumber": "00001",
"Header.CompanyCode": "1000",
"Header.FiscalYear": "2022",
"Header.Text": "This is a test document (00001)",
"Date": "25-May-2022",
"LineItems.DocumentNumber": "00001",
"LineItems.CompanyCode": "1000",
"LineItems.FiscalYear": "2022",
"ItemNo": "002",
"GLAccount": "320000",
"CostCenter": null,
"LineItems.Text": "Item Text 6",
"Amount": -110.0
},
{
"Header.DocumentNumber": "00002",
"Header.CompanyCode": "1000",
"Header.FiscalYear": "2022",
"Header.Text": "Join on this document (00002)",
"Date": "11-Aug-2022",
"LineItems.DocumentNumber": "00002",
"LineItems.CompanyCode": "1000",
"LineItems.FiscalYear": "2022",
"ItemNo": "003",
"GLAccount": "270000",
"CostCenter": null,
"LineItems.Text": "Item Text 7",
"Amount": 10.0
},
{
"Header.DocumentNumber": "00001",
"Header.CompanyCode": "2000",
"Header.FiscalYear": "2023",
"Header.Text": "This is another test document (00001)",
"Date": "17-Oct-2023",
"LineItems.DocumentNumber": "00001",
"LineItems.CompanyCode": "2000",
"LineItems.FiscalYear": "2023",
"ItemNo": "001",
"GLAccount": "6182000",
"CostCenter": "10002000",
"LineItems.Text": "Item Text 3",
"Amount": 432.43
},
{
"Header.DocumentNumber": "00001",
"Header.CompanyCode": "2000",
"Header.FiscalYear": "2023",
"Header.Text": "This is another test document (00001)",
"Date": "17-Oct-2023",
"LineItems.DocumentNumber": "00001",
"LineItems.CompanyCode": "2000",
"LineItems.FiscalYear": "2023",
"ItemNo": "002",
"GLAccount": "120000",
"CostCenter": null,
"LineItems.Text": "Item Text 4",
"Amount": -432.43
}
]
Code language: JSON / JSON with Comments (json)When forceFullyQualifiedFieldNames is set to true, there is no ambiguity about where the field originated from in the resulting dataset.
Request
{
"joinType": "Left",
"joinFields": [
"DocumentNumber",
"CompanyCode",
"FiscalYear"
],
"fields": [
"+FieldName(%)"
],
"forceFullyQualifiedFieldNames": true,
"data": {
"Header": [
{
"DocumentNumber": "00001",
"CompanyCode": "1000",
"FiscalYear": "2022",
"Text": "This is a test document (00001)",
"Date": "25-May-2022"
},
{
"DocumentNumber": "00002",
"CompanyCode": "1000",
"FiscalYear": "2022",
"Text": "Join on this document (00002)",
"Date": "11-Aug-2022"
},
{
"DocumentNumber": "00001",
"CompanyCode": "2000",
"FiscalYear": "2023",
"Text": "This is another test document (00001)",
"Date": "17-Oct-2023"
},
{
"DocumentNumber": "00002",
"CompanyCode": "2000",
"FiscalYear": "2023",
"Text": "This is another test document (00002)",
"Date": "24-Oct-2023"
}
],
"LineItems": [
{
"DocumentNumber": "00001",
"CompanyCode": "1000",
"FiscalYear": "2022",
"ItemNo": "001",
"GLAccount": "602000",
"CostCenter": "10001000",
"Text": "Item Text 1",
"Amount": 3123.32
},
{
"DocumentNumber": "00001",
"CompanyCode": "1000",
"FiscalYear": "2022",
"ItemNo": "002",
"GLAccount": "320000",
"Text": "Item Text 2",
"Amount": -3123.32
},
{
"DocumentNumber": "00001",
"CompanyCode": "2000",
"FiscalYear": "2023",
"ItemNo": "001",
"GLAccount": "6182000",
"CostCenter": "10002000",
"Text": "Item Text 3",
"Amount": 432.43
},
{
"DocumentNumber": "00001",
"CompanyCode": "2000",
"FiscalYear": "2023",
"ItemNo": "002",
"GLAccount": "120000",
"Text": "Item Text 4",
"Amount": -432.43
},
{
"DocumentNumber": "00001",
"CompanyCode": "1000",
"FiscalYear": "2022",
"ItemNo": "001",
"GLAccount": "602000",
"CostCenter": "10001000",
"Text": "Item Text 5",
"Amount": 100.00
},
{
"DocumentNumber": "00001",
"CompanyCode": "1000",
"FiscalYear": "2022",
"ItemNo": "002",
"GLAccount": "320000",
"Text": "Item Text 6",
"Amount": -110.00
},
{
"DocumentNumber": "00002",
"CompanyCode": "1000",
"FiscalYear": "2022",
"ItemNo": "003",
"GLAccount": "270000",
"Text": "Item Text 7",
"Amount": 10.00
}
]
}
}
Code language: JSON / JSON with Comments (json)Response
[
{
"Header.DocumentNumber": "00001",
"Header.CompanyCode": "1000",
"Header.FiscalYear": "2022",
"Header.Text": "This is a test document (00001)",
"Header.Date": "25-May-2022",
"LineItems.DocumentNumber": "00001",
"LineItems.CompanyCode": "1000",
"LineItems.FiscalYear": "2022",
"LineItems.ItemNo": "001",
"LineItems.GLAccount": "602000",
"LineItems.CostCenter": "10001000",
"LineItems.Text": "Item Text 1",
"LineItems.Amount": 3123.32
},
{
"Header.DocumentNumber": "00001",
"Header.CompanyCode": "1000",
"Header.FiscalYear": "2022",
"Header.Text": "This is a test document (00001)",
"Header.Date": "25-May-2022",
"LineItems.DocumentNumber": "00001",
"LineItems.CompanyCode": "1000",
"LineItems.FiscalYear": "2022",
"LineItems.ItemNo": "001",
"LineItems.GLAccount": "602000",
"LineItems.CostCenter": "10001000",
"LineItems.Text": "Item Text 5",
"LineItems.Amount": 100.0
},
{
"Header.DocumentNumber": "00001",
"Header.CompanyCode": "1000",
"Header.FiscalYear": "2022",
"Header.Text": "This is a test document (00001)",
"Header.Date": "25-May-2022",
"LineItems.DocumentNumber": "00001",
"LineItems.CompanyCode": "1000",
"LineItems.FiscalYear": "2022",
"LineItems.ItemNo": "002",
"LineItems.GLAccount": "320000",
"LineItems.CostCenter": null,
"LineItems.Text": "Item Text 2",
"LineItems.Amount": -3123.32
},
{
"Header.DocumentNumber": "00001",
"Header.CompanyCode": "1000",
"Header.FiscalYear": "2022",
"Header.Text": "This is a test document (00001)",
"Header.Date": "25-May-2022",
"LineItems.DocumentNumber": "00001",
"LineItems.CompanyCode": "1000",
"LineItems.FiscalYear": "2022",
"LineItems.ItemNo": "002",
"LineItems.GLAccount": "320000",
"LineItems.CostCenter": null,
"LineItems.Text": "Item Text 6",
"LineItems.Amount": -110.0
},
{
"Header.DocumentNumber": "00002",
"Header.CompanyCode": "1000",
"Header.FiscalYear": "2022",
"Header.Text": "Join on this document (00002)",
"Header.Date": "11-Aug-2022",
"LineItems.DocumentNumber": "00002",
"LineItems.CompanyCode": "1000",
"LineItems.FiscalYear": "2022",
"LineItems.ItemNo": "003",
"LineItems.GLAccount": "270000",
"LineItems.CostCenter": null,
"LineItems.Text": "Item Text 7",
"LineItems.Amount": 10.0
},
{
"Header.DocumentNumber": "00001",
"Header.CompanyCode": "2000",
"Header.FiscalYear": "2023",
"Header.Text": "This is another test document (00001)",
"Header.Date": "17-Oct-2023",
"LineItems.DocumentNumber": "00001",
"LineItems.CompanyCode": "2000",
"LineItems.FiscalYear": "2023",
"LineItems.ItemNo": "001",
"LineItems.GLAccount": "6182000",
"LineItems.CostCenter": "10002000",
"LineItems.Text": "Item Text 3",
"LineItems.Amount": 432.43
},
{
"Header.DocumentNumber": "00001",
"Header.CompanyCode": "2000",
"Header.FiscalYear": "2023",
"Header.Text": "This is another test document (00001)",
"Header.Date": "17-Oct-2023",
"LineItems.DocumentNumber": "00001",
"LineItems.CompanyCode": "2000",
"LineItems.FiscalYear": "2023",
"LineItems.ItemNo": "002",
"LineItems.GLAccount": "120000",
"LineItems.CostCenter": null,
"LineItems.Text": "Item Text 4",
"LineItems.Amount": -432.43
},
{
"Header.DocumentNumber": "00002",
"Header.CompanyCode": "2000",
"Header.FiscalYear": "2023",
"Header.Text": "This is another test document (00002)",
"Header.Date": "24-Oct-2023",
"LineItems.DocumentNumber": null,
"LineItems.CompanyCode": null,
"LineItems.FiscalYear": null,
"LineItems.ItemNo": null,
"LineItems.GLAccount": null,
"LineItems.CostCenter": null,
"LineItems.Text": null,
"LineItems.Amount": null
}
]
Code language: JSON / JSON with Comments (json)