9 Replies Latest reply on Dec 27, 2010 9:21 AM by philmodjunk

    Replicating MS Access  - "Find a record on my form based on the value I selected in my combo box"

    RBowering

      Title

      Replicating MS Access  - "Find a record on my form based on the value I selected in my combo box"

      Post

      I would like to have a combo box (drop down) containing the values of the current form/layout and, when I select from the list, the form/layout jumps to that record.

      I'm very new to Filemaker (10) and when I've tried creating a drop down list as an extra field on a layout it simply changes the main value on the displayed record

      It is a customer database with a portal (detail) listing of purchases under it. I like to search for the customer using a drop down list box (like a combo box in MS Access) the highlights what I'm looking for as i type and then 'fetches' or displays the matching record.

      I'm quite good at MS Access but I'm trying to get up to speed on Filemaker because I've got the iPad version too to sync/copy/paste to.

        • 1. Re: Replicating MS Access  - "Find a record on my form based on the value I selected in my combo box"
          RickWhitelaw


          "I would like to have a combo box (drop down) containing the values of the current form/layout and, when I select from the list, the form/layout jumps to that record."

          I believe you want to create a Value List to "service" your drop-down-list. The Value List should be values from your primary key of your table. You should select a second field  in the value list definition and select "Show only values from second field." This will also sort the records by the "second" field.

          RW

          • 2. Re: Replicating MS Access  - "Find a record on my form based on the value I selected in my combo box"
            RBowering

            Rick

            Thanks for replying so quickly.

            I'm not sure what you mean and how the list suggest would be able to take me to the record I want by selecting from it

            I have value lists like you suggest to lookup values from related tables ('products' and 'customers' populate into the sales table), so I have created Value lists with the primary key id and a second plain english value - but i don't know how to implement this and use it as a separate lookup-up and go-to function on a layout

            • 3. Re: Replicating MS Access  - "Find a record on my form based on the value I selected in my combo box"
              RBowering

              ...ps maybe I shouldn't try to replicate a MS Access piece of functionality in a different product, but I have found it to be a simple, intuitive and effective way to navigate to the record I want

              • 4. Re: Replicating MS Access  - "Find a record on my form based on the value I selected in my combo box"
                bumper

                The forum's resident FileMaker and Access expert, PhilModJunk, usually isn't active over the weekend, so you'll have to wait for his interpretation of your request. The following is assuming you are using FMP 11.

                If I am reading your question correctly then what you want to do is to:

                1. Create a value list (File>Manage>Value Lists) based on the field you want to do the find on. (I know nothing of Access, but in FileMaker when you want to view a specific record you do a Find)

                2. Then in layout modes, from the Inspector, set the field as a Control Style "drop down list" and then Values from the name of the value list you just created. Note: if you have just a few few records then set it to show all values from a specific field, if you have a very large data set then the value list will probably be unworkable as describe here. 

                3. Then when you are on the layout in question, from Browse Mode, go into Find Mode (View>Find Mode), select the desired value from the drop down list and hit return. This will give you all matching records for that value. 

                This is the bare bone basics. You can create special find layouts, you can script the process, you can create a special find field and concatenate the first and last name, etc. As I understand Access there a standard ways of accomplishing a task, in FileMaker if you ask three FM developers a question you get eleven different answers. It is a very flexible and forgiving product.

                • 5. Re: Replicating MS Access  - "Find a record on my form based on the value I selected in my combo box"
                  RBowering

                  Hi thanks for your help but clearly I'm doing something wrong

                  First off I'm using  Filemaker pro 10.

                  I tried posting some pictures but it's not happening

                  OK, I already have the value list I want because when I am in the 'Sales' table layout I can select a 'Customer' from the drop down I've created.

                  When I go to the 'Customer' layout I can insert this value list drop down field (like my combo box from Access. I go to 'Find mode' as you suggested. All the fields on the screen then become blank to allow entry of query data; I select my Value list drop down and pick a customer from the list and press 'return' and the entry I've selected goes into the Customer name field on the layout (as it should) but I get the error - "No records match this set of find requests'. Not sure what to do at this point and hopefully my own website will be up so I can paste and link to soem screen shots to illustrate

                  Also (please bear in mind I never used Filemaker before 12th December) do you *have* to switch modes - can't you just find whilst browsing? 

                  • 6. Re: Replicating MS Access  - "Find a record on my form based on the value I selected in my combo box"
                    RBowering

                    I manged to upload some illustrations...

                    This is what I get in MS Access . I can edit and find from the same form using a combo box:

                    MS Access Combo Box find

                    and the steps I took in Filemaker..

                    1. Step one, I created a value list containing my customers and positioned it below the main part of the form (top left picture bit with red box)

                    2. Step 2, go to 'Find mode' and select customer from drop down (middle bit of picture)

                    3. Step 3, although the customer name is valid, I get an error, plus the selected entry is placed in the Customer name field

                    Filemaker drop down selection

                    • 7. Re: Replicating MS Access  - "Find a record on my form based on the value I selected in my combo box"
                      RBowering

                      Derrrrr

                      OK, problem solved I was searching on the second value from the drop down (Customer email) against customer name.

                      Thank you all - still a little frustrated you have to change 'Mode' though

                      if I can ask another question - can the portal part be dynamically sized. I've created it for 6 rows. Can it be made to increase or decrease dependent on the number of linked records?

                      • 8. Re: Replicating MS Access  - "Find a record on my form based on the value I selected in my combo box"
                        bumper

                        Glad you got it worked out. To answer your question about finding records and switch modes, you can set up a self-relationship from a global field to say the table's field last name. Then create a layout with just the global field and a portal pointing to other side of the relationship.

                        Now when you enter a last name in the global in the portal you will get all the records that match on the last name. Put a button in the portal record to go to related record and the main data layout. Now if you enter "smith" in the global you will get all the records that match on "smith". When you see the record you want click on the button in that portal and you will be taken just that record on the layout of your choice. All in Browse Mode. With script triggers you can create a type ahead effect that filters the portal letter by letter.

                        Resizing is possible but only works when the window is resized, not based on the number of records. You can set the object anchors to increase the size as the window is resized. If you window size is fixed portal scroll bars are probably a better option.

                        • 9. Re: Replicating MS Access  - "Find a record on my form based on the value I selected in my combo box"
                          philmodjunk

                          Maybe I should sign Bumper up as my "weekend Relief" Wink

                          still a little frustrated you have to change 'Mode' though

                          Keep in mind that you can create a script to do all this automatically if you decide to stick with that approach. The user simply selects a value in your drop down and a script trigger then kicks in and performs a find to pull up the desired record or records. The mode changes will then take place invisibly to the user.

                          To add to Bumper's info on portal resizing, you can shrink the size of a portal for printing/PDF purposes. So if you need a flexible portal size for printing purposes (where a scroll bar equipped portal might conceal some data from the print out), you can design a print layout with a very large portal and then set it to slide up/also resize enclosing part. And even more flexible approach for printing, is to base your report on the portal's table and add related fields as needed from the parent table. This report is then able to show any number of portal row records without any wasted space or hidden rows. You can find an example of this if you check how invoices are printed with the invoices starter solution.