6 Replies Latest reply on Dec 7, 2009 12:26 PM by comment_1

    Conditional Value Lists



      Conditional Value Lists


      Using FMP 8.  I have an existing DB.  When creating an invoice, the customer info is pulled from the customer table.  Over the years, this table has grown and now I need to limit the choices to just active customers when creating an invoice.  I am doing this successfully using conditional value list.  However, the customer names on old invoices for currently inactive accounts (not in the value list) are now displayed as the primary key Customer ID rather than the customer name. How do I limit the input options to active customers without affecting old invoices?

        • 1. Re: Conditional Value Lists

          How do you distinguish the difference?


          What makes a customer "Active" or "Inactive" in your database records?


          Once we know that, we should be able to set up a method for filtering out the "inactive" records.


          Also, I wouldn't recomend using a name field as a primary key. A customer number field set up with an auto-entered serial number will avoid many problems that can occur when you use a name field as a primary key.

          • 2. Re: Conditional Value Lists
               You need to define a new relationship (using another occurence of the Customers table) to filter the value list, and leave the core relationship Customers -< Invoices as it was.
            • 3. Re: Conditional Value Lists

              The customer table includes a field active_inactive.  Upon creation all customers are set to 'active'. The filter to limit the data entry options to active customers only when creating a new invoice is working accurately using a value list called Active Customer.  However it is changing the customer name on invoices for now-inactive accounts to the customer_ID (the primary key). The filter is working but it may be designed incorrectly to achieve both desired results.


              I must have mis-communicated on the pk - it is the Customer ID (auto entered serial number).


              Sorry, I have inherited this DB and it was created by two designers who are no longer available for consultation.

              • 4. Re: Conditional Value Lists
                   Are you using a pop-up menu field to display the customers name?
                • 5. Re: Conditional Value Lists

                  Yes I am using the pop-up menu field to display from value list Active Customers

                  • 6. Re: Conditional Value Lists

                    Well, then that's the problem - your value list includes only active customers. The field displays the "second field" as defined in the value list - but this field is empty, because the selected customer is no longer included in the value list.


                    You can place the related name field from Customers on the invoice layout (make it non-enterable).