1 2 Previous Next 18 Replies Latest reply on Nov 30, 2012 9:16 AM by philmodjunk

    I need help! Script to search database

    mnrk

      Title

      I need help! Script to search database & if record is found, to bring up a message, if not to start a new record? Please help!

      Post

           Hi guys & gals,

            

           I need some help with a perform find script, I want to type in a registration number of an aircraft (my brother is a plane spotter) and when I tab out of that field I want it to search the database to see if he'salready seen it, if so I want it to bring up a error box to say, You have already seen this aircraft, Would you like to 'Go to record' 'Enter a new record' OR cancel. 

           If you click 'go to record' it'll take you to that record

           if you click 'Add a new record' I want it to use that registration number and input it automaticly?

           Please can anyone help, I've been searching the internet for weeks trying to get this database sorted for my brother, I am no expert on databases and scripts and really could do with a little help?

        • 1. Re: I need help! Script to search database & if record is found, to bring up a message, if not to start...
          philmodjunk

               Set up a field with global storage for entering the observed registration number: Globals::gRegNumber

               Perform this script:

               Enter Find Mode [] --> Clear the pause check box
               Set Field[Planes::RegistrationNumber ; Globals::gRegNumber ] ---> This step will not work unless gRegNumber has global storage specified
               Set Error Capture [on]
               Perform Find[]
               If [ Get ( FoundCount ) // a plane record was found]
                 Show Custom Dialog [ "You have already seen this aircraft"]
               else
                 Show Custom DIalog ["Registraion Number Not Found. Create a new record?"]
                 If [ Get ( LastMesageChoice ) = 1 // default button was clicked]
                    New Record/Request
                    Set Field [Planes::RegistrationNumber ; Globals::gRegNumber]
                 End If
               End If

          • 2. Re: I need help! Script to search database & if record is found, to bring up a message, if not to start...
            mnrk

                 Thank you Phil, I have quickly gone through the steps you have mentioned but it's still not finding a record I have created as a test. It's saying no record found. I think i'm missing something myself, not you. I'm a complete novice at scripts but i'll keep at it. Anyway, my battery is about to die as we've had a power cut due to the flooding in North Wales, I'll have another look at it afer work tonight or tomorrow and see if I get any sence then.

                 If not, would there be anyway of copying and importing a script?

                  

                 Many Thanks,

                  

                 Michael

            • 3. Re: I need help! Script to search database & if record is found, to bring up a message, if not to start...
              mnrk

                   Thank you Phil, I have quickly gone through the steps you have mentioned but it's still not finding a record I have created as a test. It's saying no record found. I think i'm missing something myself, not you. I'm a complete novice at scripts but i'll keep at it. Anyway, my battery is about to die as we've had a power cut due to the flooding in North Wales, I'll have another look at it afer work tonight or tomorrow and see if I get any sence then.

                   If not, would there be anyway of copying and importing a script?

                    

                   Many Thanks,

                    

                   Michael

              • 4. Re: I need help! Script to search database & if record is found, to bring up a message, if not to start...
                philmodjunk

                     Set Error Capture should prevent the "no record found" message from appearing.

                     Make sure that you enter the registration number into a field that has global storage specified or this script will not work.

                     Since a registration number includes letters and numerical digits (I think), make sure that both registration number fields are of type text, not number.

                     If all else fails you can post your script.

                     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.
                2.      
                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.
                4.      
                5.           If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
                6.      
                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. (Use the HTML option on the database tab panel and paste the text into the forum's HTML editor.)
                • 5. Re: I need help! Script to search database & if record is found, to bring up a message, if not to start...
                  mnrk

                       Thank you for all your help Phil, here are the screen shots of the script and fields I've created. 

                  • 6. Re: I need help! Script to search database & if record is found, to bring up a message, if not to start...
                    mnrk

                         Here is a copy of the database report.

                          

                          

                          
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
                                             Overview                                                     File Options
                                             Tables                          1                                                     Default custom menu set                          [Standard FileMaker Menus]
                                             Relationships                          0                                                     When opening file
                                             Layouts                          2                                                     Login using                          Account Name; Account= Admin
                                             Scripts                          2                                                     Switch to layout                          Off
                                             Value Lists                          1                                                     Perform script                          Off
                                             Custom Functions                          0                                                     When closing file
                                             Accounts                          0                                                     Perform script                          Off
                                             Privilege Sets                          0
                                             Extended Privileges                          0
                                             File Access (in / out)                          0 / 0
                                             FileMaker Data Sources                          0
                                             ODBC Data Sources                          0
                                             Custom Menu Sets                          0
                                             Custom Menus                          33

                    Tables

                                                                                                                                                                                                                                             
                                             Table Name                                                                         Occurrences in Relationship Graph
                                             Aircraft                          
                    9 fields defined, 5 records
                                        
                                             Aircraft

                    Fields

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  <td style="border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: solid; border-right-style: solid; border-bottom-style: solid; border-left-style: solid; border-top-color: rgb(136, 136, 1
                                             Table Name: Aircraft - 9 Fields
                                             Field Name                          Type                          Options                          Comments                          On Layouts                          In Relationships                          In Scripts                          In Value Lists
                                             Date                          Normal, Date                          Auto-Enter:                          
                                                    
                    •                                    Allow editing
                    •                          
                                             Validation:                          
                                                    
                    •                                    Only during data entry
                    •                          
                                             Storage:                          
                                                    
                    •                                    Repetitions: 1
                    •                               
                    •                                    Indexing: All
                    •                               
                    •                                    Index Language: English
                    •                          
                                        
                                                                        
                                                    
                    •                                    Input Form
                    •                               
                    •                                    List
                    •                          
                                        
                                             
                                               
                                        
                                             
                                               
                                        
                                             
                                               
                                        
                                             Airport                          Normal, Text
                    • 8. Re: I need help! Script to search database & if record is found, to bring up a message, if not to start...
                      mnrk

                           Sorry, looks like I pasted too much of it! Here is just the script from the report. I really appreciate all your help :)

                            

                            

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
                                          Script Name                     Search
                                          Run script with full access privileges                     Off
                                          Include In Menu                     Yes
                                          Layouts that use this script                     
                                            
                                     
                                          Scripts that use this script                     
                                            
                                     
                                          Script Definition
                                          Script Steps                     
                                                 
                      •                               Enter Find Mode [ ]
                      •                          
                      •                               Set Field [ Aircraft::Registration Number ]
                      •                          
                      •                               Set Error Capture [ On ]
                      •                          
                      •                               Perform Find [ ]
                      •                          
                      •                               If [ Get ( FoundCount ) // a plane record was found] ]
                      •                          
                      •                               Show Custom Dialog [ Title: "Registration Number Not Found"; Message: "Would you like to create a new record?"; Buttons: “Yes”, “No” ]
                      •                          
                      •                               If [ Get ( LastMessageChoice ) = 1 // default button was clicked] ]
                      •                          
                      •                               New Record/Request
                      •                          
                      •                               Set Field [ Aircraft::Registration Number ]
                      •                          
                      •                               End If
                      •                          
                      •                               End If
                      •                     
                                     
                                          Fields used in this script                     
                                                 
                      •                               Aircraft::Registration Number
                      •                     
                                     
                                          Scripts used in this script                     
                                            
                                     
                                          Layouts used in this script                     
                                            
                                     
                                          Tables used in this script                     
                                                 
                      •                               Aircraft
                      •                     
                                     
                                          Table occurrences used by this script                     
                                                 
                      •                               Aircraft
                      •                     
                                     
                                          Custom Functions used by this script                     
                                            
                                     
                                          Custom menu set used by this script                     
                                            
                                     

                            

                      • 9. Re: I need help! Script to search database & if record is found, to bring up a message, if not to start...
                        philmodjunk

                             I see two problems:

                             1) your registration number field in Aircraft has global storage specified. This won't work as it keeps you from recording different registration numbers in each record of the aircraft table. You need TWO fields, one that is global and used for specifying a possibly new registration number for searching and one that is NOT global used to record individual registration numbers.

                             2) Your set field steps are incorrectly set up. If you compare what you have to what I posted to what I suggested, you'll find that set field has TWO parameters separated by a semi colon. THe first parameter is the target field and the second is the "calculated result"; You've only specified one of the two parameters in your set field step, this is understandable given that you do not have the two fields needed in order for this to work as I mentioned in 1) above.

                             PS. If you drag the mouse over the script in your database design report, you can copy it to the clipboard and then paste it to Post A Answer in this forum instead of adding a screen capture of it.

                        • 10. Re: I need help! Script to search database & if record is found, to bring up a message, if not to start...
                          mnrk

                               Hi Phil,

                                

                               Does that mean he needs to type in the registration number twice?

                               Sorry to be so dumb, I bought fm thinking it would have been easier a proper program but seems im wrong, obviously not as technominded as i thought i was!

                                

                               Many Thanks,

                                

                               Mike

                          • 11. Re: I need help! Script to search database & if record is found, to bring up a message, if not to start...
                            philmodjunk

                                 You enter the registration number once into the global field. If a record with that value is not found, he then has the option to create a new record to log this registration number. If he clicks the button for that option in the custom dialog, the script moves the data from the global field into the registration number field in the Aircraft table for him.

                            • 12. Re: I need help! Script to search database & if record is found, to bring up a message, if not to start...
                              mnrk

                                   Hi Phil,

                                    

                                   This is as far as i've got, it seams to now search, but i'm confused what/where I should put this global field? I'm not sure what I should be doing with it? Currently i've put it on the layout but when I type a new record to demo whats working i'm getting the my test registration on every page in the database ie Global Registration Number 12345. 

                                    

                                   The script seems to find that one record? Even if I put 54321 as a Registration Number?

                                   Here is the script so far!

                                    

                              • 13. Re: I need help! Script to search database & if record is found, to bring up a message, if not to start...
                                mnrk

                                      

                                     Search
                                     Enter Find Mode [1
                                     Set Field [ Aircraft::Reaistration Number Aircraft::Registration Number_Global
                                     Set Error Capture [ On I
                                     Perform Find [I
                                     If [ Get ( FoundCount) I/a plane record was found]
                                     Show Custom Dialog [ Title: !!You have already seen this aircraft!!; Message: !!Would you like to cancel?!!; Default Button:
                                     Yes”,Commit: “Yes”; Button 2: “No”, Commit: “Yes”
                                     If [ Get ( LastMessageChoice) = 1//default button was clicked]
                                     New RecordlRequest
                                     Set Field [ Aircraft::Registration Number
                                     End If
                                     End If
                                • 14. Re: I need help! Script to search database & if record is found, to bring up a message, if not to start...
                                  philmodjunk

                                       Make sure that Aircraft::Registration Number does not have global storage specified. It was changed to global earlier and from what you describe, it still is.

                                       Then you will need to re-enter registration numbers into this now, non-global field in each aircraft record as you lost that data when you changed the storage option to "global". If you have a back up copy made before you made this change, you can use it with this script instead and save yourself some data entry.

                                       Your buttons and script message don't match the logic of your If step, BTW, you ask the user "would you like to cancel", but if they click "yes", the script does not cancel, it creates a new record. With that wording, the If step should check for button 2 rather than button 1.

                                       Set Field [ Aircraft::Registration Number

                                       is incomplete in your post. It should read:

                                       Set Field [ Aircraft::Registration Number ; Aircraft::Registration Number_Global ]

                                  1 2 Previous Next