I think the new record would be most convenient way in a new table related to the Worker. This way you cans save on the new record:
Pay time frame [DateStart to DateEnd]
Simple Calculation would check Previous months for negative provisions and empty check# then bring forward to current frame.
Once cleared or accounted for, then place the Check# on all negative months to show "cleared"
Even print on check stub:
1/1/12 - 1/31/12 $-40.24
2/1/12 -2/28/12 $-10.14
3/1/12 - 3/31/12 $200.42
Total commission $150.04
I think you might be able to do this very simply.
If you make one invoice to every worker every month. Then you can add a checkbox to this invoice that you check when that invoice has been "accounted for"
On your invoice you can show a portal that shows other invoices that still need to be "accounted for".
The way to do this is to make a second table occurrence of the invoice table and create a relationship so that only values that are "Not accounted for" are shown trough this relationship.
What you would need to do this is two fields:
- A text field called "AccountedFor"
This would be formatted to be a checkbox with one manual value of "Yes"
- A text field calles "g_Yes" set to global storage containing the value "Yes"
To be sure this always contains the value "Yes" even if you delete all invoices, you could set an Auto enter data of "Yes"; But remember that that only get's filled in the moment you create a new record.
Now you create a new table occurrence of the invoices table (InvoicesNotAccountedFor) and you make the following relationship:
Invoices::g_Yes------[Is Not Equal to sign]-------InvoicesNotAccountedFor::AccountedFor
Then you create a portal on your invoice layout based on the InvoicesNotAccountedFor that shows the invoice number, month, and the total amount.
Then you put a Summary total field outside that portal that shows the total.
You also add your accounted for checkbox. And as soon as you check that checkbox the invoice will be removed from the portal.
If you need any help with this I could add it to the file I made you. That way you could see what I mean.
I updated my example file and added the portal.
The idea is that when an employee has a negative amount in a certain month. The next month you can add that amount to his invoice (the negative amount) so that amount get's subtracted from this invoice amount. If that's added to the invoice, the negative invoice can be checked as "accounted for".
If his person keeps getting negative amounts, you can just let ik keep on going on untill he get's a positive invoice, and then you start checking off previous months.
I am suddenly in a goddamn hurry here in the office, im gonna look your file later at home.
Thank you again for your help!!