By using this website, you agree to our Terms of Use (click here)
Thanks for creating this group.
What we are trying to accomplish is a Backorder report. I have modified the Shipment Confirmation report (we don't email this to customers, we would prefer to add tracking numbers & ship via to the invoice report and email, but that is for a different post).
So what we would like is if only the backordered items were displayed. Currently shipped items are displayed as a quantity of zero. Is there a way to display the backorder quantity only if it is greater than zero?
thank you.
My RPX file should be attached.
You are so close it's scary. On your line where you show the inventory totals, you have the logic that belongs in the "filters" tab of your report. Open the Schema Builder, go to Filters and add:
[SOShipLine.ShipComplete] EQUALS 'B'
This will filter out anything that doesn't have your "backorder" tag on it. Further, if you wish to fully hide 0 quantity items using a filter:
[SOShipLine.OpenOrderQty] GreaterThan 0
Like I said, you had everything you needed for this, but your logic only applied to that single field. If you need to filter your end result for tables, you likely need to do it on the Join Relationships or the Filters tab of the schema builder.
I can see two ways to accomplish what I think you are asking for:
1) Look for all Sales Orders with a status of 'Back Order' (SOOrder.Status). I don't really like this approach. When analyzing back orders, I am typically looking for information at the item level. Looking just at open sales orders help the shipping department but doesn't help with product management.
2) Look for all Sales Order lines with a Shipped Qty > 0 and Open Qty > 0 and Completed = False). (SOLine.ShippedQty, SOLine.OpenQty, SOLine.Completed) This gives you a list of all line items with open back ordered items.
I am a pretty big advocate of Generic Inquiries and Dashboards over reports. As such, I would build my list in a GI showing each SO Line along with whatever header info I needed and then use these as the basis for two dashboard widgets:
- Open back ordered Sales Order count by item (limit to top 10 or 20),
- Open back ordered total quantity by item (limit to top X)
I would build in Navigation from the GI to the following screens:
- Sales Order Entry based upon Sales Order Type and Order Nbr,
- Inventory Allocation Details using Inventory ID and Warehouse
I hope this answers your question.
@shawn-slavin +1 for the flexibilty and scalability of your answer. My answer is a direct solution, but if you have the time or the need for it, Shawn's is a superior solution.
Well, I can't take credit, it was a standard report column within Shipment confirmation.
I got this to work, thanks to your suggestions. I did have to use the SOline in the filters portion of the Build Schema. Also in order for it to work the shipment document has to be confirmed.
thank you for helping me with this.
I will explore the generic inquiry option, it sounds like a better visualization solution.