Load additional Item Information in performant way

Andre Seiss

September 15, 2023 13:21

Hi,

I face currently a challenge to load additional information for items in a performant way into multiline  marketing document.

We have an UDT that holds additional technical information for items. It can be 10-20 lines with 3 columns with information for 1 item. UDT has columns ItemCode, Characteristic, Value. It should be shown like a table below each item line (Characteristic & Value). In the past we loaded the data with LD.Query. But this was already slow as LD.Query was run for every document row.

We added it now as own datasource in Layoutdefinition and linked it to SWA_LD_Document:Row via ItemCode. To display all lines from the UDT we packed it into an advanced databand. But performanc is now even worse because we have probably now more loops in loops.

How can we solve such a requirement in a performant way?
Any suggestions welcome.

0

• 

  Daniel Ebi

  September 15, 2023 13:32

  Hi Andre,
  I think you should create a query showing only the needed items for an document.
  As I understand correctly your UDT.Code should be the ItemCode.
  
  Then you can create the query like that:
  SELECT DISCTINCT T0.* FROM UDT T0 INNER JOIN @@RDR1 DL ON T0.Code = DL.ItemCode WHRE DL.DocEntry = [%DocEntry]
  
  - I used DISTINCT in case of having one ItemCode in more than one document line.
  - I used "@@RDR1", because coresuite designer replaces it with the right table e.g. rdr, dln, inv. With this way, you can use the same query in all documents.
  - I am not sure, but I think the paramter [%DocEntry] is standard in documents

  The result of this query should only deliver the itemcodes which are used in the document.
  
  Then you can link DocumentRow with your new qury by Link filed 1 ItemCode ans Linkfield 2 Code.
  
  I hope it works fine.
  
  Kind regards
  
  Daniel

  2
• 

  Andre Seiss

  September 19, 2023 13:09

  Hi Daniel,

  thanks for your suggestion.
  I got it now working with proper speed by rewriting the Query to select based on DocEntry and use the auto linking by DocEntry. I don't use the Link Fields but instead defined a FilterExpression in the databand by DocumentRow ItemCode = ItemCode form my UDT.

  Thanks for your quick help
  Regards Andé

  0

Please sign in to leave a comment.