SAP connector

BabuJayaraman
New Contributor II

We have been using the SAP connector for fetching the GL data and few other information like units and install base. The connectors were working fine until Oct 2023. We noticed duplicate and/or missing rows. The row counts would match. It would only have this issue when the data is in multiple packages. We had package size of 10000.

As a workaround we increased the package size 100000. But one of the integration has >500000 rows. When we tried to increase the package size to match the rows, the integration would fail with internal memory size error.

Looking into get data function, I would like to know if there is a way to add order. Below you will find the get data code. The where clause is - r3Table.AddCriteria("FISCYEAR = " & "'" & fiscalYear & "'"). Is there a way to add order by?

#Region "GetData"
'Read source data from the R3 table
Private Sub GetSourceDataSAP(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Transformer, ByVal r3Conn As R3Connection, ByVal r3TableName As String)
	Try
		r3Conn.LogDir = BRAPi.Utilities.GetFileShareFolder(si, FileShareFolderTypes.ApplicationOutgoing, Nothing)
		r3Conn.Logging = True
		r3Conn.Open()
		Dim r3Table As New ReadTable(r3Conn)
		Me.w_r3Table = r3Table
		Dim whereclause As New Text.StringBuilder
		'Set the data package size
		r3Table.PackageSize = Me.m_PackageSize
		r3Table.RaiseIncomingPackageEvent = True
		'Specify the table to open
		r3Table.TableName = r3TableName
		r3table.SetCustomFunctionName("Z_XTRACT_IS_TABLE")
		Dim fields As List(Of String) = GetFieldList(si, globals, api, r3conn, r3TableName)
		For Each fieldname As String In fields
			r3Table.Addfield(fieldname)
		Next

		'filters for year 
		' Dim fiscalYear As String = "2021"
		Dim fiscalYear As String = TimeDimHelper.GetYearFromId(api.WorkflowUnitPk.TimeKey).ToString
		'where clause
		r3Table.AddCriteria("FISCYEAR = " & "'" & fiscalYear & "'")
		'Query the table
		r3Table.Run()
	Catch ex As Exception
		Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
	End Try
End Sub

 

4 REPLIES 4

JackLacava
Community Manager
Community Manager

This is really a question for Theobald Software, makers of the ERPConnect library that the connector leverages. The documentation I've found on their site doesn't indicate anything related to Order By; in fact, they seem to have removed .AddCriteria too in the newest releases (6.1+), indicating to use .WhereClause instead. I don't know which version of ERPConnect you have installed.

It also looks like your code is using a SAP function Z_XTRACT_IS_TABLE, of which several versions can be found online - some supporting ORDERBY as parameter and some that don't.

I don't know enough about SAP to tell you more than that; unless someone else comes in, you might have to open a case with Support.

Btw, next time you'd like to post some code here (which you're welcome to!), please have a look at this post, describing how to do it in the best way.

NicolasArgente
Valued Contributor

Hi All!
Adding an order to your data fetch from SAP in VB.NET using ERPConnect's ReadTable isn't straightforward. ReadTable is great for grabbing data but not for sorting it like you'd do in a SQL query. It is designed primarily for data extraction and not query manipulation!
- Your solution could be to use a custom SAP Function Module  - requires some development from your SAP team. 
- Or you could Handle less data in each call by adding a filter.
  Can you add a filter on the month and add a loop in your BR? 

  1. You add a filter on month, like Jan to June.
  2. You run this call and it imports Jan to June data (so your volume gets divided by 2 in theory)
  3. You write those values in a data table
  4. You do a second call for data from July to December
  5. You add those value to the data table...

    Obviously you could reduce the volume if you loop with entities, or even doing month by month...

Hope it helps,

Nic

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.

franciscoamores
Contributor

Hi,

I think the readtable class has the property OrderBy that you can assign. The problem is that it works only if the Z_Xtract custom function has that option enabled. If I remember well there were new versions of the custom function with the option enabled.

so R3table.orderby = “your order by”

you can give a try.

Anyway, I’d try to find out why that issue is happening .

BabuJayaraman
New Contributor II

I could not get orderby to work.

Anyone with SAP integration greater than 500K record with the connector integration that is working?