Hi all,

I have a situation in selecting data for a Crystal report that I am not sure how to tackle, I'm hoping that someone out there can point me in the right direction.

For simplicity, I'm going to boil down my issue to two tables: I am selecting data from PurchaseOrders table which contains the columns: Number, LocationName, AccountCode, etc where the Location column is sourced from a view called vwLocation. Content of vwLocation is LocationName and AccountCode. They are joined on LocationName.

I have a Crystal parametre field created on vwLocation.LocationName so that all PurchaseOrders are returned for the LocationName entered, say LocationName = 'ABC'. That's working fine, however there's a complication: When creating PurchaseOrders, application users can select a LocationName for the purchase order, but then change the value of the AccountCode so that it's different from the one that's created by the vwLocation. For example: the AccountCode for 'ABC' is 258, users create the PO for Location XYZ and enter AccountCode 258.

My Crystal selection needs to change so that it not only selects PurchaseOrders having a LocationName of 'ABC' but also an AccountCode of 258

Now, I can get this working in a SQL Query Analyzer really easily with the following SQL:

SELECT PurchaseOrder.PurchaseOrder, PurchaseOrder.LocationName, PurchaseOrder.AccountCode
FROM vwLocation INNER JOIN PurchaseOrders ON vwLocation.LocationName = PurchaseOrders.LocationName
WHERE PurchaseOrder.LocationName = 'ABC' OR
PurchaseOrder.AccountCode = (SELECT AccountCode FROM vwLocation WHERE LocationName = 'ABC')

In my Crystal report I have a parametre called ?Location which the select expert uses to select from PurchaseOrders. I created an SQL Expression to bring back the AccountCode from vwLocation and added it the the select expert. The last part of the puzzle it to have the ?Location parametre also exist against the SQL Expression... so that the user is only ever prompted for one LocationName parametre. How can I do that? Or maybe you can think of a better way...

You may be thinking: "Just use the AccountCode as the only selection criteria." Unfortunately, the report is designed for end users, who are more familiar with LocationNames than they are AccountCodes and the aim of the game is to keep the end users happy.

I really hope someone can help!