Solved

How to Group Items By Lot Serial on Report Writer/Report Designer

  • 31 October 2023
  • 9 replies
  • 130 views
K
Rank
Userlevel 4
Badge

We have a report that prints the pack list along with Lot Serial Nbr. Currently the items on a shipment are repeating multiple times because the items are located on multiple locations.  

 

Current Output

How can we display items along with lot serial number, total shipped qty but not have it split these lines based on location? 
We currently have it grouped by Sales Order Nbr and Order type.  

 

 

I have also attached the rps file of the report for reference. Thank you for the help in advance! 

icon

Best answer by Robert Sternberg 1 November 2023, 22:03

View original
1 Attachment
Kaavya

9 replies

Robert Sternberg
Rank
Userlevel 7
Badge +8

Hi @kanupindi can you share how you would like the lot/serial number to appear.  Are you expecting a single line with the lot/serial numbers listed, separated by commas?

Inventory ID Qty Lot/Serial Nbr
Invt001 3 0001, 0002, 0003

 

If not could you provide a quick mockup similar to the above of what you are looking for?

 

Thanks!

Robert Sternberg, Crestwood Associates -- ** Summit 2024 - Visit Us at Booth #600 ** -- If a community member helped you, please *Like* their post/reply. If they provided an answer to your question, please mark it as *Best Answer*.
K
Rank
Userlevel 4
Badge

Hi @Robert Sternberg,

thank you for your response. It should look something like this below: 

Inventory ID Lot Serial Nbr Shipped Qty
123 001 463
123 002 500
456 003 412
456 005 320
897 004 140

 

Kaavya
Robert Sternberg
Rank
Userlevel 7
Badge +8

Please try adding a group by of [SOShipLineSplit.LotSerialNbr]

If you need help please upload an rpx file rather than an rps file.  More info on this here - https://www.augforums.com/acumatica-rps-versus-rpx-file-types/

Robert Sternberg, Crestwood Associates -- ** Summit 2024 - Visit Us at Booth #600 ** -- If a community member helped you, please *Like* their post/reply. If they provided an answer to your question, please mark it as *Best Answer*.
K
Rank
Userlevel 4
Badge

Hi @Robert Sternberg, 
We did try grouping by lot serial nbr but that did not work. Is there a different way we can try? I have attached the rpx file below. The reason why they are duplicating is because of different locations

Below is a snapshot from our database. We also grouped by Lot Serial, Location and Inventory ID on the report and that did not work either. 

 

thank you!

1 Attachment
Kaavya
Robert Sternberg
Rank
Userlevel 7
Badge +8

Hello, thanks for the file, you are seeing this because you are still reporting in the detail (ungrouped section) of the report, if you move your fields into a grouped section you will only see one result line per group.  Do not put anything in the detail section unless you want it to print ungrouped results.  

Robert Sternberg, Crestwood Associates -- ** Summit 2024 - Visit Us at Booth #600 ** -- If a community member helped you, please *Like* their post/reply. If they provided an answer to your question, please mark it as *Best Answer*.
K
Rank
Userlevel 4
Badge

Hi @Robert Sternberg , that did not work. It is still duplicating items like the ones below : 

 

Kaavya
Robert Sternberg
Rank
Userlevel 7
Badge +8

Hi @kanupindi 

Please take a look at the attached, I have highlighted the section footer in green which only prints once per lot/serial group.  Notice how the detail section prints for every shipment line. 

To finalize this solution you will need to:

  1. Set detailSection1 to Visible = False, this will hide the ‘duplicates’
  2. Remove the green backcolor from groupFooterSection3 under Properties>Style>Backcolor
  3. Follow my example on the QTY. ORDERED column to create an aggregate total for your group when necessary. (QTY. SHIPPED, QTY. BACKORDERED)
  4. Update the NO. Column to more accurately describe the group(s) line nbrs. 

 

1 Attachment
Robert Sternberg, Crestwood Associates -- ** Summit 2024 - Visit Us at Booth #600 ** -- If a community member helped you, please *Like* their post/reply. If they provided an answer to your question, please mark it as *Best Answer*.
K
Rank
Userlevel 4
Badge

Hi @Robert Sternberg  , thank you very much for your help. Looks like it worked with a few changes! 

Kaavya
robgriffin00
Rank
Userlevel 3
Badge

This was an excellent example to help me solve a similar problem on T&M project invoices - thank you.

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved