How to Display Only the First Record of Each Group in Acumatica Report Designer

How would you identify the first record?

If there is a line number for each record then you could create a table self join.

For example:

List table twice as a datasource:

I’m using SOLine as an example:

SOLine as alias SOLine

SOLine as alias SOLine2

Then create the self table join

SOLine.LineNbr = Min(SOLine.LineNbr)

The self join the @ejmillar explains works well in a GI.  In the report designer, you should be able to simply sort on the order number and line number fields, then print the first line details in the group header.

Laura 

Hi @lauraj46 @ejmillar,
I need to display only the first record of each group in the report. Is there any method to do that?
Thank you 

Hi @RKarunarathne51,

Both the techniques described above will display the first record of each group. 

Are you having difficulty implementing them?

Regards,

Ewan.

Hi @ejmillar ,

Yes. Could you please explain it step by step?
Thanks in advance

Is it an existing Acumatica report? 

What are you grouping on?

Hi @ejmillar ,

The Min (x,y) function returns the smaller of two values. How can we use it to get the minimum value from an array, as you mentioned above in SOLine.LineNbr = Min (SOLine.LineNbr)? Or is there any other Min function for that?
Thank you

Hello,

I tried the Min function within a report and it wasn’t working the way that I expected.

Apologies for that.

Here is another technique:

1. Add a Variable for Row Number:

   • In the Group Collection Editor, select the group you want to modify.
   • In the Properties pane for the selected group, add a variable to keep track of row numbers. Click on Behavior > Variables, then add a new variable, e.g., RowNumber

2. Set the Variable:

   • In the detail section of your group, set the variable to increment by 1 for each record. You can use the Expression Editor to set this up. For example, set RowNumber to RowNumber + 1.

3. Filter to Show Only the First Record:

   • In the Properties pane, go to the Visibility property of the detail section and click the button to open the Expression Editor.
   • Add a condition to show the detail section only when RowNumber equals 1. For example, =IIF(RowNumber = 1, true, false).

Hi @RKarunarathne51 ,

If you simply move your fields into the group header then you will be able to print the details for one record per group.  You will need to define the Group in the Groups collection and the sort order in the Sorting collection.  Hope this helps!

Hi @lauraj46 ,
Thank you for your guidance. Moving the fields into the group header and defining the Group in the Groups collection, as well as setting the sort order in the Sorting collection, worked perfectly. I appreciate your help

Hi @ejmillar ,

I greatly appreciate your detailed instructions. Your guidance was invaluable. Thank you!