Skip to main content

Hello,

         I have a requirement to display all invoice lines and its related shiplines(the key is the shipped 0 line, which has not converted into invoice) by invoice# 
        I can get the data using the sql below:

 

select* from ARTran a 
full join SOShipLine c on a.SOShipmentLineNbr=c.LineNbr and a.SOShipmentNbr=c.ShipmentNbr
left join SOOrderShipment b on c.ShipmentNbr=b.ShipmentNbr
where 
a.RefNbr='xxxxx' 
or 
b.InvoiceNbr='xxxxx'

 

The key here is the “or” where condition.

 

However , when it comes to report designer

I can only either group by artran.ref / arinvoice.ref

or

group by soordershipment.invoicenbr

 

either way, I would get data lost. 

      

Hi @ray20 ,

I just came across this post… maybe you’ve already got the answer. 

If I’m understanding you correctly, I think you should be able to do a conditional grouping by using a formula in the grouping expression, something like this:

 

 

For the data field, you could use an expression like this one: =IsNull(nSOOrderShipment.InvoiceNbr],.ARTran.RefNbr])

Hope that helps!

Laura

@lauraj46 Thank you for your trick. I did not know this talent move before.
I was using a sub report to solve my need before.
Next time, I will try this method. Thank you.

I use sub report as well. Will try with this method.

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