By using this website, you agree to our Terms of Use (click here)
Has anyone been successful in adding the tracking information from the boxes tab of a shipment to the SO invoice form and if so how did you map the fields?
Hello nheuer!
I've edited mine extensively, but let's assume you've included the SOOrder table in your joins on the invoice report and start from there, so we don't bloat this post:
Additional Table Join #1:
SOOrder LeftJoin SOOrderShipment
Relations:
OrderNbr
OrderType (prevents collisions should you have two types with identical numbering schemes)
Additional Table Join #2:
SOOrderShipment LeftJoin SOPackageDetail
Relations:
ShipmentNbr
From here, you should have access to all your line level items shown under: Shipments -> Packages. If this is not what you needed, let me know and I'll try to dig around a bit more for you. This unlocks fields such as: SOPackageDetail.TrackNumber and SOPackageDetail.CustomRefNbr1.
I ran these joins in a Generic Inquiry as a test (we don't use the packages tab in my company). If for some reason the report writer won't accept the logic above, I'll edit my invoices report directly and send you screen shots, just let me know if anything is amiss.
I added the table:
SOOrderShipment LeftJoin SOPackageDetail
I am getting the fields I want, but it duplicates the line item multiple times. Does this have something to do with the field relationship settings?
So I made some progress by adding
- SOShipment - Left Join - SOPackageDetail
- Parent Field - ShipmentNbr
- Link Condition - Equal
- Chil Field - ShipmentNbr
But now, it repeats lines based on how many packages there were in the shipment.
In the attached test example, there were (4) packages.
Just make sure you do it as a Subreport since there could easily be more than one Package for a given Shipment.
I need to do the same with both the Invoice and the Shipment Notification. Were you able to develop the sub report and if so, could you attach your SO Invoice and the sub report?
If it is helpful for anyone, I successfully made a subreport showing package tracking numbers. I used this subreport on the Shipment Confirmation Report SO642000
1) Create a SubReport
2) Add SOPackageDetail table
3) Add Parameters
- ShipmentNbr
4) Add Filters
- SOPackageDetail.ShipmentNbr = @ShipmentNbr
5) Add Detail Section & Header
Format it to the size you want to display on your main report.
5) Add to Main Report (My example is Shipment Confirmation SO642000)
6) Add Report Name
7) Add Parameters
Now, if anyone knows how to format the tracking numbers to:
"Tracking1, Tracking2, Tracking3, etc."
Instead of:
"Tracking 1
Tracking 2
Tracking 3
etc"
Please let me know!
You have to wonder why Acumatica has not included this in the main form . With a check mark in Sales Preference area to allow users to use the feature out of the box.
One to many relationship.
Sometimes we ship as many as 25 packages for one shipment so there is no room to list all tracking numbers. Since most carriers (UPS and FedEx, at least) will group multiple packages using one tracking number, we just show the tracking number from carton 1. We did it as follows:
On the invoice:
Establish a parameter "One" in order to designate the carton number...
Join to Package Detail and carton 1...
Use this expression on the invoice:
=[SOPackageDetail.TrackNumber]
We also list =[SOShipment.ShipVia_description] in order to display "FedEx Ground" or whatever.
@rpiester did you add these SOShipment details to the invoice report using a subreport?
@rpiester how did you join SOShipment in the report? I only see the join from SOShipment to SOPackageDetail
We are trying to pull in the Ship Via Description and Ship Date to AR Invoice AR641000. We were using SOShipVia_description, but sometimes we have to change the ship via on the shipment, so if that happens it is not an accurate description.
Adding the master tracking number would be a bonus after we figure that part out.
We are doing this on the SO Invoice. I think we never figured out how to do that on the AR Invoice. We pretty much only use the SO Invoice in daily operations and with customers.
That being said, when I look at the joins on my SO invoice, it looks like it's possible to get this in the AR Invoice. See those relationships below.
I think you could join SOShipment to ARTran (using ShipmentNbr)and then SOPackageDetail to SOShipment. For the latter, we only need line one of the detail to get that master tracking number so that is joined with ShipmentNbr and SOPackageDetail.LineNbr equals @One.
Good luck.
