6 Replies Latest reply on Jun 4, 2010 9:17 AM by user14040

    If my drop down menu is too long how do i get the information plus add a new record.

    user14040

      Title

      If my drop down menu is too long how do i get the information plus add a new record.

      Post

      My Experience: Newbie

      Version: FileMaker Pro 10 Advance

      OS: Windows 7

      Published To: DB is used on desktop company and I also use FMTouch to transfer the DB to my Ipad.

       

      How do I?

       

      Table Names; Customer, Equipment & Field Report

       

      My DB  tracks  serial numbers of equipment, I link the serial # to customer.  On a field report layout I have a drop down menu that has a list of serial numbers. When using the serial number drop down menu and you pick a serial number all of the information for that serial number and customer automatically populate the fields in the field report layout.

       

      The problem is the list of serial numbers is too long,  How do I search for the serial number in the field report layout, have it fill in the related information and create a new record?

       

      Right now I am able to create a new record, use a drop down menu to select a serial # have all the related information populate automatically.  

       

      I would rather do a search for the serial # then have it populate the related fields as well as add a new record with the related information.

       

      Doing a regular search will only bring up a previous record.

       

      Thanks

        • 1. Re: If my drop down menu is too long how do i get the information plus add a new record.
          ninja

          Howdy Lawfour,

          Welcome to the forum.

           

          First, your dropdown is based on a value list.  in the Field Control/Setup (in Layout mode) you can check "Autocomplete using value list" and have the list shorten as you type.  That can make it more manageble.

           

          Second, you can have your value list be based on your ID#, but show a different field on screen.  In you value list setup, check "also use values from second field", choose the field, and choose "show values only from second field".

          By doing this, you can choose the customer by name, but have the ID# put into the field upon your selection thereby fulfilling your relationship.

           

          Is this what you are trying to do?

          • 2. Re: If my drop down menu is too long how do i get the information plus add a new record.
            philmodjunk

            One approach to managing long value lists to make it a conditional value list. You select a category in drop down #1 and only the serial numbers that are a member of that category then appear in the serial number drop down.

             

            Here's a thread on setting up conditional value lists:

            Custom Value List?

             

            Another option is what you have described. You can enter text in a search field and a script searches the database to find the relevant record and then uses data from that found record to populate fields in another table/record. If you precede that with a New Record/Request step, you'd have what you describe.

             

            Here's a thread on such a scripted method. It searches by name, which you might want to consider, but it could also be adapted to search by a serial number.

             

            Tutorial: How to use an auto-complete drop down list when selecting records related by ID number

            • 3. Re: If my drop down menu is too long how do i get the information plus add a new record.
              user14040

              tried doing the conditional value list and I tried using the search script, could not get the script to work at all.  maybe i created the db wrong.

               

              this is how i have it set up.

               

              i have three tables

               

              Field Report, Equipment and Customer

               

              This is how I have the relationship connected.

               

              Field Report Table - equipIDfk [number]

              connects to 

              Equipment Table - EquipID [primary key] 

               

              Equipment Table - InvestIDfk [number]

              connects to

              Customer InvestID [primary key]

               

              I use the serial number field from the Equipment table in a drop down value list to import data from the Customer table and Equipment Table.

               

              Everything worked fine until I ran across the problem trying to find a serial number because the drop down list was too long.  I wanted to be able to either type the serial number into a new record and have it populate the related information about the customer and equipment or have only the related machines show up in the value list so the drop list wont be as long.

               

               

              I am guessing I might have designed my database wrong.

               

              Do I need three tables should I have just one or two tables and put everything in the two table.

               

              The customer table has the normal information [name, address, phone, email etc].   Equipment table has everything that relates to the equipment [model, serial, expiration date etc.]

               

              The field report pulls information from customer and equipment table and then it has it's own fields

              [problem with machine, resolution, parts needed, price etc.]

               

              I guess the biggest question I have is should the 3 tables be 1 or 2  is the database designed right or do I just have to fine a script to work to search for serial numbers in the field report.

               

              Thanks

              L

               

               

               


               

               

              • 5. Re: If my drop down menu is too long how do i get the information plus add a new record.
                philmodjunk

                I don't see anything wrong with your basic table structure and relationships. You have the following relationships, if I read your last posts correctly:

                 

                Equipment::equipID = Field Report::equipIDfk

                Equipment::InvestIDfk = Customer::InvestID

                 

                "I wanted to be able to either type the serial number into a new record and have it populate the related information about the customer and equipment or have only the related machines show up in the value list so the drop list wont be as long."

                These aren't mutually exclusive options, you can have both.

                 

                I assume that you are trying to create a new Field Report record and need to select the correct Equipment record by either entering it's ID or selecting it from a drop down. To have the info from the Equipment and customer tables automatically populate, you have two options. You can define matching fields in Field Report and use the Looked up value auto-enter option to copy the data from these two tables automatically each time you enter an equipment ID value in Field Report::equipIDfk. This records the current data at the time you create the Field Report record and is likely what you want here. Another approach is to dispense with the matching fields in the Field Report table and just place the fields from Equipment and Customer directly on your Field Report layout. When you select or enter a serial number in the EquipIDfk field, the matching data automatically appears. This is easier to set up, but if changes are made to the equipment or customer tables (say a piece of equipment changes ownership) these changes automatically appear in your field report and I don't think you want that.

                 

                If you want to shorten your list, you can add a relationship and then set up your relationship so that you first select a customer, and then this value is used to "filter" your equipment list down to just those serial numbers that belong to that customer.

                 

                Make a new table occurrence of Equipment, EquipmentByCustomer and link it in to your current table occurrences this way:

                Field Report::InvestIDfk = EquipmentByCustomer::InvestIDfk

                Set up your value list to draw values from EquipmentByCustomer::EquipID, Specify Field Report as your "starting from" table occurrence.

                 

                Now you can format FIeld Report::InvestIDfk as a drop down listing all your customers (You can make this a two column value list with InvestID as the first column and the customer's name as the second.) You select a value in this field first and then your equipment ID list should be limited to just that equipment that is owned by that customer.

                • 6. Re: If my drop down menu is too long how do i get the information plus add a new record.
                  user14040

                  Thanks For your help, I think I got it i had to make additional relationships for the customer names and serial numbers to show up on the form.  But it does now, going to do some testing over the weekend.

                   

                  Thanks again for your help.

                  L