splitting amounts in a line item by percentage

[stefangs]

i am working on a royalty distribution system and the relationships are becoming confusing. my scenario is this.

i have customers (table) who order one or more products (table).

an order is put into an orders table which points to a line items table with the usual data fields (customer ID, product ID, price, etc.)

the money is split between partners. not all partners share all products. so i created another table of contacts (not customers, but partners). in the products table, i have a portal that shows which partner is related to that product along with a percentage.

example:

customer john buys a copy of a cd and pays $10.00 which appears in the line item.

partners phil and don each hold 50% of that cd and so $5.00 should appear in their statements.

how do i connect my order line items to these percentages? do i need a join table that creates records on the fly via a script?

thanks for any input,

stefan

[Ocean West]

At what point do you wish to compute commissions? At the creation of the line items or once the sale is finalized and payment was made - if that is the case and the invoice is closed then a script that would import or step thru the line items and create a record for each product/partner that calculates the percent as it exist at close of sale -

the commission percentage is a join table between product and partner as each product could have a different percentage between one or more partners.

The commission table would be ID of the partner / the line item w product code / the percent / commission amt / there you can also add things like date it was disbursed or what check you payed out the commission - also too by being a transaction item if for some reason the product is returned and you need to credit back you will need to create another transaction with a negative amount to zero out the commission paid.

By having a commission table you can impose rules like payouts are accumulative for the prior 30 days - or such.

Much more flexible then adding commission amounts to the actual invoice line items.

[stefangs]

thanks for the speedy reply!

so a join table looks easy enough then (at this time of writing ) i see how creating a new table just for reporting purposes would be best.

just to get me started in the right direction.. would you suggest to have a layout where i can set report parameters, then click a go button to create the appropriate records? that seems easiest, but it would mean that these are deleted once i create the next report. if i ever wanted to go back to check on a previous report i might end up with a different set of records, or even with different results, if the percentages change, for example.

if i save all records, on the other hand, i don't see an easy way to recall a particular set of records that was used for the distribution amount.

hope that makes sense.

thanks,

stefan

[Ocean West]

you would create a distribution table - it has the date / partner and you assign existing commissions to this table - it becomes the parent to the children commission records.

So you can pay one check to Phil that includes line items for: item 12345 at 50% commission or $5 and item 6789 for 40% commission for $12 on 1/8 for total of $17,

you could manually add distribution ID to the child commission record by date or some script or manually assign commissions (individual) to a payout distribution.