7 Replies Latest reply on Jan 3, 2011 11:43 AM by philmodjunk

    How do I use something like a drop down list to locate a record?

    HowardRathbun

      Title

      How do I use something like a drop down list to locate a record?

      Post

      I have a "data entry" layout to enter/edit records in a table.  The table contains over a a thousand records so I need a mechanism to locate the record to be edited.  The format of a drop down list is OK but that appears to be for data entry.  I want to be able to select or highlight the name in the drop down list and use that input to go to the indicated record.  My list or "index" includes, among other fields, names and record numbers. So when a name is selected, the script can immediately bring up the selected record.  There must be a straight forward way to do this.

        • 1. Re: How do I use something like a drop down list to locate a record?
          philmodjunk

          Drop down list formatting ( and check boxes, radio buttons, pop up menus ) can also be used to find records as well as for data entry.

          First you need to define a field that is separate from the fields defined in your table to hold data. Almost always, you will use field options to specify global storage for this field as this is easier to use with a script to find your records.

          Let's say you've defined a global field, named gFindRecord. Place this field on your layout and format it with your drop down list of values.

          Write this script:

          Enter Find Mode []
          Set Field [YourTable::YourField ; YourTable::gFindRecord ]
          Set Error Capture [on] // keeps dialog box from interrupting script if no records where found
          Perform Find[]

          You'll have to replace YourTable::YourField with a reference to a field and table in your database. If your value list lists the names of individuals, for example, you'd refer to that name field here and set up your value list to list names from this field for your drop down.

          You can then either put a button next to the drop down to perform this script or you can use a OnObjectExit script trigger to perform this script after the user selects a value from the drop down. (For pop up menus, you can use OnObjectModify).

          • 2. Re: How do I use something like a drop down list to locate a record?
            HowardRathbun

            I'm having trouble understanding how this works.  I assume that when you select or click on a name in the drop down list, that name is entered into the gFindRecord variable.  I should then be able to use that value to search my table for that name.  But that doesn't seem to work.

            I don't understand your script:  nothing  is specified for the Enter Find Mode or Perform Find steps. 

            Also, the second step, Set Field, changes the contents of a field, but I don't want to do that.

            • 3. Re: How do I use something like a drop down list to locate a record?
              philmodjunk

              First, it's a global field not a variable. Variables are something else.

              Enter Find Mode[] is exactly the same is clicking the Find button at the top of your screen. It enters find mode but doesn't specify any search criteria--that's why you don't see Enter Find Mode [Restore] here. When you are in Find Mode, set field will not enter data into a field, it enters search criteria. In this case, the search criteria will be the data the user has previously entered in the gFindRecord field. Since Set field has entered all the criteria needed for this simple example, there is no need to specify additional criteria, so Find Record also does not use the restore option.

              Note that this script will not work as written unless you specify global storage for the gFindRecord field.

              Give the script a try and see if it works for you.

              • 4. Re: How do I use something like a drop down list to locate a record?
                HowardRathbun

                After reading your responses VERY carefully, I was able to make it work.  The first practical problem was that my value list is a list of names with many duplications but every name is associated with a unique number.  So I appended the number to each name in the value list using a space as separator.  The pop up menu then showed each name and number as a single entry.  Then I retrieved the response from the gFindRecord field, selected the right most word from the response and used that to control the find.  It worked perfectly.

                I do not see a solution to the next problem however.  I would like to use buttons to select various subsets of my value list and use the same pop up entry on my layout.  I can not see any way to select from different value lists in a script. I could create a separate  pop up entry for each of 5 different value lists but that takes extra layout space, is not very elegant, and is a last ditch, brute force method.  The only way I see it is to make a new value list (with the same name) for each case.  I used a simple find request to get a subset of the original value list, but the pop up menu only used the original value list, not the subset.  So the only way I can see to do this is to create 5 separate value lists (these are tables) and rename them according to what is desired by the user.  Any suggestions would be appreciated.

                I haven't explored the differences between a popup list and drop down list so maybe there is something for me there.

                • 5. Re: How do I use something like a drop down list to locate a record?
                  philmodjunk

                  What you describe is a conditional value list. If you use your script to modify the value in a field that is used as a key to match to records in your table of values from which you get your value list, you can then get a value list that displays different subsets of the total list of values. Instead of different buttons, you can even add a single, global field with a drop down that controls which subset will appear in the list.

                  Custom Value List? This is a tutorial on conditional value lists

                  http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list This is a knowledgebase article on the subject

                  • 6. Re: How do I use something like a drop down list to locate a record?
                    HowardRathbun

                    I'm almost there.  I used the tutorial you mentioned and created a Calculated field in my value list similar to:

                         if(Code = 4; LastName ; "")  where Code and LastName are fields in my value list.

                    As soon as I enter that into the calculated field, the value list responds immediately and correctly.

                    If, however, I replace the constant 4 with a global variable (eg $$abc) and set $$abc from my script, then nothing happens, that is, the value list does not change.

                    My second problem is:

                    The drop down list works perfectly if the value list uses LastName as the selected value.  However, if I specify the Calculated Field as the selected value, nothing works. 

                    I think I understand what is supposed to happen but it doesn't. 

                    • 7. Re: How do I use something like a drop down list to locate a record?
                      philmodjunk

                      If, however, I replace the constant 4 with a global variable (eg $$abc) and set $$abc from my script, then nothing happens, that is, the value list does not change.

                      Which is why you didn't see such an example in the tutorial. References to global variables, global fields and fields from related tables all render the calculation unstored/unindexed and these can't be used as a source of values for a value list as these are actually built from the field's index. You'll need to use the second option, that relies on a relationship instead.

                      This renders your second question "moot" as you won't be able to use the calculation field like the one you've defined here.