10 Replies Latest reply on Jan 17, 2014 2:56 PM by philmodjunk

    (un)assigning related id to filtered (un)selected portal rows

    AdamReed

      Title

      (un)assigning related id to filtered (un)selected portal rows

      Post

           I would like to place a yes/no field in a portal row, and a "submit" button outside the portal so that "yes" records, once the submit button is pressed, will be assigned an id related to a field in another related table.  It's important, however, that if a record is selected and then deselected ("yes", whoops I mean "no"), it be treated as not selected.

           The purpose (maybe there's a simpler way to do this) is to allow a user to assign a number of a payments to a single check.  The payments will appear as portal rows, the user will go through and choose which they'd like to pay with that check.

           Another important part of the problem is that I would like to assign this id only to visible portal rows (and not previously selected portal rows).  For example, if on Monday I display five payments, and select three of them to be paid on Monday's check, and then come in Tuesday and see only the two that haven't yet been paid, and then select both of those to be paid on Tuesday's check, I don't want those that were put on the Monday check to appear again on Tuesday's.  I assume I can handle that by just saying "if a record has an existing payment id, don't overwrite"?

           One last clarification, in case it's helpful, the layout is based on the table which handles the checks (not the table that handled the individual payments).

           Thanks for any help.

           Adam

        • 1. Re: (un)assigning related id to filtered (un)selected portal rows
          philmodjunk

               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.

               See some similarities there? wink

               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.

          • 2. Re: (un)assigning related id to filtered (un)selected portal rows
            AdamReed

                 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.

            • 3. Re: (un)assigning related id to filtered (un)selected portal rows
              philmodjunk

                   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.

              • 4. Re: (un)assigning related id to filtered (un)selected portal rows
                AdamReed

                     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.

                • 5. Re: (un)assigning related id to filtered (un)selected portal rows
                  philmodjunk

                       So your portal needs to list all unpaid royalties? For a specific Contract?

                  • 6. Re: (un)assigning related id to filtered (un)selected portal rows
                    philmodjunk

                         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.

                    • 7. Re: (un)assigning related id to filtered (un)selected portal rows
                      AdamReed

                           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?

                      • 8. Re: (un)assigning related id to filtered (un)selected portal rows
                        philmodjunk

                             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

                        • 9. Re: (un)assigning related id to filtered (un)selected portal rows
                          AdamReed

                               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?

                          • 10. Re: (un)assigning related id to filtered (un)selected portal rows
                            philmodjunk

                                 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.