Deprecated: Array and string offset access syntax with curly braces is deprecated in /www/wp-content/plugins/Ultimate_VC_Addons/admin/bsf-core/auto-update/admin-functions.php on line 11 Removing Duplicates from Generic Inquiries in Acumatica

Removing Duplicates from GIs in Acumatica

Removing Duplicates 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.Removing Duplicates in Acumatica

Removing Duplicates in Acumatica

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

Removing Duplicates in Acumatica

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:
      Removing Duplicates in Acumatica
    • Enter a Script Name and enter a drop if exists and create view script as below:
      Removing Duplicates in Acumatica
    • Click OK to close.
  • Now click on the Code link and click to add a new Code:
    Removing Duplicates in Acumatica

    • 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.”
      Removing Duplicates in Acumatica
    • Next, modify the Code as shown below, to mark key fields in the new DAC:
      Removing Duplicates in Acumatica
  • Then publish the new customization project as you normally would.
  • Finally, you can now add this new DAC to your GI:
    Removing Duplicates in Acumatica
    Removing Duplicates in AcumaticaRemoving Duplicates in Acumatica

Now your results will no longer have duplicates:

Removing Duplicates in Acumatica

Leave a Reply