Tracking Assigned Products & Sales by Distributor
I am looking for best way to track products assigned to certain distributors along with such distributors sales on those assigned products.
We have our line item table related to the products table (via item#) and customer table (via cust#). <Products>------<LineItems>-------<Customers>
Every month we assigned products to distributors abroad. We work with multiple distributors in a single territory. For example, Product A might be assigned to Customer XXX in the UK, Customer YYY in Germany and Customer ZZZ in France. Product B might be assigned to Customer AAA in the UK, Customer BBB in Germany and Product B assigned to Customer ZZZ in France, etc. After we ship inventory to each Customer, they then report back each month on units sold of each Product. We want to be able to track those products assigned to them along with their unit sales, if any. If a product was assigned to a certain customer but there's no unit sales from them on the product, we want to be able to see that (as well as those products they have sold).
How should we setup this up? We thought to create territory fields in the Products Table and use value lists for each populated with each customer in respective territory, but I am not so sure that's right.
The ultimate goal is to (1) show a list of all products during a given month and pick which customers to assigned them to by territory (we work with 5 customers in germany, 3 in France, 5 in the UK, 4 in the Benelux, etc. Once assigned each product then is exclusive to that customer for their territory). And (2) track units sales, if any, on those products assigned.