The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.
Forum Discussion
Solved
REST API USE
Hello OneStream Community, I'm working on how to implement a REST API in OS, but i'm not sure to understand all the steps, i've already read the documentation and i've found it very hard to unders...
Ahhh, thank you CoPilot. I got it to work with this code:
Private Function GetSourceDataREST(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Transformer) As DataTable Try Dim strEntityWF As String = api.WorkflowProfile.Name.Split(".")(0) ' Use already encoded credentials (eg.,In Postman) key Dim wdCreds As String = "<CREDENTIALS>" 'Call the REST API Dim wc As New WebClient wc.Encoding = Encoding.UTF8 wc.Headers("cache-control") = "no-cache" wc.Headers("ContentType") = "application/json" wc.Headers("Accept") = "application/json" wc.Headers("Authorization") = "Basic " & wdCreds Dim jsonURL As String = "<AURL>" Dim json As String = wc.DownloadString(jsonurl) Dim pullData As DataTable = ParseJsonToDataTable(json, strEntityWF) 'Close Web connection wc.dispose 'Return the dataset Return pullData Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Function Function ParseJsonToDataTable(json As String, EntityName As String) As DataTable Dim dataTable As New DataTable() dataTable.Columns.Add("entity", GetType(String)) dataTable.Columns.Add("displayName", GetType(String)) dataTable.Columns.Add("amount", GetType(Integer)) Dim jObject As JObject = JObject.Parse(json) Dim dataArray As JArray = jObject("employees") For Each item As JObject In dataArray Dim row As DataRow = dataTable.NewRow() row("entity") = EntityName row("displayName") = item("displayName") row("amount") = 1 dataTable.Rows.Add(row) Next Return dataTable End Function
Hi indu,
Thanks for your prompt reply. I'm afraid the problem occurs a bit sooner, on the deserialisation step. The error I'm getting is:
Unable to execute Business Rule 'cnHiBob_v1'. Unexpected JSON token when reading DataTable: StartObject. Path 'employees[0].employee', line 1, position 59.
The JSON I'm accessing has a general structure as (anonymised and shortened):
{
"employees": [
{
"displayName": "John Johnson",
"employee": {
"payrollManager": "1234567890",
"hrbp": "1234567890",
"itAdmin": "1234567890",
"buddy": null
},
"work": {
"shortStartDate": "01-01",
"startDate": "2023-01-01",
"manager": "1234567890",
"tenureDuration": {
"periodISO": "P1Y9M9D",
"sortFactor": 000,
"humanize": "1 year, 6 months and 15 days"
},
"custom": {
"field_1698922567656": null,
"field_1697701034939": "1234567890"
},
"durationOfEmployment": {
"periodISO": "P1Y9M9D",
"sortFactor": 00,
"humanize": "1 year, 6 months and 15 days"
},
"reportsToIdInCompany": null,
"employeeIdInCompany": 2,
"reportsTo": {
"displayName": "John Doe",
"email": "[email protected]",
"surname": "Doe",
"firstName": "John",
"id": "1234567890"
},
"indirectReports": 3,
"siteId": 0000887,
"tenureDurationYears": 1.777,
"department": "1234567890",
"tenureYears": 1,
"customColumns": {
"column_1694007818663": "1234567890",
"column_1694007758524": null,
"column_1692966052634": "1234567890",
"column_1695048623305": null,
"column_1697624280234": null
},
"isManager": true,
"title": "1234567890",
"site": "mycompany
"originalStartDate": "2023-01-01",
"activeEffectiveDate": "2024-01-01",
"directReports": 3,
"secondLevelManager": "1234567890",
"daysOfPreviousService": 0,
"yearsOfService": 1
},
"companyId": 1234567890,
"email": "[email protected]",
"surname": "Johnson",
"id": "1234567890",
"firstName": "John"
},
]
}
Thanks for any help or guidance!
Ahhh, thank you CoPilot. I got it to work with this code:
Private Function GetSourceDataREST(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Transformer) As DataTable
Try
Dim strEntityWF As String = api.WorkflowProfile.Name.Split(".")(0)
' Use already encoded credentials (eg.,In Postman) key
Dim wdCreds As String = "<CREDENTIALS>"
'Call the REST API
Dim wc As New WebClient
wc.Encoding = Encoding.UTF8
wc.Headers("cache-control") = "no-cache"
wc.Headers("ContentType") = "application/json"
wc.Headers("Accept") = "application/json"
wc.Headers("Authorization") = "Basic " & wdCreds
Dim jsonURL As String = "<AURL>"
Dim json As String = wc.DownloadString(jsonurl)
Dim pullData As DataTable = ParseJsonToDataTable(json, strEntityWF)
'Close Web connection
wc.dispose
'Return the dataset
Return pullData
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
Function ParseJsonToDataTable(json As String, EntityName As String) As DataTable
Dim dataTable As New DataTable()
dataTable.Columns.Add("entity", GetType(String))
dataTable.Columns.Add("displayName", GetType(String))
dataTable.Columns.Add("amount", GetType(Integer))
Dim jObject As JObject = JObject.Parse(json)
Dim dataArray As JArray = jObject("employees")
For Each item As JObject In dataArray
Dim row As DataRow = dataTable.NewRow()
row("entity") = EntityName
row("displayName") = item("displayName")
row("amount") = 1
dataTable.Rows.Add(row)
Next
Return dataTable
End Function