Hi All,

I need to create some reports and have decided to use a TypeList and then export the DataTable to CSV files etc.

The first is a customer order & shipping report. Simply this includes an order number, product number, ordered quantity & shipped quantity.

I'm using MS Access and have typical Orders & OrderLines tables with appropriate PK/FK relationships defined on OrderNumber fields. I also have a multi-purpose Activity table which includes amongst other things, shipping transactions.

In MS Access I can define a query which gives me the report I need:

SELECT
  Orders.OrderNumber,
  OrderLines.ProductNumber,
  OrderLines.OrderQuantity,
  Activity.ShippedQuantity
FROM
  (Orders
INNER JOIN
  OrderLines ON Orders.OrderNumber = OrderLines.OrderNumber)
LEFT JOIN
  Activity ON (OrderLines.OrderNumber = Activity.OrderNumber) AND
  (OrderLines.ProductNumber = Activity.ProductNumber)

When I try to create the TypedList in designer I find I can't include the Activity entity because it is not related to the OrderLines table. I can't create a new relation because I have to specify the relationship based on the PK of the OrderLines table. However this is not used in the above query.

Ideally when transactions are added to the Activity table the ActivityId would be the FK of the OrderLines table so there is a 1:1 between the two tables, but as I mentioned above the Activity table has numerous purposes so I have to use left joins to get the data - because of course some order lines do not ship.

Unfortunately i've inherited a legacy system so changes are not possible at this point. Do I still have an option to use TypeLists or do I have to use Dynamic lists and build the predicates myself?

Thanks for your help.

What defines an Activity to be of type shipping?

Good question. I've simplified the query somewhat to explain the problem.

The activity table provides an ActivityTypeNumber. So for instance shipping records have an ActivityTypeNumber of 1.

As I see, you will have to use DynamicList or TypedView. You can write the view in your DB, map it to a typedview and use it in your code.

Ah yes, I forgot about views. As I mentioned the example is simplified and some of the reports will be based on loosely coupled tables so I guess the best bet is to create views for these reports and then use TypedViews with some filtering.

Thanks for your assistance.