BAQ Adding TrackingDtl to InvcDtl+InvcHead

shetay29 · July 16, 2018, 4:31pm

I suck at tables and connections.
I am using this BAQ to have all invoices from a group (criteria is group ID on InvcHead) show and I will export all this data into excel so i can then copy and paste this into invoices I make via EDI.This has shaved a ton of time off my hands, and does not require me to jump screen to screen in Epicor qith the “open with” every invoice.

It all worked until I added TrackingDtl (and for the life of me can’t figure out how to display the information correctly, I did use ShpDtl I think at first, but had even bigger problems)

some invoices through EDI ask for a tracking number or pack ID (this is why I am incorporating these into the displayed columns)

As far as “Customer” I would like to have:
Sold to Customer Name
Ship to Address
Bill to Name
(I’m unsure of where I can find these to work)

What am I doing wrong? I have previously got the tracking number to show up, but it wont show the Pack ID, or it returns bogus lines for that one invoice…?

gpayne · July 16, 2018, 6:26pm

We use CartonTrkDtl to gather the tracking numbers and weights for our shipment notifications.

shetay29 · July 16, 2018, 7:14pm

I tried that and I am still not getting a pack ID or tracking number…

gpayne · July 16, 2018, 8:33pm

This I think will get you what you want.

If you need more than the first tracking number you need cartontrkdtl. You have to make the join manually. The join would be company=company and packnum=packnum.

then set criteria

Greg

shetay29 · July 16, 2018, 8:50pm

How do I manually join them being company=company and packnum=packnum.

Do I do this with invcdtl=invchead (company and packnum)
and shiphead=invcdtl( company and packnum) ?

I done this minus the invcdtl=invchead (packnum) because invchead didn’t have this option and now I am getting a lot of repeated lines:

Also, the circled connection is not bold?

gpayne · July 16, 2018, 8:57pm

To do a manual join, click on the line between the tables, then in the table relations tab. click the new button and add as below.

The bolding indicates it is not an outer join. it should work as an outer, but there would always be a detail of a valid invoice header.

shetay29 · July 16, 2018, 9:17pm

Okay, do I need to do the manual joins + relationship between just the CartonTrkDtl + ShipHead?

I only need the one tracking number that ShipHead has…

I only have +criteria on InvcHead. Do I need criteria on InvcDtl, ShipHead and CartonTrkDtl?

gpayne · July 16, 2018, 9:21pm

if you only need shiphead’s tracking number then don’t add cartondtl and you don’t need the joins.

My criteria was just to get data out of my system, so you will not need them.

Are you getting the rows you want without extras?

shetay29 · July 17, 2018, 1:19pm

I’m still getting a lot of duplicate lines

gpayne · July 17, 2018, 3:56pm

you can add the InvoiceDtl.invoiceline, PackNum and Packline to see if you are getting the same ship head for each invoice line.

You can also change Shiphead, Customer and Shipto to full joins and first instead of each. This will help speed and avoid duplicates as long as you are getting all of the results you need.

shetay29 · July 17, 2018, 4:04pm

The full joins between ShipHead, Customer, and ShipTo and changing the qualifier to first instead of each for all three solved it.
I will test by invoicing a group and will update if it for sure worked.

I had already used the InvoiceDtl.InvoiceLine and InvoiceDtl.PackNum so I didn’t have to change anything there.

Exciting! Fingers crossed.

shetay29 · July 17, 2018, 6:56pm

It did get rid of the duplicate lines, but I did not notice that it made all the tracking numbers and customer/bill to customer the same until I started to invoice…

Am I doing anything wrong?

gpayne · July 17, 2018, 9:05pm

Take the outer join off from InvcDtl to Shiphead.

shetay29 · July 17, 2018, 9:12pm

I took the outer join off and it’s still pulling the same tracking over (I deleted the customer name/bill to for now) since I was about to invoice another group and didn’t want the duplicated names

gpayne · July 17, 2018, 9:36pm

Is that the whole tracking number? It seems short and I can’t see a way that three different shipheads can come up with the same tracking number.

For the image you uploaded, the 17+ lines will all have the same invoice number and packnum, so all of the tracking, customer, etc. would also be the same.

shetay29 · July 17, 2018, 9:42pm

I should have used a better example, but this isn’t even a tracking number we use.

The first order should have a tracking that starts with 4514etc and the second one shouldn’t even have a tracking number since it was a CPU. for the large order the po should have started with a fedex freight tracking 3837etc.

So I am unsure of where this tracking number comes from (along with the customer “Orshelns” ) we haven’t even used this customer yet (new customer from several months back but have yet to create a order).

gpayne · July 17, 2018, 9:57pm

it seems like the link from invcdtl to shiphead is the issue. I would show the packnum from invcdtl and shiphead to make sure they match.

Is it like this?

shetay29 · July 18, 2018, 1:24pm

I am almost positive that did the trick

I am going to invoice these and make sure I am not overlooking any problems, before marking as solved

Thank you so very much

shetay29 · July 18, 2018, 4:29pm

I have noticed it has not included all invoices, it seems to be the ones with OTS enabled

gpayne · July 18, 2018, 5:15pm

Change the join from shiphead to shipto to an outer join. There is also an option on the customer maintenance to save the OTS as a shipto.