How to create a generic inquiry to display the AP Aged Period-Sensitive by aging bucket hoping just to get the totals of the aging buckets.

Hello,

Someone has posted an example Aging GI in this post linked below.  They are using AR Aging but have kindly provided you with all related calculations for the “Aging Buckets” - see attachment:

Laura

Note: Got the help of an AI tool when generating this response 💡

To create a Generic Inquiry (GI) in Acumatica that displays the AP Aged Period-Sensitive Report totals by aging bucket, you'll need to use the AP Aging information and aggregate the data into the following columns:

• Current
• 1-30 Days
• 31-60 Days
• 61-90 Days
• Over 90 Days
• Balance

Step-by-Step Process for Creating the Generic Inquiry:

Step 1: Navigate to Generic Inquiry

1. Go to: System → Customization → Generic Inquiry (SM208000).
2. Click + to create a new inquiry.

Step 2: Set up the Inquiry Header

1. Title: Enter a meaningful name like AP Aging Totals by Bucket.
2. Description: (Optional) Add a description like "Shows total aging buckets for AP".
3. Primary DAC: Select APVendorBalanceEnq as the Data Access Class (DAC). This DAC contains the aging information for Accounts Payable (AP).

Step 3: Select Data Fields

1. Go to the Tables tab, and ensure that the APVendorBalanceEnq DAC is included as the main table.

2. Go to the Relations tab: Add any other tables that you need, like the Vendor master table, but for aging totals, you likely won't need additional tables.

Step 4: Set Up the Results Grid

1. Go to the Results Grid tab.

2. Add the following columns to your inquiry:

   • VendorID: (if you need to filter by vendor or display them).
   • CurrentBalance: Displays the total for the Current period.
   • Bucket1: Displays the total for the 1-30 Days aging bucket.
   • Bucket2: Displays the total for the 31-60 Days aging bucket.
   • Bucket3: Displays the total for the 61-90 Days aging bucket.
   • Bucket4: Displays the total for the Over 90 Days aging bucket.
   • Balance: Displays the total for all outstanding amounts.

3. To display only the totals for each bucket, apply SUM as the aggregation for each of these fields:

   • In the Aggregation column, set each of these fields (CurrentBalance, Bucket1, Bucket2, etc.) to SUM.

Step 5: Add Filters (Optional)

If you want to filter this GI by vendor, date, or company, add the relevant fields in the Filters tab.

Step 6: Save and Test the Inquiry

1. Save the Generic Inquiry.
2. Click View Inquiry to test the results.

Step 7: Refine the Inquiry

1. If the data looks correct, but you want to display only the total row (without individual vendor details), you can modify the Group By clause:
   • Go to the Grouping section and remove VendorID from the Grouping if you want only the totals without grouping by each vendor.

Example Results

After following these steps, your inquiry should display a summary of the AP aging buckets as follows:

The totals should reflect the sum of each aging bucket for all vendors.

@chameera71 when you search APVendorBalanceEnq - there are four different options for Tables. I tried adding all four and none contain an out of the box data field of ‘Bucket’?

There’s also a great inquiry that you should be able to adapt that comes “out of the box” in the newer editions on the AP Aging Screen.  Look for Screen ID AP0012DB (Screen is called Vendor Details and it’s what you drill down into from the AP Aging donut chart).  

Hi ​@skumar98 were you able to find a solution? Thank you!