Calculate commissions based on commission parties

Business Central/NAV Developers Forum
1

Hi all I am having some trouble with a current development task. Here is the scope of the project.

I have successfully created all of the following:

I have created two tables. Customer commissions and commission parties, customer commissions stores the customer no. commission party (lookup to commission parties – a table storing the names of parties for unification and easy lookup) and commission % in decimal form.

I have also created a simple matrix form to show customers who have parties and their associated commission percentage.

All of this works fine and I have two things left to complete. I am more of a .Net and Web programmer so I am having trouble with these next two initiatives.

Challenge 1: I am creating an live excel workbook which shows a matrix of percentages and payouts based on selections of parties and date ranges. This is currently coming along fine. In fact better then #2.

Challenge 2: I now need functionality in NAV to show all invoice headers repeated for each number of associated commission parties based on filter of customer no. from invoice header.

I really need input on the next steps for challenge #2. I don’t need to write the values to a table (unless it makes things much easier) I just need to show open invoices and commissions based on commission parties and customer no. I have created a running balance flow field (I know, I know performance, it’s been years and its been fine) on the G/L entry table using a flow filter. It calculates fine and shows a running balance. About two years ago I remember working with test code and just though table view was able show a repetitive entries based on a document number but I don’t really remember how I was able to do it.

I don’t really know the best way to go about this.

In other words what would be the best way to use a form to show all open invoice numbers with the percentage of commission paid out based on invoice amount. So if no commissions are paid out there would be one line with commission party blank and a % field blank and a commission dollar amount as blank. And for the next invoice lets say it has 3 commission parties; so it would show 3 lines one for each commission party IE

INV-bla | Total Amount(IE $100) | otherfields | commission party 1 | %10.00 | $10.00

INV-bla | Total Amount(IE $100) | otherfields | commission party 2 | %5.00 | $5.00

INV-bla | Total Amount(IE $100) | otherfields | commission party 3 | %2.50 | $2.50

I know if I could get the data together I can handle the math and working with the data; I’m just stuck on how to put this piece together.

I’m sorry if this comes across as a no brainer but I’m still learning J

Thanx

PS

Running on Dynamics NAV 5.1 SQL

2

What do you mean by commision paid out based on invoice amount, i thought its based on customer and customer parties associated with that customer.

When you say open invoices do you mean posted invoices but still they are open and need to be applied or invoices that still on invoice header and not yet posted.

I am not sure where we are going with this exactly and what are your business requirements exactly but to answer your concern about how you will use a form then here it goes.

If you are connecting this with invoice header then you can create a form which will act as subform but will be opened from menu items of invoice header form. Same as if you are openning dimensions form from invoice header form. So you will have to create one table and a form with fields to fill this

INV-bla | Total Amount(IE $100) | otherfields | commission party 1 | %10.00 | $10.00 and you need to here customer no. as well.

Then you have to link this form with invoice header form as i mentioned earilier and when you will click the menu button on the invoice header form , you need to write code here to fill your new table with values based on commission parties table you have created already.

But if this is about open invoice entries that are posted but not applied yet (open entries) then scenario will be different. Here you will create two tables and two forms. First form will have some serial no. & customer no.(header form) The second form will be same as subform created above. Connect these both forms as header and subform. On header form when you will choose customer no. write code to fill the subform same way i did mention above.

I think your buiness requirement is to add commission to each open invoice or what?

3

I have been doing testing with customer ledger entry table (table 21) but when i view the table i see only the first result from commission party relation, if i could get it to show a line for each commission party in my form, on a temp table or a table it self i would be able to write the code for the commission calculations.

4

This is what i meant.

Open invoices that are open are from customer ledger entries. You will not add customization on customer ledger entry table right? don’t do that there. Do what i have mentioned above. Create new table and form and write code to bring associated comission parties and what ever fields you want.

5

Yes i understand, i will not modify table 21 cust ledger entries. I am having trouble with how to bring the data to the temporary table(for example), i have been able to use copy rec but i still only see one line for the entry. I need it to show this one line for each number of associated parties and then use the parties comission % to calulate from the amount field.

6

As i mentioned earlier that also you have to create function, you have to do some coding to bring all the open invoices of a customer in question and for each line you will access your customer party table and if there are 3 records you will enter these 3 lines in your table and calculate what ever you want. Then your loop will go to next invoice and repeat that. (This is what i understood anyways). Its all just a matter of choosing right procedure and writing code here thats all.

7

Sorry i did not see your other post;

I should not have said commissions paid out, it should have said commissions to be paid out.

Let me try to explain again. I really need some help, thanx.

Assuming that I am using the invoice header. On the invoice header through the customer no and relation to customer commissions table(custom) we know what associated commission parties are linked the to customer no and what percentages.

Currently when I use a lookup to the customer commissions table I see only the first entry for that customer no. regardless if there is one or five.

How could I create a single table box to show five lines with some invoice header information and if there are five commission entries how do I repeat the invoice header and show the associated commission party and calcs for each line?

8

I have already suggested this before.