Solved

How to display 0 shipline on Invoice using Report designer?

  • 15 January 2021
  • 5 replies
  • 205 views

Userlevel 6
Badge +5

Hello,

         I need your help. Previously I thought it is easy, but Now I did not find out a solution.

As we know, the 0 shipment line would not turns into ARTran.
So we might meet a situation like 

Shipline                                  ARTran

a                                                     b

b                                                     c

c                                                      d 

                                                         e

line d and e is added by Advanced Direct Invoice Function. So they only have invoice#, no shipment#

line a is shipped 0, so it did not turn into ARtran.

My need is to display on the Printed out Paper invoice with all lines: although a is with 0 qty.

like

a

b

c

d

e

I previously thought is a full join can get this done.

But my problem is, if we are printing Invoice, we normally only know Ref# and Type.

If I apply the Invoice ref# as the where condition,

The line a will still be disappeared,  Because  a.ref is null.
What should I do?

 Can anyone give me some ideas?

icon

Best answer by Neil Cantral 15 January 2021, 20:22

View original

5 replies

Userlevel 6
Badge +5

I was thinking about this today - glad you found a solution!

I remember having a similar request shortly after going live. I had some difficulty getting it going and ultimately landed on a subreport to show a “open items not shipped” section at the bottom of the invoice. I can’t remember why we opted to only show invoiced items only and just scrapped the feature altogether.

 

Thank you for keeping this in mind. If you need, I can share with my report and sub report.

   If you adjust the sub report layout properly, it would look like just “in” the master report.

 

Userlevel 5
Badge +3

I was thinking about this today - glad you found a solution!

I remember having a similar request shortly after going live. I had some difficulty getting it going and ultimately landed on a subreport to show a “open items not shipped” section at the bottom of the invoice. I can’t remember why we opted to only show invoiced items only and just scrapped the feature altogether.

Userlevel 6
Badge +5

By the way,
Finally, I get my work done by report and sub report

Userlevel 6
Badge +5

SOOrderShipment will have the shipment number and invoice number and could be used to complete the link between your shipment and invoice lines.

@Neil Cantral 
Hi Neil, Thank you so much. It is working.

select a.TranType,a.trandesc,c.trandesc,b.InvoiceType,b.OrderType,c.OrigOrderType,* 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'

 

I can get the results using something like above, now all I have to do is to modify the rpx.
thank you again.

Userlevel 5
Badge +3

SOOrderShipment will have the shipment number and invoice number and could be used to complete the link between your shipment and invoice lines.

Reply


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