Acumatica Tips and Tricks – How to print multiple product labels based on a line item quantity WITHOUT a customization

Recently, one of our Acumatica customers had a requirement to print bar coded product labels. The objective was to print multiple product labels for each item. We needed the number of labels to match the ship quantity for the line on the shipment.

Creating this report in the Acumatica Report Designer is straightforward if the item is serialized. In that case, we could have simply configured the report schema to include the DAC SOShipLineSplit which will contain a row for each item with the unique Serial Number stored in the LotSerialNbr field.
However, in our scenario the items did not have unique serial numbers, therefore we needed a way to create additional records on the fly – one for each the labels to print.

A typical way to approach this problem is by joining on an auxiliary table that contains one record for each integer number. The idea is to join on the auxiliary table where the integer value is less than or equal to the line item quantity. One useful thread on this topic suggests a customization to create a “Numbers” table.

Before you go that route and create a customization, lo and behold – there is already an existing table in your Acumatica instance that might fit the bill! The DAC is named PX.SM.DateInfo. I took a look on my local test instance in SQL and found that this table contains exactly one record for each date from January 1, 1970 through June 6, 2079 – 39,969 records in total.

Using the DateDiff function we can convert the [date] field to an integer by calculating the number of days between 12/31/1969 and [date].

To join on the DateInfo DAC in the Report Designer, first add it to the schema on the Tables tab.  ​

On the Relationships tab, specify the join as shown below, using the Child Formula button to edit the formula for the Child Field:
=DateDiff(‘d’, ’12/31/1969′, [DateInfo.Date])

To display the count (e.g. 1 of 50), we can use a formula such as the built-in global [PageIndex] or create a counter variable within the Report Designer that will be incremented on each report detail.

And here is our final label:

There would be actually be 50 labels printed, one per carton.
For more Acumatica tips and tricks check out the Parallel Solutions blog. Happy report designing!

#Acumatica #Labels #ReportDesigner #tipsandtricks

Ready to Experience the Parallel Solutions Difference?

Contact us today and learn how we can support your business and provide the solutions and services it needs to achieve your goals.