Remove Duplication Data

Hello,

Can you show the join on the three last Relationships that are custom to your report?

When we have too many results or too few results, the table Join is nearly always at issue.

When APInvoice is joined to APTran we need to join on both Reference number and DocType to Trantype. 

When Batch is joined to INRegister, may need  multiple fields too.

Laura

hi @Laura02, 

here, the rest relations

all the relation based on the DAC Shema browser.

When you have both the AP Invoice and AP Tran table connected it will duplicate the AP Invoice data for every AP Tran line within that invoice.

For example, if you have AP invoice XYZ and in that invoice’s detail section you have four different transaction level lines, lets call them 1, 2, 3, and 4, the result will display four lines in your GI or report, one for each line in the detail section.  You will end up with:

Invoice Number, Line Detail
XYZ, Line 1
XYZ, Line 2
XYZ, Line 3
XYZ, Line 4

Anything else that is connected will also repeat in the same manner. 

Hello,

Building on what HGilbert said, you may Group the data on the report by one of the APTran fields (by PO Receipt #?).  Bills with multiple lines, each with different Receipt numbers will continue to list separately, multiple lines, one per Receipt.

Laura

@hgilbert , Because we need to track PO Number and Receipt Nbr. on each line journal transaction,  that so how to make a direct relation table from Batch to APTran without APInvoice ? because there is no direct connection both of them on DAC. 

@Laura02, add APTran.ReceiptNbr in Grouping tab also didn’t help

Appreciated if there any workaround for this issue.

I’m trying to understand what your desired result is from your screen shot above.
Should this be displaying just the two highlighted lines? Once for each different description?
If so, perhaps try grouping by this field and see if that helps.
 

Another possibility is that you may need to utilize a sub report. The main report would call up just the invoice table, and then pass a parameter through to the subreport which can be linked to the APTran table. This would prevent the duplication in the main report as you would not have the APTran table connected there.