1 2 Previous Next 26 Replies Latest reply on Feb 26, 2013 12:28 PM by philmodjunk







           I have a list of places in a table along with their adresses, ID and their category. I want to be able to select a category from a drop down list , (or pop up) i.e doctors surgeries and only the doctors surgeries are displayed to the user who can then select which doctors surgery they require. I have trawled the Missing Manual on FMP 12 and this forum but still can't find out how to do this. could someone tell me if this is possible and how to do it please?






           I guess as I never got a response to my question no-one understood what I was trying to say and do. So I'll try to explain it a bit better. I have to say I'm a newbie to FM but I have created a database with forms and reports which I have found fairly easy to do. But I have a problem. In a nutshell, we are a charity which runs a social car scheme whereby volunteer drivers take clients to and from doctors, dentists, hospitals, airports etc. 


           Using the database form I have created allows the booking clerk to select a client (which populates their address and details) and then select where that client needs to go i.e hospital. The problem is the list of destinations is quite long so I want to present just the list of relevant destinations to the booking clerk that they have selected, for instance just hospitals, if thats what they have selected from the first drop down list on the form and not every other destination on the list. I can create the first drop down list no problem but what I can't do is get the deconf drop down to show just the relevant records and have attached a couple of screenshots to show what I'm trying to achieve. 


           Hopefully someone has done this before and knows what I'm trying to do. 




        • 1. Re: Categories

               Set up a global field with your drop down. Use the OnObjectSave script trigger to perform a script that uses the value in your global field to perform a find. The script can switch to a list view layout where the user can click a button in the layout body to select a specific record to bring it up on a layout to view in more detail.

               See this thread for some scripted find examples: Scripted Find Examples

          • 2. Re: Categories

                 Hi PhilModJunk

                 I have tried to set this up as suggested but still can't get this to work. (see enclosed screenshots). It may be I have chosed the wrong fields within the scripting so any pointers would be appreciated.

            • 3. Re: Categories

                   I don't see anything different in your last screen shot from what was in your first.

                   Did you use a global field for the drop down?

                   What script did you create?

                   To post a script here in the forum:

              1.           You can upload a screen shot of your script by using the Upload an Image controls located just below Post A Answer.
              3.           You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF to your clipboard for pasting here.
              5.           If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
              7.           If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional and easier to read format.
              • 4. Re: Categories




                     Thanks for swift reply, I appreciate the help as being a newbie I'm learning as I go so please bear with me. 


                     For clarity, in my example DB I have fields Dest ID, Destination, Address, Address1, Town / City and Post Code. The Destination field is the field that contains the category Airport, Doctors, Hospitals etc which I'm guessing should be the global field as this is the drop down. This is the field I have chosen for the Global Field. 


                     I used the script that you referred to in your link and written by you. (See Below) I now run this script and it seems to want to create a new record as it says no records are present. It also changes every category to the one I selected, i.e if I select Airports it overwrites all the destination records to Airports. So I'm doing something wrong somewhere just can't figure out where!



                New Script
                                    Go to Layout
                [ “Destination” (Destination) ]

                Enter Find Mode [ ]
                Set Field [ Destination::Address; Destination::Destination ] Set Error Capture [ On ]
                Perform Find [ ]
                If [ "Destination::Destination" ]
                End If 


                • 5. Re: Categories

                       Please note that you need TWO fields for the Destination category. One, a non global field that identifies the destination category for each record in your table. The second,global, is the one to set up with this drop down so that your user can select a category for their search and so that your script can use it in the set field step. That set field step should copy the data from the global destination field into the nonglobal destination field, not the address field.

                       I'd guess that you took your original field and changed it to global storage or replaced on your layout with a new global field. Either way, when you select a destination in this field, you see the same destination for every record due to the fact that it has global storage.

                       The script you have posted cannot create new records and cannot modify the data in any existing records as the set field and new record/request steps are executed while in Find mode.

                       The if step, BTW, makes no sense at the moment, but maybe you plan changes to it you have not yet implemented.

                  • 6. Re: Categories


                         Still no joy. I've attached the revised script but I still can't figure why it won't work. Is there an example database somewhere I could look at to understand how the script works in conjuction with the fields?



                    New Script
                                        Go to Layout
                    [ “Destination” ]

                    Enter Find Mode [ ]
                    Set Field [ Destination::Destination; Destination::Category ] Set Error Capture [ On ]
                    Perform Find [ ] 


                    • 7. Re: Categories

                           Is Destination::Category the field you have set up with the drop down list?

                           Is global storage specified for that field?

                           Are Destination and Category specified as fields of type text? (Not number)

                      Go to Layout[ “Destination” ]

                      could be an incorrect script step. Normally, this step will look like this:

                      Go to Layout[ “Destination” (Destination) ]

                           Looks like you have specified a "by calculation" option for this script step and typed in the name of the layout in quotes. If you accidentally selected the "go to layout NUMBER by calculation" option, this step is not changing layouts. It's better to directly select the layout you want from the drop down instead of using the by calculation option option. Not only does the by calculation option require that you enter the exact layout name, the step fails if you later change the name of the layout. If you select the layout name from the drop down, it will continue to refer to the correct layout even if you later change the name of that layout.

                      • 8. Re: Categories



                             Is Destination::Category the field you have set up with the drop down list?  Yes, which points to a custom Value List.

                             Is global storage specified for that field? Yes, with the "Use Global Storage (one value for all records)" checked.

                             Are Destination and Category specified as fields of type text? (Not number) Yes, both set as Text but the Destination field is set as indexed and points at the same custom Value List as the drop down.

                        Go to Layout [ “Destination” ]. This part of the script doesn't allow me to amend this. I orginally selected the Go To Layout from the commands on the left pane when setting the script up and only gives you the option of choosing original layout or select your layout which I then specified. Once selected you are unable to alter it. You can choose the two calculation options below this selection but I didn't.

                        Hope this helps and I appreciate your time and help.

                        • 9. Re: Categories




                               I am still struggling with this problem of being able to display just the chosen category from another table using a drop down list and I really need to crack this. Just to clarify what I'm trying to do. I have a form that has a drop down on it, set up as a value list and points to a category field in another table, called Destinations. In this destinations table, it has an ID, the category, an address, a Town / City and Post Code fields.  What I'm trying to achieve is to get someone who is using the form to select a category, i.e doctors and it then just displays the doctors. They can then choose the required doctor from the ones displayed. Again for clarification the form has a Journey ID, a Surname Field, a Destination Field and Two category fields. All other fields on the form are setup to point at the Destinations Field and draw the information from it.


                               I have followed PhilmodJunk's advice about the Global fields and using scripts but… when I set the Global field it deletes all the data in the category field no matter which way I try it. I have tried to follow the scripting scenario's in the FileMaker Missing Manual book but still cannot get this to work. So could anyone please advise me on what I need to do to get this to work or if I am doing something wrong point me in the right direction. Do I need to set up the Category and address fields in a separate table or do I need to link the category to the relevant address in some other way? Have I set the form up correctly? Is there a step by step tutorial on how to set up scripts that I can follow if this is what is needed? 


                               Any help would be greatly appreciated as I know this can be done but is just frustrating me on not being able to do it. Thanks in advance.

                          • 10. Re: Categories

                                      when I set the Global field it deletes all the data in the category field no matter which way I try it.

                                 That's because you are still trying to use one field where you need two. One NONGLOBAL field identifies each record's category and one GLOBAL field that is separate from the first that is used to select a destination then used by the script to find all records with that destination category.

                                 A field with global storage stores a single value that will be the same no matter which record you are on. That is not a problem when it is a separate search field for use by your script just don't use it to try to identify a specific record's category.

                                 See this demo file: https://dl.dropbox.com/u/78737945/CategoryFindDemo.fmp12

                                 Note: it does everything all in one table as it is not clear to me what purpose is served by having a separate destination table.

                            • 11. Re: Categories


                                   Thanks for the demo. It's doing exactly what I was looking for albeit I have to adapt it slightly. The reason for the seperate form (and subsequent Table) is because it actually records a lot more information about the journey the client is taking, (see screenshot) and I didn't want everything on the one table, both for size and speed. Once I have this working on my test form and tables I will be incorporating it into the real DB, hence the need to get it working on seperate form.

                              • 12. Re: Categories

                                     Take another look at the demo. The same global field with the same script trigger and script is set up for searching the database on two different layouts.

                                     As long as your script switches to a layout based on the destination table before entering find mode, the script modeled in the demo file will work for you.

                                • 13. Re: Categories




                                       The demo file worked fine but I've been struggling with it to populate another form. I changed the script to reflect that it's on a different form from the demo one but when I've selected the data it drops back to the detail form. I then tried to change (and adding) the new form to the bottom of script with the choose layout command but that just stops it working. 


                                       I tried a workaround which was to incorporate this amended demo into the main journey form which allows the user the select the client, the driver, the cost and other relevant items to the journey (all of which are drawn from different tables) and this automatically assigns a job number using the serial number command in the Field options. However, I found that when you select the client and all the other data and then go to select the destination from the drop down this creates a new record and loses the information that has been input and relating to the one you are trying to create, so I abandoned that idea.


                                       In an ideal world I'd like the choose category drop down on form A along with fields Destination, Address etc and when the user selects the desired destination from the drop down the script goes away works this out on the table CategoryDemo but then populates the result in the appropriate fields on Form A somehow. I don't know whether I need to setup another script to copy the data to the relevant fields or whether I can add this into the demo script or I need to do something else. Hopefully this would then record that information and add it to the other details selected (as shown in previous screenshot). Any help / input would be appreciated.

                                  • 14. Re: Categories

                                         It sounds like you have two tasks, not one.

                                         Task 1: Find a record using the specified data selected
                                         Task 2: Use that data to update information in a record in a different table.

                                         The demo file shows you how to do task1. It does not show you how to do task 2.

                                         I'm going to feed back to you my understanding of what you want:

                                         You want to be able to create a new record in Table A that displays an address and other info that describes a destination using data from a table of possible destinations. The user first creates a new record, then selects a Category from the drop down and is presented with a list of destinations from that category. They click a button to select one of the listed categories and this information then appears in the current record of Table A on the user's original layout.

                                         Is that the scenario that you have in mind?

                                         If so, my next post will outline a set of relationships, script and set of layouts to make that work for you.

                                    1 2 Previous Next