1 2 Previous Next 20 Replies Latest reply on Feb 11, 2014 3:46 PM by philmodjunk

    Total Noob drop down search



      Total Noob drop down search


           I am trying to move away from Microsoft Access and start fresh with FM.  This should be a very simple database i would think. I Need A client database and a job database.  Name and address phone number simple stuff for the client data base.  The job data base, I would like to have a auto number like 14-0001 and so on. The job data base will contain multiple fields such as job location and number of photos, and so on.  I think my problem might be with the relationship between client database and job database, but i am not sure.  I will supply a pic of what i currently have.  The big thing i can not figure out is how to have a simple interactive form where the user has a drop down list which contains all the job numbers 14-0001 etc. and when they click on one it displays all the info for that record.  I have seen multiple script examples but it baffles me.  i understand that a the user can click find at the top and then preform find, but there should be a better more interactive way.  Thank you in advance.  Please any help you can give me with my database is much appreciated.  please go easy on me.


        • 1. Re: Total Noob drop down search
          /files/a0a5861f4b/Database2.jpg 717x541
          • 2. Re: Total Noob drop down search
            /files/addcd751fb/Database3.jpg 721x543
            • 3. Re: Total Noob drop down search

                   You have a one to many relationship between client and test and it appears to be set up correctly.

                   I think that you want to select a job Number from a drop down and then see that Test record and possibly data from the linked in Client record.

                   Your Find is the right idea, but it doesn't have to be done manually, You can select from a drop down and then a script can perform a find for that record in the Text table.

                   Define a field with global stroage and set it up with a drop down list of job numbers by selecting the "use values from a feld" option in Manage | Value LIsts.

                   Use a script trigger to perform a find similar to the example scripts found in this thread: Scripted Find Examples

              • 4. Re: Total Noob drop down search

                     Could you help me apply this script to my database.  Maybe explain what each step is doing?


                Go To Layout [Select a layout based on table being searched]
                Enter Find Mode [] ---> clear the pause check box
                Set Field [YourTable::YourField ; Globals::gField]
                Set Error Capture [on] ----> Keeps error dialog from interrupting script when no records are found
                Perform Find []
                If [Not Get ( FoundCount ) // no records were found ]
                    Show Custom Dialog ["No records were found by this search."]
                End IF


                Im most confused by the set field part.  please break that part down  my table which filed??  how do you put in the second part and what is Globals::gfield ???

                Also how best is this script used button? on selection of value from drop down list?


                Thank You

                • 5. Re: Total Noob drop down search

                       When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

                       Think of when you perform a find by hand. You enter find mode and then enter search criteria into the fields of your table as required for you search. Before clicking perform find. If you look at the script, you'll see that the script goes through the same steps. Set Field is your "personal typist" that is taking the data from the global field and entering it as search criteria into fields of your table while your window is briefly in find mode.

                  • 6. Re: Total Noob drop down search

                         i now have a better understanding of set filed  Thank You.  Now how do i use that info.  I would like to be able to select job number from drop down and navigate directly to that record.  Thank you

                         I would like for the info that is display to contain any data i specifiy


                         In the pic my global drop down field is bBarCode. Its name that from another forum i was following

                         I may be making this difficult.  It was so easy to do a query in Access

                    • 7. Re: Total Noob drop down search

                           Set up a different field--with global storage as your drop down. Use the OnObjectSave trigger to perform the above script.

                      • 8. Re: Total Noob drop down search

                             I tried that, and it didn't work Can I send someone my file? and maybe you can figure out where I'm going wrong.  Thank you for all your help.  Im sorry for being so ignorant know its frustrating

                        • 9. Re: Total Noob drop down search

                               Instead, please describe how it "didn't work". Also you have at least one layout based on contacts and one layout based on Test db. On which table is the layout where you tried to set this up based? (What is selected in Show Records From in Layout Setup....?)

                          • 10. Re: Total Noob drop down search

                                 Im not sure how to describe how it didn't work.  Im sure that i'm missing a step  the layout is set to Test Db.  them the script runs nothing happens.

                                 Please bear with me and let me start from the top.

                                 I created two tables Contacts, and Test Db ( which will be named Job Db later ) The Contacts table contains a PK field __PKClients ID its a number and auto enter serial.  the other fields are Client Name, person in charge, address, city, state, zip  The second Table Test Db contains a field named Job Number ( it is auto enter serial ) the next field is Client_ID ( I believe this is called my Functional Key ) the other fields are client name, address, city, state, zip, and location.  I will be adding more later.  The relation ship currently between the tow tables are one to many as shown in a previous post.  

                                 The layouts i am attempting are, first a simple new record the first field will be Job Number ( which will hopefully be auto populated.) the second is client name, address, city, state, zip, and job location.  I would like the Client Name to be a drop down box which contains all of my clients names, and when selected will auto fill address, city, state, zip.  I have had trouble getting that to work with my current relationship setup.  If i change my relationship to Client name - client name it works fine.  Please any thoughts on this, am i doing this correct of should i be attempting this in a different way? 

                                 The second layout which is the one we have been discussing it this post is, Job Number ( which i want to be a drop down box which when selected will show the record relating to the particular job number.  ( job numbers are never duplicated ) The fields should show Client Name, address, city, state, zip, and location.

                                 Thank you in advance, I think with a little help i can get the hang of FM pro.

                            • 11. Re: Total Noob drop down search

                                   Ok I'm looking at a tiny iPhone screen on the way back from the doctor. I suggest removing set error capture from your script and then test it. Let me know what error message pops up. That will be a useful clue. 

                              • 12. Re: Total Noob drop down search

                                     There is no error the script runs and the find screen appears.  It is waiting on me to input criterial and click perform find.  which is what i am trying to avoid.

                                     So i have created a new global field called find ( it is a text field )  then i put this filed on my form and set it to be a drop down box with the value being pulled from Job Number ( is this where I'm going wrong?). Then i tell it for preform script trigger on save.  What happens is it just enters find mode.

                                     Thank you


                                • 13. Re: Total Noob drop down search

                                       Sorry I didn't have the preform find step in my script. I do now and the error is ( The provided find criteria is not valid. Enter a valid request before proceeding)

                                  • 14. Re: Total Noob drop down search

                                          I suggest posting a copy of your script. So that I can zero in on the several different ways you may have a problem that is resulting in that error.

                                         To post a script to 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. (with this approach, you can get multiple script steps on the same line, please edit the pasted text by inserting some returns to separate those steps.)
                                    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.
                                    1 2 Previous Next