7 Replies Latest reply on Mar 7, 2011 10:24 AM by LarryMadden

    Drop Down List Control



      Drop Down List Control


      I have 2 questions but let me first briefly explain the database format:

      The tables that will be used are:

      Client, Ware#, & Contract

      Contract is the main table that pulls info in from both Client & Ware. I have a control button in the Contract Form (browsing mode) that allows me to first enter new client info.  In in the client form I can enter either First & Last names or Business name with their relative information. Once this information is entered in I then go back to the main Contract form (I wish I knew more about portals).

      To begin the contract, I select the ClientID (generated in the client form).  Here is my first question.  In the client drop down, I currently show the ClientID with the last name (to help select the correct id).  This works fine except for the businesses that have no names associated with the business.  All I see in the drop down list is the ClientID (the last name is blank).  How can I get the drop down list to display either a last name (if one is available) or the business name (if no last name is available)?

      Question 2:

      In a previous forum post I was helped to provide an empty warehouse report based on contract information (contracts for various warehouse units).  In the Contract Form, I have a drop down (based on Ware# table) that gives me all the mini warehouse numbers.  After I select the ClientID, i then select the Ware#.  Here is my question:

      I would like the the Ware# drop down list to only show available (warehouses not currently rented) warehouses.  This protects from writing multiple contracts on the same warehouse.  It also simplifies the contract process.  Is there a way to write code similar to the code written for the Empty Warehouse Report?

      Thanks in advance.

        • 1. Re: Drop Down List Control


          Define a calculation field in contacts that combines as many fields as you need to serve as the 2nd column of data in your value list. Here's one possible approach just to get you started:

          Case ( IsEmpty ( LastName ) ; Company Name ;
                    IsEmpty ( FirstName ) ; LastName ;
                    Lastname & ", " & firstname )

          You can even tack on an address line--which can help distinguish between clients of the same first and last name.


          As I recall, that calculation checks data in the related contracts table as well as references the current date and thus is an unstored calculation. Unfortunately, that precludes it being used in a relationship on which to base a conditional value list of this type. We'd need some other way to flag all available warehouses in an indexed field. Then the conditional value list of just available warehouses can be set up.

          Perhaps you'd set up a simple "Available" field that you log with a 1 or 0 to show whether or not it is available. You'd set up a script to run the first time the file is opened, to check for any expired leases to update those warehouses as available by changing the field from 0 to 1. A second script would need to run (can be controlled with a script trigger) that marks the related warehouse record as available each time you exit the termination date field in a contract record.

          While this takes a bit more work to set up, it can also serve the needs of your original report and due to being an indexed field, will result in much faster sorts and finds.

          • 2. Re: Drop Down List Control

            Where do you enter the case statement?  I guess it would be in the ClientID field of the Contract Form.  I currently use a value list called ClientID (sorry not very creative).  In this value list, I have chosen to "Use values from field:"  I have specified "Client::ClientID" and "Client::ClientLast".  I guess I need to enter the statement you gave me in the "Use custom values" option.  Is this correct?

            • 3. Re: Drop Down List Control

              This not and should not ever be the client ID field. This is a separate text calculation, defined in your clients table that you use as the second column of values in your value list. Thus, you would refer to it in place of Client::ClientLast in your value list definition.

              • 4. Re: Drop Down List Control

                I tried using the custom values option and I can't use that method.

                • 5. Re: Drop Down List Control

                  Yes, custom values will not work for this. Keep the same setup you had originally, and just swap your last name field for this new calculation field.

                  • 6. Re: Drop Down List Control

                    That makes sense.  I wasn't thinking of a new field.  Sorry.

                    • 7. Re: Drop Down List Control

                      Thanks again for your help.