Is the customer contact stored in the sales order? You have to choose a contact in order to select a shipping address, but I don't see a way to get to the contact name in a report.
Hello Stephen
What report are you looking at?
I'm trying to create a report that lists all sales orders for a date range along with the name, email, phone number and address of the contact that was selected in the Ship-To field of the sales order. But I haven't found a field linking the sales order and the contacts. I used the Sales Analysis 1-4 container as my starting point.
I think you will need to head over to the connector module, Steve. The joins in the canned container (Sales Analysis) will not give you what you need.
I've looked in the help at the field listing for JrnlHdr.dat and can't find anything linking back to the contacts. I also noticed that if you choose a contact/ship-to other than the default when entering a sales order or invoice, it doesn't display that contact when you re-open the transaction. So I suspect that the transaction doesn't hold a link to the contact table.
Yes, you will need to add the contacts table and possibly the address table, Steve
That's what I was trying to do. The problem is, I don't see a field in JrnlHdr.dat to join to contacts.dat.
give this a try, Steve
LEFT JOIN "Contacts" ON "Customers"."CustomerRecordNumber" = "Contacts"."CustomerRecord"
Then if you are wanting Contact Address stuff, add this >>
LEFT JOIN "Address" ON "Contacts"."AddressRecordNumber" = "Address"."AddressRecordNumber" AND "Address"."AddressTypeNumber" = 0
*Community Hub is the new name for Sage City