Order Tracking (Crystal Reports table join problem)

[rubendeleon]

I am new to Macola and Crystal. I need to create a report that would track every order and sort each record by ship zone, ship to name, item #, and date. I'm currently trying to do such report with the following tables: oehdrst, oelnhst, arcusfil, and araltad. My problem is with the joins. Which fields should I choose to join such tables, what type of join should I use and how?

 

[dgillz]

Please indicate if you are using the Pervasive or MS SQL database, and indicate if you are using ODBC or Native connection to the data, as this can make a difference in the answer.

However in general:

Join OEHDRHST to OELINHST by ord_type, ord_no, and inv_no.

Join OEHDRHST to ARCUSFIL by cus_no

I see no need to join to the ARALTADR as if an alternate address is used, this info is captured on the OEORDHDR.

Use left outer joins on all joins.

Be careful of your data. For example, if you delete a quote and give the quote a lost sale code, then the quote also ends up in order history, so you will need to filter these records out with a selection formula: OEHDRHST_SQL.ord_type <>"Q"

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[MacolaHelp]

Do you still need to do this? If so, you'll have to link the araltaddr via the cus_no, cus_alt_add_no from the order header history in order to get the UPS Zone. You may need to change the join on those files to a left outer in order to get the correct records returned.