Calculate invoice commissions based on commission parties

Doc9

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

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. Works great.

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

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 a 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. :-k

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 | Customer No. 123 | Total Amount(IE $100) | otherfields | commission party 1 | %10.00 | $10.00
INV-bla | Customer No. 123 | Total Amount(IE $100) | otherfields | commission party 2 | %5.00 | $5.00
INV-bla | Customer No. 123 | 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

Thanx

PS
Dynamics NAV 5.1 SQL

Doc9

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.

Doc9

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?

](*,)

David_Singleton

Doc9 wrote:

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.

Create a new table.