3 Replies Latest reply on Aug 9, 2010 10:13 AM by philmodjunk

    Creating Portal That Filters Records

    cameraeyes

      Title

      Creating Portal That Filters Records

      Post

      I am building a database that is made up of two databases, purchase orders and payments.  Customers place orders and then make partial payments against those purchase orders.  I have created a third database called accounts which shows a portal containing purchase orders and their current blance.

      Since I am using FileMaker 11 I can use the filter control in the portal to I am able to create a relationship that allows me show all of the purchase orders and their current balances in a porta.  However, I want to create a portal that only displays purchase orders that still have a balance, i.e., greater than zero.

      I know this simple but am struggling nontheless.

      David

        • 1. Re: Creating Portal That Filters Records
          philmodjunk

          The expression used depends on how you determine an purchase order is paid or not. You may have two fields in your Purchase Orders table, or you may not: InvoiceTotal, PaymentTotal. If you have both such fields then this filter expression:

          PurchaseOrders::InvoiceTotal > PurchaseOrders::PaymentTotal

          Will limit the listed invoice records to those that are not yet paid in full.

          There are other approaches possible that will require a different filter expression on the portal.

          • 2. Re: Creating Portal That Filters Records
            cameraeyes

            PhilModjunk thanks for your response.

            That is exactly what I am doing currently unfortunately as I mentioned payments are not always paid in full so I need a calculation in the Payment database to sum all payments to the same po before making the relationship.  Not sure how to do that.  Right now it is a one to many relationship and it needs to be a one to one.  I know I could run a script to update status but would like to do this without adding any kind of scripting

            • 3. Re: Creating Portal That Filters Records
              philmodjunk

              How are you relating payments to PO's?

              A calculation in your PO table that totals all related payments should give you the total payments that apply to a given PO. If one payment must be split over several PO's you may need to add a "details" table that details how a given payment is to be split over multiple POs and you'd then compute the sum of any such related "detail" records to determine if the PO is paid.

              Something like Sum ( Payments::PaymentAmount) is how you'd compute, in the PO table, the total payments for a given PO. That's what I had in mind for PurchaseOrders::PaymentTotal--which can then be used in the filter expression.