How to join related records for IDOC when using RowGeneration?
I have a situation where I want to create IDOCS for customers (1 idoc / customer). Each customer may have multiple associated accounts. Each account results in an idoc segment. Each Account has a DOCUMENT-NUMBER which links it to the Customer.
I first created the job to create 1-idoc per customer by adding a RowGeneration transform and selecting from this. This works successfully as I create 1-idoc for every customer. The segments select only from the RowGen transform (not Customer). This is how my input schema looks for each segment:
However, I now want to add segments to each of these idocs (for the Accounts).
When I make the join criteria, I have to use Customer/Account as a join. This means I must select from Customer/Account, which is returning all Accounts for all customers and making each as an idoc (so in the above situation I would have 3 idocs all with 3 segments - not 1 with 1, and another with 2).
I am not sure how to modify these schema selections to properly make the segments work correctly. It seems like I need to avoid using Customer in selection criteria here, but if that is the case, I am unsure how to setup my join correctly so that each set of Accounts is linked to the proper customer.
In case anyone else has a similar problem, the solution is:
- Remove Account selection from the idoc input schema.
- The input schema for the idoc needs to be your Customer table/Row Generation
- Add the Account selection to the relevant segments.Do not select from Customer here
- Do not use a join (this is where my problem was). If you use a join, you are required to select from both Customer/Account tables, which causes the problems I had
- Use the where clause with something like Customer.ID=Account.CustomerID (or whatever your join condition would have been)