SoldTo CustID instead of BTCustID for AR invoices

Epicor ERP 10
1

We would like to show SoldTo CustID instead of BillTo CustID shown on the AR invoices. Anyone has any idea on how to pull the SoldTo CustID from which table?

2

Did you ever get this to work? I’m trying to pull both the Sold To and Bill To Cust ID’s into the AR Invoice.

3

Here is what you can do to add the CustIDs to your AR Invoice.

“Customer” table is already part of the base RDD, and the CustID field is already “included” (this would be your SoldToCustID)… but it is NOT a query field in the SSRS report, but you can add that in Report Builder at anytime.

But to get the BillTo CustID, you’re going to need a custom RDD anyway.

  • If you haven’t already, create a custom RDD.
  • You need to add the OrderHed table (I use ALL CAPS when I add tables so I can easily see which tables were base, and which were added in the custom RDD).
  • You can then add Linked Tables from the ORDERHED table.
  • Add “link” for the BTCustNum Table
  • Under Description Fields… add CustID (and any other fields you may want to reference in your report)

image
image772Ă—445 25 KB

Add a relationship between InvcHead to ORDERHED (on Company & OrderNum)

image

Save your RDD.

In Report Builder… edit your query:

Add ORDERHED as a query table: (my example is below… I called the table “OH”)

LEFT OUTER JOIN ORDERHED_" + Parameters!TableGuid.Value + " OH ON T1.Company = OH.Company AND T1.OrderNum = OH.OrderNum

Then add your fields to the query (like below):

OH.BTCustNum_CustID as BTCustID

Again, Customer Table is already included in the query as T6… so add:

T6.CustID as SoldToCustID

Then add the field to your report… Dataset Properties > Fields > Add > Query Fields

  • BTCustID
  • SoldToCustID

Add them to your report… upload & test.

1 Like
4

Depending on what your end goal is, if you just need to show the Sold To address, you can use the Print Sold To Adress field on the Invoice header. The Sold To, Bill To, and Ship To addresses are already part of the out of the box RDD.

image
image794Ă—109 4.37 KB

Otherwise, @dcamlin has given you the instructions on how to get the CustID (and more) into the RDD.

5

I was able to get it to work with using the linked table. The tricky part was finding the actual field name it was being stored into was BTCustNum_CustID.

1 Like