Solved

BAQ OrdHed --> CustCnt

  • 16 June 2022
  • 4 replies
  • 230 views
I

Our CSR’s are neglecting to add contacts to the Order on a routine basis, we used to have BPM’s in place to remind them to add contacts, but at some point (we’re in the cloud and go through a lot of upgrades) they began interfering with core functionality, so we disabled them.

I’m trying to come up with a BAQ so that we can monitor the ATTN fields (OrderHed.PrcConNum & OrderHed.ShpConNum) on open orders for blank values and update them, but I keep getting more rows than I should.

I started out with just trying to get adding the PrcConNum added.

We have 269 open orders but when I add the CustCnt table (Relations: Company/Company, CustID/CustID, PrcConNum/ConNum) I end up in the high 300’s with rows repeated. If I change to unique it goes back to 269, but when I try to bring the CustCnt name field in it shots back up. I’m getting rows for all contacts instead of the one that specifically associates to that order (I thought matching up PrcConNum to ConNum and setting relation for rows from OrderHed might return only what I needed but that does not seem to be the case).

Doesn’t seem like my efforts are headed in the correct direction. If anyone has any insight, that would be much appreciated.

icon

Best answer by lizb 20 June 2022, 16:19

View original
Liz - Epicor Kinetic Cloud w/ ECC Magento Store Integration Admin

4 replies

I

Ah - OK, I get it  now. Thanks.

Liz - Epicor Kinetic Cloud w/ ECC Magento Store Integration Admin
L

You don’t necessarily need to add the ShipTo table.  The issue is that the CustCnt table includes both customer-master-level contacts and ship-to contacts.  So, for one customer, you might have three different contact number 1’s - one associated with the master and one on two different ship-tos. When you join CustCnt to OrderHed, you need to include ShipToNum in your Table relations to narrow your results to the custnum that applies to your order.  That will eliminate possible duplicates/extra results.  The CustCnt table uses CustNum, ShipToNum, and ConNum as key fields, so without all three in your table relations, you’ll likely have duplicates.

Liz Braden ERP Manager CSI Springfield MO
I

I never made it that far. I wanted to make sure that I could first retrieve the correct data for the main customer contacts before adding more tables and complicating things even more.

I had 269 open orders on OrderHed before joining tables. I joined OrderHed to CustCnt (Relations: Company/Company, CustID/CustID, PrcConNum/ConNum) and did not retrieve the same number of records as expected. The BAQ did not retrieve the correct contact based on PrcConCNum on order, it retrieved multiple contacts. No point adding the ShipTo table into the mix at this point, when there is clearly already something amiss.

Liz - Epicor Kinetic Cloud w/ ECC Magento Store Integration Admin
L

You didn’t mention joining on the ShipTo number - do you have that specified in the table relations?  The CustCnt table includes contacts associated with both the customer master and ship to’s.  Ship To Contacts have a ShipTo number, while customer master contacts have a ShipToNum of blank.  I typically join the order directly to the CustCnt table, making sure the ShipTo to CustCnt relation includes the ShipToNum. You can also join the Customer table, ShipTo table, etc, but join them directly to the order table (i.e. don’t link Order to Customer, then Customer to ShipTo in a chain.)

Liz Braden ERP Manager CSI Springfield MO

Reply


Terms & Conditions