Forum Discussion

hoaphan's avatar
hoaphan
New Contributor III
4 years ago

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...
Data Integration
REST Api
  • pvanerp's avatar
    pvanerp
    22 days ago

    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

     

pvanerp's avatar
pvanerp
New Contributor II

Hi IBK,

Did you get a json with nested namespaces working? I'm having the same issue.

IBK's avatar
IBK
New Contributor III
24 days ago

Hi pvanerp,

      Yes I did get it to work.  What is the exact error you are getting?

In the Business Rule you are looking for each one of Name Value pairs as below and that worked.  args.NameValuePairs.XFGetValue("etaPayplanName") 

Sample:

"CustomSubstVarsAsCommaSeparatedPairs":"piResponse=https://sage.nia.nih.gov/api/filedownload/16694,applId=10941338,"

Thank You,

Indu

  • pvanerp's avatar
    pvanerp
    New Contributor II
    22 days ago

    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": "johnd@mycompany.com",
          "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": "johnj@mycompany.com",
      "surname": "Johnson",
      "id": "1234567890",
      "firstName": "John"
    },
  ]
}

    Thanks for any help or guidance!

    • pvanerp's avatar
      pvanerp
      New Contributor II
      22 days ago

      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