Is there a table available that I can use in Gridview that will give me a list of the items shipped and the price paid for a specific customer in order by item?

What are the columns?

Cust #
Cust Name
Item #
Item Description
QTY
Price Paid
Inv/CN #
Inv/CN Date

Hi Susan

There are three options. In order of preference:

1) SalesAnalysis Transactions - it's the easiest table to work with and you can easily edit a transaction if you have to. The downside - you have to pull the customer name (and possibly item description) from a linked table.
2) OrderEntry History Header/Details - has a complete set of data, but you have to filter out completed orders, and change the sign of credits to negatives
3) OrderEntry Sales Header/Details (current) - if using the History table takes too long, but the data may not go back very far - same problems as the History table.

Steve

Hi Steve,

1) To use the SalesAnalysis table, I assume I would have to have the SalesAnalysis module and be retrieving data to it? I don't currently have SalesAnalysis.

2) I tried OE History Detail and it takes more than 1 minute to load a customer's history. Other than being slow, it's what I'm looking for.

3) We only keep current for 1 day, so I don't think this would work. We need the details from Order history.

Hi Steve,

To get the Customer name from AR use:


To get the Item Description from IC use:

You don't have to type in these formulas. Just select Edit | Insert related column or press alt-F8.

Hi Susan,
The trick is to create a customer index view that acts like a filter to see your customer data. You can do finders by customer # and Name to find the customer just like in AR.

Then link that customer index without or without a filter to the data, and the selection of the customer as a view link (from the separate index view)speeds up the loading of customer data.

Save both views in a workspace.

As Steve suggests, Sales Analysis is the preferred table.

Best,
Brian

Susan - you are correct, you have to own SalesAnalysis. Besides being an easier table to deal with when writing custom reports, it has (at least) one other major feature. It is the only place you can go to create a report that shows paid invoices BY ITEM. This is crucial if you are paying commissions based on collections and not based on billings.

Andrew - I consider those formulas to be using linked tables. While not difficult to learn how, it is one step more advanced than selecting fields from the driving table.

Steve

Hi Steve,

I misinterpreted your post to mean that you needed separate Views into the AR and IC masters to get the customer names and item descriptions, presumably using GETLINKEDVALUE() function calls. Closer reading shows that probably wasn't what you meant.

However, there are lots of tables in Adagio that just contain the customer code or item number, so getting comfortable inserting the related information is a good skill.

Attached is a workspace on the SalesAnalysis Sample Data which demonstrates Brian's linking suggestion and how you would select the items purchased by one customer and sort them by date descending.

Thanks all. I am comfortable with inserting related columns. We use that feature often.

Brian, when you say that you can speed up loading the data with a linked view, don't you still have to load the data for it to work with the link?