Skip to main content
Member
Nov 21, 2022 5:10 AM UTC

Data import from Sage with API

Hi,

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

 

9 replies

Employee - Sage
Nov 23, 2022 9:18 AM UTC

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.

WikusAuthor
Member
Nov 23, 2022 9:22 AM UTC

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.

Employee - Sage
Nov 23, 2022 11:06 AM UTC
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
WikusAuthor
Member
Nov 23, 2022 11:09 AM UTC

Thank you very much for the help, Jack.

Much appreciated.

Newcomer
Mar 29, 2023 1:15 PM UTC

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

Veteran
Apr 11, 2023 12:59 PM UTC

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