1 Reply Latest reply on Nov 9, 2009 1:51 PM by philmodjunk

    Self Join portal summary

    glucas

      Title

      Self Join portal summary

      Post

      I have a database of users with balances due on their account. I have created a calculation CallList_c to equal "Call List" if their balance is >0 as follows: 

       

       If(DuesOutstanding_s>1;"Call List";"")

       

      Dues Outstanding is a sumamry field (I think herein lies my problem): 

      If(DuesBilled_s - DuesPaid_s=0;0;DuesBilled_s - DuesPaid_s)

       

      Since all of these are summary flelds and cannot be indexed, I believe this is the reason the portal comes up empty. 

       

      Can someone suggest a better approach. I just need to list all members in a portal that have balances greater than zero. 

        • 1. Re: Self Join portal summary
          philmodjunk
            

          When we don't have a description of your table and its fields, we end up guessing and could easily guess wrong--which could leave you with a less than optimum suggested solution.

           

          Judging from the summary fields you described, it sounds like you have a "register" type table where one record = one transaction (either a bill or a payment) for a given individual.

           

          You then have summary fields that are used compute the total billed, total owed and total paid.

           

          You are correct that you can't use an unstored field as the key on the "child" side of the relationship. I think you'll need to either add a an additional table or use a different table that you've already created in your database.

           

          Here's one possibility:

          Create or use a table where each record represents one and only one member. (Surely you have a table that servers as a member list with the typical contact fields?)

          Relate the tables:

          Membertbl::MemberID = BillingRegister::MemberID

           

          Now create a calculation field in Membertbl:

          Sum(BillingRegister:: Dues Outstanding)

           

          Perform a find with "> 0" entered in the above calculation field to pull up a list of all members with unpaid dues.

           

          BTW, you can simplify If(DuesBilled_s - DuesPaid_s=0;0;DuesBilled_s - DuesPaid_s) to just DuesBilled_s - DuesPaid_s and get the same results.