This brings back memories. I once had a "check register" DB for my own use where I logged each credit card charge as a checking account transaction. When the CC statement arrived in the mail, I'd go to a layout that showed all unpaid CC charges in a portal. I'd click to select each charge in turn until the total of the selected charges equaled/matched the statement. I then clicked a button and all the selected CC items were replaced by a single check entry to the CC company to pay the bill. The nice aspect was that my checking account balance never changed when a CC bill was paid as I'd already reserved the funds to pay it due to the fact that the amounts were individually deducted when I charged the card.
I'd need more detail of how you have this set up in user environment, tables and relationships to be able to answer in detail, but the basic method that you can use is as follows:
1) Define a field _fkCheckID, in the portal's table to store the ID number of the check that will be used to pay off this transaction.
2) Put a button in this field that performs a script that does the following:
If _fkCheckID is not empty, clear the field to disconnect it from the check that paid it. (This is your "oops" feature)
If _fkCheckID is empty, assign it to the ID of the check designated to pay it off (more about that later)
3) conditional formatting can be used to highlight which rows have been selected or layout text can be formatted so that you button looks and acts like a check box field.
Where the details get fuzzy for me is that you haven't described the parent record for this portal of related records. If the parent record is the record for the check you will use to pay the selected charges, then this is pretty straight forward. A portal filter can be used that omits all records unless _fkCheckID is empty or matches the __pkCheckID field of the parent record. That will eliminate transactions that have been paid off by a different check and your script can use:
Set FIeld [Transactions::_fkCheckID ; Checks::__pkCheckID ]
to link a selected portal row to the current check.
If your parent record is from a different table, you'll need to describe your design in order for me to modify this suggested approach to work with it.
Thanks, that does sound familiar. In my case these payments are based on book contracts for various authors and books, so they're triggered at different times and there are frequently complications (multiple authors, multiple books. different splits, etc.). I would like to limit this to selected payments due to a particular recipient (usually the author), otherwise they may be varied. I've attached the basic surrounding table structure.
I hope that helps.
But where is your table for checks so that you can link specific payment records to a specific check?
I'd think you'd want to record a check number and date as well as the amount each time that you do this.
Sorry, I should have explained. The "royalties" table stores the payments. The "payment" table is for all practical purposes the "checks" table -- it essentially contains a payment id field and then a number of calculated fields to determine things like exchange rate, deductions, etc.
So your portal needs to list all unpaid royalties? For a specific Contract?
What I'm picturing is a layout based on Payment with a field for selecting the contract. The portal would show all unpaid royalties for that contract so that you can then click portal rows to select them for the current payment record.
The portal will need a relationship to a new occurrence of Royalties if that concept makes sense to you.
Ideally the portal would like all royalties due for a particular time period (say the next two months), sorted by date. They often come in groups (but often for different books and contracts), and so you may have a ten to process of the 20 or 30 in the list. So in a nutshell I'd like the portal to list the upcoming (due) unpaid royalties for a specific person.
Does that change your second suggestion?
Not particularly, but not all royalties can be paid to the same person can they? I'd think you'd want an additional filter or match field here to keep from accidentally selecting royalties for different recipients for the same payment.
And I'm not sure you really need that specific time period either as it is likely that a portal of all unpaid royalties will automatically produce the list for the time period that you need for your payment anyway.
But using what you've specified,
You can take a new occurrence of Royalties, I'll name it Royalties|Unpaid
and link it like this:
Payments::DateStart > Royalties|UnPaid::date And
Payments::DateEnd < Royalties|UnPaid::date
Then you can use this portal filter:
Royalties|UnPaid::paymentID = Payment::PaymentID or IsEmpty ( Royalties|UnPaid::paymentID )
You can specify a sort order for the portal that sorts by date in descending order if needed to put the oldest records in the specified date range first--which is why you may not find it necessary to specify a date range here.
Your script for selecting/deselecting a royalty for the current payment record would be:
Set Field [ Royalties|UnPaid::paymentID ; If ( IsEmpty ( Royalties|UnPaid::paymentID ) ; Payment::PaymentID ; "" ) ]
IF you find that you don't need the date range, you can use this relationship:
Payments::AnyField X Royalties|UnPaid::AnyField
This makes sense on the whole. I don't, however, fully understand why I need a second instance of the royalties table. Could you explain?
Adding a new table occurrence enables you to set up this different relationship while keeping your existing parts of the system unaffected by the new addition.
Your current relationship matches a payment to all royalties paid by that payment. The new relationship matches to all royalties in a specified date range. They aren't the same relationship. I am assuming that you need that original relationship in order for other parts of your database to function correctly. For example, if you need to see payment info for a given royalty on a royalty layout or portal to royalty on a contract layout, you'll need the current relationship.