Data import from Sage with API

Wikus
New Contributor III

Hi,

Does anybody have a example of an business rule to import data from Sage with an API?

 

9 REPLIES 9

JackLacava
Community Manager
Community Manager

I don't know anything about the Sage API, but if you don't know how to write a Connector rule to power a Data Source, I can provide simple example code for that.

Wikus
New Contributor III

Hi Jack, thanks for the reply. The problem is the client doesn't allow direct connections to their Sage databases. You can only access it via the Sage API.

However, I would appreciate an example of a connector code.

JackLacava
Community Manager
Community Manager
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Transformer, ByVal args As ConnectorArgs) As Object
    Try
        Select Case args.ActionType
            Case Is = ConnectorActionTypes.GetFieldList
				' Here we want to provide the list of fields the connector will provide, 
				' so we can map them in the DataSource page.
				
                Dim fieldList As New List(Of String)
				fieldList.Add("SomeColumn")
				fieldList.Add("SomeOtherColumn")
                Return fieldList

            Case Is = ConnectorActionTypes.GetData
                ' Here we're actually doing the work of retrieving data.
				
				Dim myData as DataTable = Me.GetSageData(si, globals, api, args)
                api.Parser.ProcessDataTable(si, myData, true, api.processInfo)
				Return Nothing

            Case Is = ConnectorActionTypes.GetDrillBackTypes
			    ' if you want to support DrillBack, 
				' here you have to declare which types you provide
				
                Dim drillTypes As New List(Of DrillBackTypeInfo)
				drillTypes.Add( _
					new DrillBackTypeInfo( _
						ConnectorDrillBackDisplayTypes.DataGrid, _
						New NameAndDesc("SageData","My Sage Data")))
				return drillTypes

            Case Is = ConnectorActionTypes.GetDrillBack
				' if you want to support DrillBack, 
				' here you have to provide the actual source data
				
                If args.DrillBackType.NameAndDescription.Name.Equals( _
							"SageData", StringComparison.InvariantCultureIgnoreCase) Then
					' retrieve the row we're drilling for. 
					' Field names can be found in StageConstants or StageTableFields
					Dim sourceValues as Dictionary(Of string, Object) = _
							api.Parser.GetFieldValuesForSourceDataRow(si, args.RowID)
					Dim drillBackInfo As New DrillBackResultInfo
					drillBackInfo.DisplayType = ConnectorDrillBackDisplayTypes.DataGrid       
					' similarly to GetSageData, getSageDrillback should return a DataTable. 
					' PageSize and PageNumber can be used to manage pagination.
					drillBackInfo.DataTable = me.getSageDrillback( _
						sourceValues, si, globals, api, args)
					Return drillBackInfo
				Else     
					Return Nothing 
				End If 
      
        End Select
        
    Catch ex As Exception
       Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
    End Try
End Function     

function getSageData(ByVal si As SessionInfo, ByVal globals As BRGlobals, _
			ByVal api As Transformer, ByVal args As ConnectorArgs) As DataTable
	' This is where you would launch your Sage api calls, 
	' stuffing results in a DataTable.
	' Extract parameters can be defined in various ways 
	'   (get the WF period, or the POV, or Text properties on Scenario or WF...)
    ... do sage stuff here
	return myDataTable
end function
function getSageDrillback(ByVal sourceDict as Dictionary(Of string, Object), _
			ByVal si As SessionInfo, ByVal globals As BRGlobals, _
			ByVal api As Transformer, ByVal args As ConnectorArgs) As DataTable
	' get the necessary parameters from sourceDict.
	' Field names can be found in StageConstants or StageTableFields
	' args.PageSize and args.PageNumber can be used to manage pagination.
    ... do sage stuff here
	return myDataTable
end function

Wikus
New Contributor III

Thank you very much for the help, Jack.

Much appreciated.

GrantH
New Contributor

Hi Wikus, did you have any luck with your Sage integration as I want to implement the same connection. Thanks.

Wikus
New Contributor III

Hi Grant,

I haven't tested it yet, the project is on hold for now. I will let you know when I have tested. 

 

GrantH
New Contributor

Super thanks Wikus. I will let you know how we get on if we get there before you.

Hey @JackLacava !

I tried to use the code above, but I am having an issue with the XFDatatable and this line : 

drillBackInfo.DataTable = me.getSageDrillback( _
						sourceValues, si, globals, api, args)

 

I can not find any info on this XFDatatable.

I created a dummy datatable : 

	Private Function dtDummyTable(ByVal sourceDict As Dictionary(Of String, Object), ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Transformer, ByVal args As ConnectorArgs) As DataTable
	'Create a new DataTable with 5 columns
Dim dt As New DataTable()
dt.Columns.Add("Column1")
dt.Columns.Add("Column2")
dt.Columns.Add("Column3")
dt.Columns.Add("Column4")
dt.Columns.Add("Column5")

'Add 10 rows with dummy data
For i As Integer = 1 To 10
    Dim row As DataRow = dt.NewRow()
    row("Column1") = "Data " & i
    row("Column2") = i * 2
    row("Column3") = DateTime.Today.AddDays(i)
    row("Column4") = True
    row("Column5") = 1.23 * i
    dt.Rows.Add(row)
Next

Return dt
End Function


So how to drill back to this datatable? 

Connect with me on:
LinkedIn: https://www.linkedin.com/in/nicolas-argente/
Website: https://aiqos.io
If you want to lift yourself up, lift up someone else.

NicolasArgente
Valued Contributor

Here is the answer on how to make an XFDatatable: 

Dim xfDT As New XFDataTable(si, dt, Nothing, SharedConstants.Unknown)


where dt is the datatable.
Thanks @JohnGoodwin 

Connect with me on:
LinkedIn: https://www.linkedin.com/in/nicolas-argente/
Website: https://aiqos.io
If you want to lift yourself up, lift up someone else.