Here is what I see you need.
You have a potential 3000 customers that fall into 10 groups, all 3000 potential customer come from one customer you already have. You need to be able to produce the following reports for that one customer and pay him a rebate based on those sales.
- Customer Reports
- Sales by customer
- by customer group
- by item1. Rebate reports
- Rebate = Sales by item x commission %
i. Commission % is determined by Inventory Posting Group of the item
I would set up a new field on the customer table labeled “Rebate Group”. You could try dimensions, or using exiting customer posting groups, but those affect other areas of Navision, probably better to just make a new field. I would also setup a salesperson code for the main customer, and enter it as the salesman for all the rebate customers, this way you can easily identify this customers.
Your customer reports then could all come from existing Navision reports, using the Rebate Group field to group on.
To get your rebate report, I would still use the Item commission group, you can populate it several ways pretty easily.
-
filter on the item card by the Inventory Posting Groups and then use search and replace on the “Commission group field” You search for blank field and replace with your commission percentage. You only have to do this once, so it would not take too long.
-
You could make a dataport, and export you item file, with just the couple of fields, such as Item No, Inventory Posting Group and Commission group.
a. Then use access or excel to update the commission group and re-import the item file
- Use a Processing only report to update the commission group field
a. Dataitem = Item table
b. Code is basically if inventory posting group = x then commission = y, do this for the 10 groups you have. And run the report
Then the commission report uses the value entry table and the item table and customer table. The value entry has all the info, and you link the item table and the customer table, to get the commission % and the customer group.
It will take a lot of work, but it sounds like it is worth it.
If you don’t want to use the commission group field, I would add a field “Commission %” to the Inventory Posting Group” table instead. Then your Rebate commission report could use the value entry table and the Inventory posting group table.
sorry I don’t have more time to spend on this, but I think you need to just set back and take a fresh look at it.