In building a report for deliveries in and out of town we have used “Territory” as or main record selection and sort option. We are running into a problem with customers who have more than one “Ship-to” address which in turn each of those need to have a different Territory from the main customer (billing address) territory. Both of these Territory fields are available in the forms in Receivables and Order Entry and can be filled in individually. However, we are running into a snag in finding the “Ship-to” territory in any of the tables that are currently available to us in Crystal Reports. Can someone please tell us where this field is (in which table - does it possibly have an Alias name assigned to it?) or what we need to do to have this field pull into our report?

We appreciate any help or advice that can be given to help us through this.



Cheers and Happy Holidays,

For the current versions the ShipTo data, including territory, is in the AR90W Contact/ShipTo table.

If you are building a report for deliveries, why are you not using the Territory field on the order/invoice itself? This is loaded from the Customer or ShipTo record as appropriate.

Doug, the AR90W tables have no relationships set up to be able to bring them into the report with the OEW65 tables and therefore I am still unable to pull the "Ship-to" territory into the report. Do you have any suggestions on this?

Thanks,
Laureen

What is the main driving table in this report?

The main tables are pulled out of OEW65 Orders and Invoice Headers. The problem is we have additional "ship-to" address territories in the AR table that I need to allow us to pull into the report as well.

That sounds reasonable. But why not use the Territory field on the Invoice header as Andrew suggested? I have a feeling that you have a good reason, but it's not obvious at the moment.

Hi Laureen,

If you are using the current versions of Adagio Order Entry (8.1D) and Receivables (9.0B), you can use Crystal Reports for Adagio to create a report based on the 'OE Orders and Invoices Headers' file, and link to the 'AR Contacts/Ship to' file to print information from the master record, such as Territory. The connection requires 2 fields, both the Customer code and the Ship-to code.

When adding both of the above tables to a Crystal report, the Visual Linking Expert requires 2 links:
OE Orders table AR Contacts/Shipto table
Customer number -> Customer no
Ship to address code -> Contact code

Performing these links connected the tables for me.

If you only need the Territory code that was read from AR ship-to address when creating the OE Order (as Andrew and Bruce alluded to), then this link and extra table is not needed. If you wanted to have both fields (ie for comparison where the Ship-to Territory is different than the Territory on the order), this is one example of needing the master record information.

At this time we are still running OE 8.1C and Receivables 9.0B - could this be the problem of the links not working?

I tried your suggestion anyway and this is the error I get on the Customer Number -> Customer no:
"The link you added causes a link configuration with multiple paths leading to the same table. You may want to cosider reversing the links."

When I try reversing the links I get this:
"Invalid file link. Not consecutive index fields."

When trying to make the link for Ship to address code -> Contact Code I get the same:
"Invalid file link. Not consecutive index fields."

For whatever reason if I only use the Territory from the OE table it does not bring in any customers who have subsequent Ship-tos which are stored in the AR90W Contact/Shipto table. Here is an example of this:

Shipping to Fred in Prince Albert (territory 6 - setup in - OE-Customers-Contacts/Shipto Tab)
Billing goes to Fred in Saskatoon (territory 10 - setup in - OE-Customers-Invoicing Tab)
Fred also has 3 other ship-to addresses in territories 4, 5 and 8 which also do not pull into the report.

I hope this makes sense?

In response to the latter part of your message, what you are seeing (or not seeing) makes sense given the tables you have selected. The report will show the Territory from the header, not any other Ship To's belonging to this Customer.

Maybe there's another way to do this. What is your report designed to do? What do you want to see on the report?