7 Replies Latest reply on Dec 22, 2012 9:15 AM by rounakjain

    filtering portal on the basis of portal row records in another portal

    rounakjain

      Title

      filtering portal on the basis of portal row records in another portal

      Post

           http://dl.dropbox.com/u/9738427/Screen%20Shot%202012-12-21%20at%2012.07.23%20PM.png

           http://dl.dropbox.com/u/9738427/Screen%20Shot%202012-12-21%20at%2011.43.51%20AM.png

           Customer has many invoices.
           Invoice has many line items.

           Earlier, there was no "Find" Table. I used a layout based on "Customer Table" to act as the ledger account for all customers. It had a find box, an invoice portal and a line table portal. The find box presented a drop-down list of customer names. All the invoices relating to that customer were seen in the Invoice portal. On clicking a particular field in the Invoice portal, the relevant line items were seen in the line item portal. Then I thought it did not make sense to:
           1. base the layout on Customer Table since no records are to be modified or created. The ledger account is only for viewing purposes. (However, I do use a delete portal row button in Invoice portal to delete invoices)
           2. click on a particular field in the Invoice portal to see the relevant line items. I should be able to click on the invoice portal row itself to see the relevant line items.

           With this in mind, I created the "Find" table. It has find box, an invoice portal and a line table portal. Ignore the fact that some variable names start with "g" in the "Find" table, they are not global. The gFind field presents a drop-down list of customer names. All the invoices relating to that customer are seen in the Invoice portal. I then attached a script trigger to the portal's OnObjectEnter. The script sets the Find table's ActivePortalRowInvoice field to the Invoice Id of the currently selected portal row. The line item portal is filtered such that Line Table::LTInvoiceID= Find:ActivePortalRowInvoice. So I get the relevant line items correctly. So everything is working as before.
           The problem--
           There is a summary field "Balance" which shows the running balance in the invoice portal. It disappears whenever i select a row in the Invoice portal. It appears back if I commit records. It never disappeared in my earlier setup when my layout was based on Customer Table. How to make it not disappear? If I "commit record", i loose the selection of current portal row. This disturbs me since I prefer to navigate the portal via the keyboard (I have a script set for OnLayoutKeystroke).


            

        • 1. Re: filtering portal on the basis of portal row records in another portal
          philmodjunk

               Summary fields used to summarize values in a portal like this often have screen refresh issues. You may be able to add a commit records step to your script that selects and invoice to force the needed update.

               Or you can use a relationship instead of a portal filter to control what line item values appear in the portal. Then a calculation field using the Sum function can compute the line items total. Come to think of it, you can define that field in your invoices table to show an invoice total and just use that, even with your filtered portal as it can evaluate from the context of each invoice record.

          • 2. Re: filtering portal on the basis of portal row records in another portal
            rounakjain
                 

                      Summary fields used to summarize values in a portal like this often have screen refresh issues. You may be able to add a commit records step to your script that selects and invoice to force the needed update.

                 As I wrote earlier:

                 If I "commit record", i loose the selection of current portal row. This disturbs me since I prefer to navigate the portal via the keyboard (I have a script set for OnLayoutKeystroke).

                 

                      Or you can use a relationship instead of a portal filter to control what line item values appear in the portal. Then a calculation field using the Sum function can compute the line items total.

                 What i want to display depends on the currently selected row in the invoice portal. Also, I already have the sum function in my invoice table which sums all the line item subtotals and add value of freight field present in invoice table to show total amount on one invoice. How is that field supposed to help me with this problem?

            • 3. Re: filtering portal on the basis of portal row records in another portal
              philmodjunk
                   

                        If I "commit record", i loose the selection of current portal row. This disturbs me since I prefer to navigate the portal via the keyboard (I have a script set for OnLayoutKeystroke).

                   But your script can save the current portal row in a variable and return to it after committing the record. And you can use a global variable to disable your onObjectEnter script from being performed and producing a loop.

                   

                        What i want to display depends on the currently selected row in the invoice portal.

                   And your script that is trigger when you select a portal row can set a field to the InvoiceId field such that your portal of line items then lists only those line item records without filtering. This then creates a situation where a calcualtion field defined in the layout's table can use Sum to compute a total of a field from the portal records.

                   

                        How is that field supposed to help me with this problem?

                   Isn't a total of the line item records for the currently selected invoice what you want? If you want the line item total without the additional charges, construct a calculation field that only totals the line items. And this is only one of two suggested methods that you might use with a Sum function to get the total values.

              • 4. Re: filtering portal on the basis of portal row records in another portal
                rounakjain
                     

                          Isn't a total of the line item records for the currently selected invoice what you want?

                     Please see this:

                     http://dl.dropbox.com/u/9738427/Screen%20Shot%202012-12-21%20at%2012.07.23%20PM.png

                     The records under "Debit" already give me that total. Its the records under the field "Balance" that disappear. The field balance is the running total of invoice totals belonging to a particular customer.

                • 5. Re: filtering portal on the basis of portal row records in another portal
                  rounakjain
                       

                            And you can use a global variable to disable your onObjectEnter script from being performed and producing a loop.

                       This helped me solve my problem. Many thanks PhilModJunk.

                  • 6. Re: filtering portal on the basis of portal row records in another portal
                    philmodjunk

                         You might also consider this approach to what you are doing. It may work out as a simpler approach if you do not need to edit data in the invoices portal: Need layout solution for nested portals...

                    • 7. Re: filtering portal on the basis of portal row records in another portal
                      rounakjain
                           

                                You might also consider this approach to what you are doing. It may work out as a simpler approach if you do not need to edit data in the invoices portal: Need layout solution for nested portals...

                           Such a neat solution. Vow!!