1 Reply Latest reply on Jun 11, 2012 8:31 AM by philmodjunk

    Filtering Value List



      Filtering Value List


      In the Accounts payable portion of the system in question, there are a few tables:

      • AP_Invoice (stores invoices from vendors)
      • AP_Payments (stores payment records made to invoices
      • AP_PaymentItems (stores the items to be made on a payment (similar to LineItems on an invoice)
      On the AP_Payments records, there is a portal showing records from AP_PaymentItems. The relationship allows teh creation of new records in the AP_PaymentItems table. A pop-up menu based on a value list is available in the portal row for the user to select an invoice to include on the payment record.
      The goal is to have the portal show only record from AP_Invoice when / AP_Invoice::cAmountDue>"0" /.
      I have attempted to filter the records in the value list by creating a second relationship between AP_Invoice and AP_PaymentItems:
      AP_Invoice::RecordID = AP_PaymentItems::RecordID and AP_Invoice::cAmountDue = AP_PaymentItems::Zero
      where AP_PaymentItems::Zero is a number field with an auto-enter value of "0"
      I get an index missing error. Any ideas?

        • 1. Re: Filtering Value List

          Index missing occurs because the cAmountDue field cannot be a stored calculation. (It references data in related tables.) Fields that are unstored do not have an index and since Filemaker uses the match field's index to determine which records in AP_Invoice should match to the current record in AP_PaymentItems, it can't find those records in your relationship.

          You'll need to find a way to flag your records that still owe an amount due with a stored/indexed field. You might, for instance, add a "flag" field that is just a number field with a 1 in it if there is still a balance due on that invoice. YOu can use a script trigger to update this field everytime you add or edit a record in payment items or any other field in any other table that might alter the amount due for a given invoice.