13 Replies Latest reply on Dec 7, 2014 2:43 PM by erolst

    Scripting a Find where I need to reject certain values

    planteg

      Hi,

       

      I am struggling in creating the steps in a script to look for records where a date in a range - that part works fine - AND where a text field is different than a litteral string.

       

      Since Perform Find can only use literal values, I have tried:

       

      Find does not work.jpg

       

      If I disable the second Set Field Name, everything is fine. But when enabled I get "No record match this find criteria".

       

      How the heck should I write the script ?

       

      Thanks

        • 1. Re: Scripting a Find where I need to reject certain values
          wimdecorte

          Your Set Field calculation results in "1" (True) so you are asking FM to look for records where EntryClient = 1

           

          not "CDE syst-M" = 1

          because you are really asking

          not getasboolean("CDE syst-M")

          and

          getasboolean("CDE syst-M") = 0

           

          why not use

           

          <> "CDE syst-M"

          • 2. Re: Scripting a Find where I need to reject certain values
            erolst

            Use

             

            Enter Find Mode [ ]

            Set Field [ TimeEntry::WeekDate ; … ]

            Omit Record/Request

            Set Field [ TimeEntry::EntryClient ; "CDE syst-M" ]

            Set Error Capture

            Perform Find

             

            As an aside: it looks like you're copying the client's names into the TimeEntry table; as ususal, it's better to use an ID.

             

            You can then simply search the appropriate field in the related Client table, i.e.

             

            Enter Find Mode [ ]

            Set Field [ TimeEntry::WeekDate ; … ]

            Omit Record/Request

            Set Field [ Client::name ; "CDE syst-M" ]

            Set Error Capture

            Perform Find

            • 3. Re: Scripting a Find where I need to reject certain values
              wimdecorte

              One approach that I frequently use is to do the find in two steps:

               

              - do the search that produces the smallest found set first (either the range or the omit)

              - then do a contrain find on the other criterium

               

              On reasonable found sets the perfermance impact is negligable and it makes for very easy troubleshooting.

              • 4. Re: Scripting a Find where I need to reject certain values
                planteg

                Thanks to all of you .

                 

                In the past I worked with SQL Server and Access. For persons like me it's quite easy to create a SQL query that will do the job. But trying to understanding how to do the same thing in FileMaker can be quite a chalange.

                 

                After a few thoughts this afternoon, I finally got it to work this way:

                 

                Strangege Find_2.jpg

                These are the settings for Enter Find Mode [Restore]
                Strange_Find_1.jpg

                Look at the scrip step with yellow outline: if I disable that step, I am told that the "Find criteria is not valid". That one I can't figure out .

                 

                Gilles Plante

                • 5. Re: Scripting a Find where I need to reject certain values
                  wimdecorte

                  As an FYI - for code-readability & easy of troubleshooting it is preferable to not use the "restore" setting and just explicitly code your request using Set Fields.

                  You'll thank yourself in a few months time when you need to revisit this script...

                  • 6. Re: Scripting a Find where I need to reject certain values
                    wimdecorte

                    If you are more confortable with SQL, you can just create an ExecuteSQL() call, grab all the IDs of the result set, dump them in a global text field and use a generic "Go To Related" script step to build the found set...

                    • 7. Re: Scripting a Find where I need to reject certain values
                      Mike_Mitchell

                      Look at the scrip step with yellow outline: if I disable that step, I am told that the "Find criteria is not valid". That one I can't figure out .

                       

                       

                      Because FileMaker is attempting to interpret "$Date_D ... $_Date_F" as a string containing valid search criteria. It's not.

                       

                      Do this instead:

                       

                      Set Variable [ $criteria ; $Date_D & "..." & $_Date_F ]

                      Enter Find Mode [ ]

                      Set Field [ TimeEntry::WeekDate ; $criteria ]

                       

                      Mike

                      • 8. Re: Scripting a Find where I need to reject certain values
                        planteg

                        Talking about "restore", as far as I understand, Enter Find Mode will display [restore] if one sets the find clauses with that script step. But why in the world is restore displays then ?

                         

                        I am fine with setting the request with Set Field, but I just can't find a way to OMIT records using Set Field. If I say Field is TimeEntry:: ClientEntry and set the criteria to  <> "CDE Syst-M", I am getting an error. In fact the calculation says it should return a text. Then my question is: how can I perform a Find using Set Field with both Include and Omit ?

                         

                        Thanks

                        • 9. Re: Scripting a Find where I need to reject certain values
                          erolst

                          planteg wrote:

                          my question is: how can I perform a Find using Set Field with both Include and Omit ?

                           

                          Thanks

                           

                          You do that by combining several queries (my code above was incomplete), e.g.:

                           

                          Set Variable [ $searchRange ; Table::startDate & ".." & Table:.endDate ]

                          Enter Find Mode [ ]

                          Go to Layout [ "TimeEntry" ( TimeEntry ) ]

                          Set Field [ TimeEntry::WeekDate ; $searchRange ]

                          New Record/Request

                          Omit Record/Request

                          Set Field [ TimeEntry::EntryClient ; "CDE syst-M" ]

                          Set Error Capture

                          Perform Find

                           

                          while in Browse mode “Omit” omits a record, in Find mode it sets the Matching Records flag.

                           

                          Alternatively, as Wim suggested, first Perform Find with the inclusion criterion, then Constrain Found Set to the exclusion criterion.

                           

                          Don't forget to trap for an empty found set.

                          • 10. Re: Scripting a Find where I need to reject certain values
                            wimdecorte

                            planteg wrote:

                             

                            set the criteria to  <> "CDE Syst-M", I am getting an error.

                             

                            Have you tried:

                            "<> CDE Syst-M"?

                             

                            Also, the approach that I mentioned about doing one search first and doing a constrain find on the result set works really well, I have not heard your feedback on that.

                            • 11. Re: Scripting a Find where I need to reject certain values
                              planteg

                              Hi all.

                               

                              this is how I did it:

                               

                              Set Variable [ $Date_D; Value:z_Resources::z_DebutPeriode ]

                              Set Variable [ $Date_F; Value:z_Resources::z_FinPeriode ]

                              Enter Find Mode [ ]

                              Set Field [ TimeEntry::WeekDate ; z_Resources::z_DebutPeriode & "..." & z_Resources::z_FinPeriode ]

                              New Record/Request

                              Set Field [ TimeEntry::EntryClient ; "CDE Syst-M inc" ]

                              Omit Record

                              Perform Find [ ]

                              Sort Records [ Keep records in sorted order ; Specified Sort Order: TimeEntry::EntryClient ; ascending

                              TimeEntry::WeekDate ; ascending

                              TimeEntry::EntryProject

                               

                              I would like to make sure I understand correctly:

                               

                              • When New Record/Request is executed, the previously defined values in the fields will be used to find the records that respond to these criterions, and you get a found set
                              • Omit Record reverse from include to omit for the second Find
                              • When the Perform Find is executed, records are omitted per new criterions from the preceding found set.

                               

                              My next report wil need a more complicated Find, so I may get back here in a few days.

                               

                              erolst, yoy mentioned the Omit Record/Request script step, I just can't find it in the help for either V12 or V13. Do you have a copy of V14 .

                              • 12. Re: Scripting a Find where I need to reject certain values
                                Mike_Mitchell

                                I would like to make sure I understand correctly:

                                 

                                • When New Record/Request is executed, the previously defined values in the fields will be used to find the records that respond to these criterions, and you get a found set
                                • Omit Record reverse from include to omit for the second Find
                                • When the Perform Find is executed, records are omitted per new criterions from the preceding found set.

                                 

                                 

                                No. You're confusing Wim's suggestion with what you actually did.

                                 

                                What you did uses a single Perform Find operation. It relies strictly on a single found set. What Wim suggested was using two separate Find operations, one as a Perform Find and one as a Constrain Found Set. These are different methods.

                                 

                                In more detail:

                                 

                                • New Record / Request creates a new Find Request. No action is taken to filter records based on this script step; all it does is create a blank Find Request.
                                • Omit Record / Request reverses the "Include" identifier at the top of the screen to "Omit", so any search criteria you enter will be omitted when the Find executes instead of included (your second bullet is mostly correct).
                                • Perform Find does not (repeat, does NOT) operate in any way, shape or form, on an existing found set. It creates a completely new one based on the criteria in the Find Requests present when it executes. (The script steps that operate on an existing found set are Constrain Found Set and Extend Found Set.)

                                 

                                HTH

                                 

                                Mike

                                • 13. Re: Scripting a Find where I need to reject certain values
                                  erolst

                                  planteg wrote:

                                  I would like to make sure I understand correctly:

                                   

                                  • When New Record/Request is executed, the previously defined values in the fields will be used to find the records that respond to these criterions, and you get a found set
                                  • Omit Record reverse from include to omit for the second Find
                                  • When the Perform Find is executed, records are omitted per new criterions from the preceding found set.

                                   

                                  No; you get one single found set (if any) after Perform Find is executed. The process you describe would be scripted in its basic form (without error trapping) as

                                   

                                  Enter Find Mode

                                  Set Field [ inclusion ]

                                  Perform Find

                                  Enter Find Mode

                                  Set Field [ exclusion ]

                                  Constrain Found Set

                                   

                                  where Find gives you a first found set that you then whittle down via Constrain to arrive at the final set, as suggested by Wim.

                                   

                                  Be sure to add error trapping to your scripts.

                                   

                                  planteg wrote:

                                  erolst, yoy mentioned the Omit Record/Request script step, I just can't find it in the help for either V12 or V13. Do you have a copy of V14 .

                                   

                                  Well, sh… happens when you script from memory. Good to see that your superior powers of deduction & detection allowed you find the correct step regardless.