11 Replies Latest reply on Sep 3, 2013 12:36 PM by philmodjunk

    Irritating scripted Find problem

    basilisk2

      Title

      Irritating scripted Find problem

      Post

           I have two scripts, both of which use the same line of code in the Find command. However, both scripts find different results. In both cases I am doing a find for clients with an account number using '*' as the search term, and that are active (set in Browse mode using a A radio button checkbox thing that is validated to accept only Yes or No from a value list as answers). The field is formatted as text.

           Is there a way to copy and paste the two scripts so I can put them on here for you to see? I understand my explanation is not very clear, but I cannot easily find any reference to conducting a Find using what other databases such as MySQL would call an AND find. Finding information to match two different fields.

        • 1. Re: Irritating scripted Find problem
          philmodjunk

               The table occurrence context--determined by what layout is current at the time your scripts are performed can be a factor.

               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. (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.)
          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.
          • 2. Re: Irritating scripted Find problem
            basilisk2

                  

                 Thanks for your help. Here is a clearer description of the problem.

                  

                 Database Start page: Layout = "Main Index" based on Table "Client Info"

                 On Layout Enter: run Script "Index"

                  

                 Script: Index

                   
            •           Enter Browse Mode
            •      
            •           Go to Layout [ “Main Index” (Client Info) ]
            •      
            •           Show All Records

                  

                 Click on Button "Client Reviews"

                 Layout "Review Months" based on Table "Client Info" opens

                 OnLayoutEnter script trigger runs Script: "Reviews"

                  

                  

                 Script: Reviews

                   
            •           Enter Browse Mode
            •      
            •           Go to Layout [ “Review Months” (Client Info) ]
            •      
            •           Perform Find [ Specified Find Requests: Find Records; Criteria: Client Info::SQ Account No: “>1” AND Client Info::Is a Client: “=Yes” ] [ Restore ]
            •      
            •           Sort Records [ Specified Sort Order: Client Info::Review Month Num; ascending Client Info::Client Name; ascending ] [ Restore; No dialog ]
            •      
            •           #Go to Preview mode for earlier versions which do not support summary reports in Browse mode.
            •      
            •           If [ GetAsNumber ( Substitute ( Get ( ApplicationVersion ); "."; "x" ) ) < 1000 ]
            •      
            •           Enter Preview Mode [ Pause ]
            •      
            •           Enter Browse Mode
            •      
            •           Go to Layout [ original layout ]
            •      
            •           End If

                  

                 To show only those records from a specific month I click on Globals::gMonth drop down box on Layout "Review Months" based on Table "ClientInfo" with Script Trigger OnObjectModify run Script "Select Month"

                  

                 Script: Select Month

                 called using Script Trigger on Modification of Globals::gMonth in Layout "Review Months" based on Table "Client Info"

                  

                   
            •           Enter Find Mode [ ]
            •      
            •           Set Field [ Client Info::Review Month; Globals::gMonth ]
            •      
            •           New Record/Request
            •      
            •           Set Field [ Client Info::Is a Client = "Yes" ]
            •      
            •           Set Error Capture [ On ]
            •      
            •           Perform Find [ ]
            •      
            •           Sort Records [ Specified Sort Order: Client Info::Review Month Num; ascending Client Info::Client Name; ascending ] [ Restore; No dialog ]

                  

                 Field Globals::gMonth takes its values from Value List "Month" which contains names of months in date order with wildcard character '*' at the top of the list to show all months. The problem is, when I select '*' I expect to see only those clients that have an account number greater than one, AND where ClientInfo::Is a Client = Yes. What I see though is a list that includes all clients with an account number, even if they have ClientInfo::Is a Client = No.

                  

                 Where am I going wrong?

            • 3. Re: Irritating scripted Find problem
              philmodjunk

                   In your last script, you generate TWO find requests with different criteria in each. The first specifies a month and the second specifies that Is a Client = "Yes". When yo specify different criteria in two different requests, you are setting up an OR search: find the records that match the criteria in the first request OR those that match the second request.

                   That's what is happening here. To fix it, remove the New Record/Request from your script so that both the month and the "yes" is specified in the same find request.

              • 4. Re: Irritating scripted Find problem
                basilisk2

                     Yes, that is what I am trying to do but I can't find the correct syntax to do that. In FMP Help and in the forum I have only found the Set Field, New Request, Set Field method and with the second of these removed the script no longer filters on the month?

                     Where should I add the two Find requests, and how to I make sure they include only those records that also match the month specified in the drop down box for the globals?

                • 5. Re: Irritating scripted Find problem
                  basilisk2

                       The problem is with the Select Month script when using the wildcard * character to select all months. Filemaker does not allow me to use SetField for two fields at the same time when in Find mode, so I do not see how your suggestion would work? I also cannot see the function Get (CurrentDate) anywhere in the function list to add that in either.

                       There is NO documentation I can find anywhere on the Filemaker site to show how to do an AND find by scripting (quite a few manually using Quick Find). Even Google cannot show me anything. I can find searching for the same text in multiple fields, but not different text in different fields.

                       Help!!

                  • 6. Re: Irritating scripted Find problem
                    philmodjunk
                         

                              Where should I add the two Find requests,

                         Your script already creates two find requests. The problem is that you need only ONE find request. As previously posted, remove the New Record/REQUEST step from your script so that your find logic is AND instead of OR.

                         

                              The problem is with the Select Month script when using the wildcard * character to select all months. Filemaker does not allow me to use SetField for two fields at the same time when in Find mode,

                         You use two set field steps exactly as you have written, one for each field. You only need to delete an extra scrtip step from your script to get it to work. The modified script would look like this:

                           
                    •           Enter Find Mode [ ]
                    •      
                    •           Set Field [ Client Info::Review Month; Globals::gMonth ]
                    •      
                    •           Set Field [ Client Info::Is a Client = "Yes" ]
                    •      
                    •           Set Error Capture [ On ]
                    •      
                    •           Perform Find [ ]
                    •      
                    •           Sort Records [ Specified Sort Order: Client Info::Review Month Num; ascending Client Info::Client Name; ascending ] [ Restore; No dialog ]

                         Note that it is exactly one step shorter than the original.

                          

                         For more examples of scripted finds, see this thread: Scripted Find Examples

                    • 7. Re: Irritating scripted Find problem
                      basilisk2

                           I made the changes you recommended, and I still have the same problem - the result of running the Select Month script with an asterisk as a wildcard is that the second Set Field script step seems to be ignored. At least, I end up with records which are not clients as well as those which are?

                      • 8. Re: Irritating scripted Find problem
                        philmodjunk

                             What layout are you on when you run the script? WHen you open Layout Setup, what is selected in "Show Records From". If you are not on a layout based on Client Info (Text selected in drop down must read Client Info), then the difference in table occurrence context could explain the results that you are getting.

                             Also, what data type is the Is a Client field? If it is a calcualtion field, what result type is specified? Should be text.

                        • 9. Re: Irritating scripted Find problem
                          basilisk2

                               I found the problem! The first line of the Select Month script needed the "Specify Find Requests" checkbox to be ticked to carry out the find:

                               Enter Find Mode [ Specified Find Requests: Find Records; Criteria: Client Info::SQ Account No: “>1” AND Client Info::Is a Client: “=Yes” ] [ Restore ]

                               Now it works and I am happy! Thanks for your help. I may have unticked this box when hacking things about to try and get it working before and not reticked it. Thanks for explaining the difference between an AND find and an OR find though, that was very helpful. smiley

                          • 10. Re: Irritating scripted Find problem
                            basilisk2

                                 Also, the script still works with only one Set Field script step to filter by month. I still don't fully understand the Filemaker logic, but at least I have learned something new today. Thanks.smiley

                            • 11. Re: Irritating scripted Find problem
                              philmodjunk

                                   The first line of the Select Month script needed the "Specify Find Requests" checkbox to be ticked to carry out the find:

                                   Then your script would, IMO, bet better off written like this:

                                     
                              •           Enter Find Mode [ ]
                              •      
                              •           Set Field [ Client Info::SQ Account No ; “>1” ]
                              •      
                              •           Set Field [ Client Info::Review Month; Globals::gMonth ]
                              •      
                              •           Set Field [ Client Info::Is a Client = "Yes" ]
                              •      
                              •           Set Error Capture [ On ]
                              •      
                              •           Perform Find [ ]
                              •      
                              •           Sort Records [ Specified Sort Order: Client Info::Review Month Num; ascending Client Info::Client Name; ascending ] [ Restore; No dialog ]

                                   There's nothing inherently wrong with using stored find criteria, but it makes reading and analyzing a script harder and more time consuming as you have to open up a dialog to see what criteria is being specified for the find where the set field steps list the criteria right in the script editor.