Solved

Report Selection criteria based on running total

  • 26 August 2022
  • 7 replies
  • 114 views
L
Rank
Userlevel 6
Badge +5

Hello,

I need to write a report (or GI) that allows me to report if there are more than 1 sales order in Acumatica with the same “Customer Order Nbr.”

I have done similar things a million times in other report writers, but I do not know of a method to accomplish this with Acumatica’s report writer.

So I need to run through the SO’s and report Acumatica sales orders and their associated “Customer Order Nbr”, but only if multiple sales orders have the same “Customer Order Nbr.”.  I’ve used functions like running totals that reset on the change of a field value etc… using other reporting tools, but I don’t know how to accomplish this here.

Any great ideas on Friday afternoon in AcumaticaLand!!!???

Thanks!!!!

icon

Best answer by BenjaminCrisman 29 August 2022, 16:27

View original
If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.

7 replies

BenjaminCrisman
Rank
Userlevel 7
Badge +4

@ltussing03 Pretty sure for this you will need to join in the SOOrder table more than once, give the other table an alias like DuplicateOrder and have a condition where if SOOrder.CustomerOrderNbr = DuplicateOrder.CustomerOrderNbr then it will show in the results.

This method is useful for comparing data from within a single table against its own data.

L
Rank
Userlevel 6
Badge +5

Ben,

THANKS!!!

I think I can make this work!

However, I don’t see how to add the SOOrder table more than once.

It’s the only table on the report and it is not available for selection a second time from the table tree.

I see you can have an ALIAS on the relationships tab, but cannot get this to take.

Can you offer any advice on getting the table into the report twice using an alias.

And thank you!!!

If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
BenjaminCrisman
Rank
Userlevel 7
Badge +4

@ltussing03 Yes, the way to do this is with the Relationships tab using the Alias option.

This is a screenshot off the AR Invoice form:

First they gave the ARAddress table an Alias so that it can be easily recognized what they are looking to pull out of the table based on the link to BillAddressID.

Because the Shipping contact details are also in the invoice and in the ARAddress table, linking with a new Alias allows to link on ShipAddressID and get different address details.

If this doesn’t help get the relations working post some screenshots of where the report is running amiss,

Thanks!

L
Rank
Userlevel 6
Badge +5

Bill,

My difficulty is the child and parent are the same table.  It lets me type it in and I add an alias, but it doesn’t save it.  Both the child and parent are SOOrder.  So I need to create the alias before this screen it seems.

Thanks,

Larry

If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
BenjaminCrisman
Rank
Userlevel 7
Badge +4

@ltussing03 I was able to create a join on the SOOrder on itself:

The Alias table has to be the Child table.

L
Rank
Userlevel 6
Badge +5

Ben thanks.

I must have been doing something wrong.

I’m tied up now and will test.

Thanks again for all of your time and efforts helping me out!!!

If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
L
Rank
Userlevel 6
Badge +5

Thanks!!!!

If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.

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