11-21-2022 12:10 AM - last edited on 05-02-2023 11:00 AM by JackLacava
Hi,
Does anybody have a example of an business rule to import data from Sage with an API?
11-23-2022 04:18 AM
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.
11-23-2022 04:22 AM
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.
11-23-2022 06:06 AM
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
11-23-2022 06:09 AM
Thank you very much for the help, Jack.
Much appreciated.
03-29-2023 09:15 AM
Hi Wikus, did you have any luck with your Sage integration as I want to implement the same connection. Thanks.
03-29-2023 09:22 AM
Hi Grant,
I haven't tested it yet, the project is on hold for now. I will let you know when I have tested.
03-29-2023 09:26 AM
Super thanks Wikus. I will let you know how we get on if we get there before you.
04-07-2023 04:37 AM
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?
04-11-2023 08:59 AM
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