Good Morning!

I am in our Quebec office, and have been asked a question I do not have the answer for, so I have said I would ask your advice.

Is there a GridView report I can pull from our data that would give me a listing of all products that any given customer has ordered within the last year or so. I would like to be able to then sort this by Report Group, which is a field that we have set up in AR.

Possible?

Thanks very much.

Ordered or purchased?

Here is what I would do:
· Make a simple GridView report from O/E History Detail table. Select fields Cust #, Item #, Description, Qty Shipped (or Qty Ordered, depending on your needs), Ext Price, Date, Lin Type.
· Check the box “Translate coded fields”, so that your Line Type descriptions become meaningful
· Export the resulting table to Excel
· Once in Excel, delete the column “Line Type”. It is no longer useful.
· Go into Adagio A/R
· Click on the Customer icon
· Hit the F6 finder
· Double click on any column heading (first, be sure that you have Adagio security rights to edit columns by clicking in File > User setup > select appropriate user > select “Edit columns”)
· In the window that appears set it up so that in the right-hand window you only have Cust # and Report Group, in that order.
· Click OK
· Click on the magnifying glass in the F6 search grid and you will have a list of all your customers and their respective report groups
· Click on the Excel direct icon. This will export your F6 search grid results to Excel. You will now have 2 Excel sheets open.
· Go to the first Excel table that was generated by exporting from GridView. Go to the first empty column to the right of the exported data, top data row and do a vlookup to bring the Report Groups to this Excel sheet.
· A vlookup has 4 components, the lookup value, the lookup table array, the column offset and a range lookup indicator
· Your lookup value would probably be cell A2, where you have the customer number
· Your lookup table array would be the entire table exported from the F6 finder
· Your column offset will be 2
· Type FALSE as the range lookup indicator.

This will add the column “Report Group” to the Excel sheet that you exported from your GridView. Now you can use all Excel’s features to sort, select, subtotal, etc.

This looks like a lot of steps, but it is a 3-minute exercise. If you need to be walked through any part of this while you are in Quebec, please feel free to call me at (514) 944-4646.

CORRECTION: In your GridView, make a filter to filter out Line Types that you do not want.

If the answer to the question is "Purchased", then select "Adagio {Composite Tables 8.0C}" as the Module and "*SR Trans w Cust" or "SR Trans with Cust, Items" as the table. (You only need the table with the items if you want something like the item description on the View. It will be slower than using just the customer.)

Select the fields you want from the table. You'l probably want to filter out the Tax and Miscellaneous charge lines and use just the regular item lines from the file. ("T", "M" and "L" Line types respectively.)

I think this will be a little more staightforward that Brian's approach for a one-time lookup.

I'd recommend a slightly different approach if you only want to see what was ordered (but might not have been purchased), or you didn't have SalesAnalysis.