Removing Duplicates from GIs in Acumatica

Sometimes you need to add an additional table to a simple Generic Inquiry in Acumatica in order to get data that is not in the original table.  If the new table has a many-to-one relationship with the original table, you can see duplicates, (or worse: entries as many times as there are records in the new table).  For example, let’s say you want to build a GI that shows PO Receipts.  You also want to show the PO Nbr on the GI.  However, PO Nbr does not exist in the POReceipt table, so you must link to the POReceipLine.

When you view your results, you’ll see records as many times as there are PO Receipt Lines for that PO Receipt:

A better way to create this inquiry is to create a new custom DAC (view in SQL), which pulls unique records for each PO Nbr in POReceiptLine.  Then link to that new DAC in the GI.

• First, create the new DAC using Customization Projects.
  • Go to Customization Projects and click New. Give it a new Project Name and a Description, then click on the Project Name to modify it.  Then click on the Database Scripts, and click “Add” and “Script” to add a new script:
    
  • Enter a Script Name and enter a drop if exists and create view script as below:
    
  • Click OK to close.
• Now click on the Code link and click to add a new Code:
  
  • Select the File Template of “New Dac,” and enter a unique Class Name that makes sense to you. Also select to “Generate Members from Database.”
    
  • Next, modify the Code as shown below, to mark key fields in the new DAC:
    
• Then publish the new customization project as you normally would.
• Finally, you can now add this new DAC to your GI:
  
  

Now your results will no longer have duplicates: