Jim
Yes, I agree with Eric that this is a fun problem.
The magic here will be to have a system which is self maintaining based on the sales history.
What I would do it to create a button which would put the item in "focus". Then that in turn would put any Lines on orders for that item "in focus". That in turn would put any orders that have lines that are in focus, in focus. That in turn would put all lines on those orders in focus. Then those items would light up and can be listed as a report link report on the original record, and the report can be ranked by sales. or by # of lines sold.
Contact me directly if you need help setting this up. It would just take an hour to get this working.
I have a way to do this which ensure full multi user solidity, but conceptually the process is this:
Make a new table with a single record (being record ID#1) which is called Focus Item.
Make a URL formula button to load the item # into a field n that record.
Relate that table to all Order Lines via a reference field with a formula of 1. On the Focus item table make a formula numeric field which calculates to 1. Call it [Item is in Focus (=1)]
Look that up to the Order Lines.
Then make a summary Maximum up to the Order (1 order has many order lines) of that field and call it [Order is in Focus (=1)]. Look that up down to the Order Lines.
Summarize that up to the Item (presumably 1 item has Many Order lines).
So now the items which have that field as = 1 have been ordered in the past. Make some kinf of summary field on the item to know the # lines ordered recently.
Lastly, put a Report link field on your original record to show an embedded report of the items that were ordered with that item, ranked by # lines.
Jim,
Contact me if you need help setting this up. It's a fun little project that I don't mind helping you with, off the clock. Will you be at EMPOWER? Regardless, give me a call or email.
