Skip to main content
Solved

Count +1 Formula In Report Designer


Forum|alt.badge.img
  • Jr Varsity III
  • 65 replies

Hi All,

I am looking to count the total line in Sales Order Summary. I do not know what would be the formula to have the text box to count 1 to 10 or more. I used Count but it just don’t add and give me random number. 

 

 

Ultimately I need a formula that simply count number of lines in the report and is not affected but the order itself.

Kind regards,

Calvin Law

Best answer by ppowell

Hi @Calvin,

Looking at your variable definition I think you just need to remove group2 from ResetGroup. You have it resetting every time otherwise.

 

Phil 

View original
Did this topic help you find an answer to your question?

12 replies

Forum|alt.badge.img+3

Try counting the Ref Nbr column - you need to count values that change for each line. Also you might need to change the ProcessOrder attribute to WhilePrint.


Forum|alt.badge.img
  • Author
  • Jr Varsity III
  • 65 replies
  • June 7, 2022

Hi @RoyceLithgo,

Would you be able to show me how to count Ref Nbr column? I do not know how to count things that is outside the table.

Thank you,

Calvin Law


lauraj46
Captain II
Forum|alt.badge.img+8
  • Captain II
  • 485 replies
  • June 8, 2022

@Calvin ,

If you are trying to show sequential line numbers on the report, then you can accomplish this with a variable.  Variables are configured and updated in the ‘Variables’ collection on one or more sections of the report.  Then you can reference them in the formulas on a textfield on the report.

 

 

Take a look at SO641000.rpx (Sales Order Form) for an example.  

Hope this helps!

Laura

 

 


Forum|alt.badge.img
  • Author
  • Jr Varsity III
  • 65 replies
  • June 8, 2022

Hi @lauraj46,

I have try to set it up in the header section but it return only “1” in the formula. 

 

The formula is setup in Header section as this way helps prevent each line from repeat itself (Something in the Relationships Table that I can not figure out. 

Would there be any other way that allow me to count number of lines that show up in a report under Header fields?

Thank you,

Calvin


Forum|alt.badge.img+1
  • Semi-Pro I
  • 128 replies
  • Answer
  • June 8, 2022

Hi @Calvin,

Looking at your variable definition I think you just need to remove group2 from ResetGroup. You have it resetting every time otherwise.

 

Phil 


Forum|alt.badge.img+11
  • Acumatica Support Team
  • 783 replies
  • June 9, 2022

Hi @Calvin 

Here is an existing KB which uses similar approach to get running total. 

https://community.acumatica.com/reports-and-generic-inquires-83/how-to-add-a-column-with-the-running-total-from-another-column-in-a-report-6739

Hope this helps,

Regards,


lauraj46
Captain II
Forum|alt.badge.img+8
  • Captain II
  • 485 replies
  • June 9, 2022

Hi @Calvin ,

I agree with the comment by @ppowell .  In this situation you don’t want the variable to reset on group2.  Let us know if that works!

Laura


Forum|alt.badge.img
  • Author
  • Jr Varsity III
  • 65 replies
  • June 9, 2022

HI @ppowell and @lauraj46,

I have try to remove group2 and it does starts to count. However the result is not working properly. I then copy the same formula set to Detail section of the report and notice the line is duplicating itself for some reason:

I now am stuck as I am not sure what cause the repeat lines. if it is my Relationships setup wrong or the Filters that preexist in the original report setup:
 

If anyone have an idea please let me know and I will give it a try.

Thank you,

Calvin


lauraj46
Captain II
Forum|alt.badge.img+8
  • Captain II
  • 485 replies
  • June 9, 2022

Hi @Calvin ,

You should not delete group2 from the report or move the formula or any of the other fields to the Detail section.  Just delete the text on the ‘ResetGroup’ line of the variable properties.  The highlighted text should be removed.

 

Hope that helps!

Laura


Forum|alt.badge.img
  • Author
  • Jr Varsity III
  • 65 replies
  • June 9, 2022

Hi @lauraj46,

It now work! I think there was another variable somewhere that makes the numbering went all over the place. 

Now that I see the numbering is correct, how would I be able to reset based on order type? What reset expression will I need to put in in order for the number go back to “1” when the order type changes?

Thank you, 

Calvin 


lauraj46
Captain II
Forum|alt.badge.img+8
  • Captain II
  • 485 replies
  • June 9, 2022

Hi @Calvin ,

I’m not sure how you have the grouping configured in the report.  If your group1 is on the field OrderType then you can change the ResetGroup to be group1 and I think that should do it.

Hope that helps!

Laura


Forum|alt.badge.img
  • Author
  • Jr Varsity III
  • 65 replies
  • June 9, 2022

Thank you @lauraj46!

Making new group solves my problem!

Calvin


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings