Hi,
A client is looking to have a side panel on the inventory screen that shows the customers who purchased the item
They do not want to see every invoice but a total quantity.
That is very doable with a grouping by Item & Customer.
They also want to see last purchase information, the Quantity & the Unit Price & Date of Sale.
When I do the grouping the Quantity & Unit Price total. The Date Of the last sale is correct but I can’t get the quantity & unit price to show for the associated last purchase.
Any help is appreciated.
Bill
Solved
How to return last line data in a GI with grouping
+3Best answer by lauraj46
Hi
The easiest way to accomplish this is to create and display a report built with the Report Designer on the side panel. The report would have the InventoryID as a parameter. On the report you can sort and group by customer. To print the total quantity you can use the sum in a formula in the customer group footer. If you sort by customer and then by sale date in ascending order then in that same customer footer section you can also print the details of the last detail record for that customer.
Alternatively, it may be possible to construct a generic inquiry that pulls the detail of the latest record by left joining the SOLine or other tables multiple times and comparing the dates, and/or by using subqueries. I recommend the Report Designer method if that works for you.
Hope this helps!
Laura
+9Hi
The easiest way to accomplish this is to create and display a report built with the Report Designer on the side panel. The report would have the InventoryID as a parameter. On the report you can sort and group by customer. To print the total quantity you can use the sum in a formula in the customer group footer. If you sort by customer and then by sale date in ascending order then in that same customer footer section you can also print the details of the last detail record for that customer.
Alternatively, it may be possible to construct a generic inquiry that pulls the detail of the latest record by left joining the SOLine or other tables multiple times and comparing the dates, and/or by using subqueries. I recommend the Report Designer method if that works for you.
Hope this helps!
Laura
Hi Bill,
Please find the attached sample Generic Inquiry created for you with the ARTran table, for your review. Please note the below key points on this Generic Inquiry:
- Sort by Descending of the Field Value CreatedDatetime. This will show the latest record in the top.
- Select top 1 on the Generic Inquiry - To fetch the top line from the results.
From my testing:
Latest invoice line showing the Qty for the item AACOMPUT01
Result from the Generic Inquiry showing the latest record from the AR Tran from the above invoice:
Thanks
Chandra
+5Hi
I think you should create a SQL View to fetch such details as you have described. In SQL View you can write the sub query to get last purchase information, the Quantity & the Unit Price & Date of Sale. Let me know if this helps or if you have any questions.
Login to the community
No account yet? Create an account
Social Login
Login with your Acumatica accountEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.