In BAQs, I have been unable to figure out how to relate records in the customer table to either invoices or orders. I need to know how to set up relationships to get billing customers, ordering customers and destination (of shipment) customers for each of invoices and orders. Customer service and sales reports all require differing customers from time to time. The customer table is so complex, I’m afraid I need some guidance to use it.
Thanks in advance.
Isnt it just a link between customer number?
One complexity with customers (as well as suppliers) is that there is a “hidden” piece of data… the Customer NUMBER field is hidden, and never shown to the user… The customer ID field is what people see.
All the tables that link to a customer use the customer NUMBER to link the tables. Why? so that a secondary feature will easily work. You are allowed to change the customer ID without worrying about loosing all historical linkages.
So, when linking your Orders, Invoices, Packslips, etc to a customer, always link using the customer Number.
BUT WAIT, there’s More: You Order/Invoice could also point to a BILLING customer number (as opposed to the ORDERING customer number). so you will be able to see both columns of data in the invoice table. It really depends on what you are trying to find before you make the decision on which to link to… if you are trying to find out how much each customer ordered, then you need the customer… but if you want to find who paid for it, then you need the bill to customer column.
ALL THAT SAID… when you display information, you want to include the Customer ID in your BAQ so that your users can access the data.
That’s fine. But as I understand it, the Customer table contains various types of customer records; e.g. who pays for the order, who placed the order, who receives the order. Accessing that table to retrieve the appropriate records has been difficult for me.
On the other side of the join, which invoice or order fields should be linked to the customer table to retrieve the desired results. For a table this complex, there must be a paper or users guide somewhere that outlines how to use this table.
CustomerTableJoinMatrix.xlsx (9.5 KB)
I have uploaded an Excel table that attempts to document the possible joins and what they might yield. I am seeking confirmations or corrections on this excel table. This is the result of two weekends of trial and error attempting to figure this out.
Any help would be appreciated.
Thanks
You will need to pull three instances of the customer table on to the BAQ if you want to retrieve the different customers associated with the order.
join one to the order/inv on custnum, one on bill to custnum (btcustnum) and one to sold to custnum
There is probably a cleaner way of achieving this, but this is how i do it and seems to work fine
One helpful thing in BAQs that people don’t always know… when you join the OrderHed to the Customer table, there is a little “Dictionary(2)” button… this is telling you that there are two possible dictionary join options pre-defined. You can choose which one to use… here is the view when linking OrderHed to Customer:
The separate instances works nicely. Also, thanks for pointing out the existence of the soldtocustnum field. I had missed that.
This little feature is useful as well. I will keep my eye out for it from now on. Sincere thanks.
