1 2 Previous Next 23 Replies Latest reply on Sep 19, 2014 6:33 AM by paulwatts

    Repeating Find in a Loop



      I use FM Pro 13, have a NewJobs table that includes a start date field and a finish date field and I have a Jobs table that includes a date field and a resource number (values from 1 to 10) field.


      I want to be able to search the Jobs table for a resource value that has no current records between the start and finish dates shown in the NewJobs table. This will enable me to automatically add new records to the Jobs table in the knowledge allocating the identified resource for all dates between between (and including) the start and finish dates.


      I have created a script (being triggered by a button on the NewJobs layout):-


      Set Field [NewJobs::gSD; NewJobs::Start_date] ( I understand that Global Fields are required in the Find Mode)

      Set Field [NewJobs::gFD; NewJobs::Finish_date]

      Set Field [NewJobs::gRN; 1] (Sets gRN - the resource number - to start at 1)

      Go to Layout [Jobs]


      Show All Records

      Enter Find Mode[]

      Set Field [Jobs::Date; NewJobs::gSD&"..."&NewJobs::gFD] (Sets date range for Find between start and finish dates)

      Set Field [Jobs::Resource_number; NewJobs::gRN] (Sets Resource_number for Find = gRN)

      Perform Find[]

      Exit Loop If[Get(FoundCount)=0] (When no records are found, the script has found a Resource_number value that's available between start and finish dates)

      Set Field [NewJobs::gRN;NewJobs::gRN+1] (Increments gRN by 1 for each Find)

      Exit Script If [Result;NewJobs::gRN>10] (The script terminates if resources 1 to 10 are not available between the start and finish dates)

      End Loop

      Show All Records

      Show Custom Dialogue["Available Resource Number is:-";NewJobs::gRN]


      The script only carries out a single Find implying that the loop isn't working. In "the missing manual" and FM help pages, the examples show the loop looking at one record in each cycle then passing on to the next record. In my case, I need to be able to instruct the next Find cycle somehow.


      Is Loop the right way to do this? If so, what's missing from my script?


      Best wishes



        • 1. Re: Repeating Find in a Loop

          Try this:

          Set Variable [$dateBegin; NewJobs::Start_date]                        // use variables

          Set Variable [$dateEnd; NewJobs::Finish_date]

          Go to Layout [Jobs]

          Set Variable [ $available ; False ]



            Exit Loop If [ Let ( $i = $i + 1 ; $i > 10 or $available ]                                                   

            Enter Find Mode[]

            Show All Records // not necessary

            Set Field [ Jobs::Date; $dateBegin &"..." & $dateEnd ]

            Set Field [ Jobs::Resource_number;  $i ]

            Perform Find

            Set Variable ( $available ; case ( not Get ( FoundCount ) ; True ; False ) ]

          End Loop

          Set Variable [ $message ; Case ( $available ; "Available Resource Number is: -" & $i ; "No resource available in date range." ]

          Show All Records

          Show Custom Dialog [ $message ]

          • 2. Re: Repeating Find in a Loop

            Hi Paul,



            If I understand you correctly, you want to know which resources do NOT have job records between two specific dates. Is that right?



            As an aside: the reason you need to use global fields in find mode is because they retain their data even while in find mode, thus they make for a good place to store stuff temporarily while you are performing a find.  Script variables will allow you to do a similar task and they have the added benefit of not being an additional field in your system that has to be managed. In my example I've used variables instead of global fields.



            I would use a loop to build the find request, but I'd do it like this:



            Set Variable $Start = NewJobs::StartDate

            Set Variable $End = NewJobs::FinishDate


            Enter Find Mode

            Go to Layout [Jobs]



            Exit Loop If [$Resource > 10]

            Set Variable $Resource = $Resource + 1

            Set Field [Jobs::Date; $Start & "..." & $End]

            Set Field [Jobs::Resource_number; $Resource]

            Perform Find

            If [Get( FoundCount ) = 0]

              # no found records means this resource has no records, so flag it for later

              Set Variable $resourceList = Case( not isempty($resourceList) ; $resourceList & "¶" ) & $Resource


              # this resource had records so don't add it to the list

            End If

            End Loop


            # At this point in the script the $resourceList variable holds a list of resources that do not have job records between the start and end dates

            • 3. Re: Repeating Find in a Loop

              Hi Chad.

              Thankyou for your quick response.


              You are nearly correct in that I need to identify one resource that currently has no job. I don't need a list, just the first available (in numerical order). As soon as I find a resource, I want to store/display the resource number for later use and stop the script.


              I take the point from both you and erolst that I might be better using variables (I tried this first but didn't succeed).


              Why would your loop script work whereas mine does not? Is it that you increment a variable rather than incrementing a field?



              • 4. Re: Repeating Find in a Loop

                Hi again erolst.

                I do like your approach to storing the available resource found and showing custom dialogue depending on the outcome. However, like with Chad's suggestion, why would your loop work but mine not?



                • 5. Re: Repeating Find in a Loop

                  Since there is no such script step as Exit Script If, it isn't clear what you mean by this statement.

                  If you mean Exit Loop If; there is still a problem. Exit Loop If does not take two arguments.

                  So what is "Result; NewJobs::gRN>10" ?


                  Exit  Script If [Result;NewJobs::gRN>10]

                  • 6. Re: Repeating Find in a Loop

                    If you want to not just find the first free recource in the date range, but compile a list of all available resources, here's another, shorter technique, provided you have FM ≥ v12:



                      Exit Loop If [ Let ( $i = $i + 1 ; $i > 10 ) ]

                      Set Variable [ $isIn ;

                          specify calculation:

                            ExecuteSQL ( "

                              SELECT COUNT(*)

                              FROM Jobs

                              WHERE \"date\" >= ? AND \"date\" <= ? AND Resource_number = ?

                            " ; "" ; "" ; NewJobs::Start_date ; NewJobs::Finish_date ; $i )

                      Set Variable ( $availableIDs ; List ( $availableIDs ; Case ( not $isIn ; $i ) ) ]

                    End Loop

                    # do something with $availableIDs, e.g. set a field to trigger a relationship and display a Resources selection portal


                    Another technique is to display a portal into a Cartesian relationship of all resources, and use the above check to conditionally format the portal rows as available or not (where the resource number comes directly from each related record, i.e. portal row). Use the same check within an allocation script to see if the user clicked an unavailable resource, and exit if so.

                    • 7. Re: Repeating Find in a Loop

                      I don't see a need for a loop at all. Why not just do the find without the resource number, sort by resource number and grab the first record?

                      • 8. Re: Repeating Find in a Loop

                        David: maybe because that does not answer the question?


                        It shows the first resource that is NOT available.

                        • 9. Re: Repeating Find in a Loop

                          Ah, I see now.


                          This sort of Looping find seems awkward. Is there a table of Resources? It's be a lot easier to do the find from the Resource table to the Jobs table (using the date range on the Jobs table and the Omit[] script step).

                          • 10. Re: Repeating Find in a Loop

                            If gRN is a text field and not really a number field; and if Exit Script If is really Exit Loop if; then your single loop exit might make sense because the values are being treated as text.


                            This is because the TEXT value "2"  IS greater than (comes later in the alphabet) than the text value "10"


                              Set Field [NewJobs::gRN;NewJobs::gRN+1]

                              Exit  Loop If [NewJobs::gRN>10]  

                            • 11. Re: Repeating Find in a Loop

                              Hi Bruce.

                              I have doubled checked that gRN is a number field.


                              However, you are right about the Exit Script If step. I should have put Exit Script without the If in my original question.

                              I have never used this step before and don't fully understand what the "Result" bit is/does. I simply assumed that, if my condition was met then the script would terminate (I would use erolst's $message idea to indicate that no resource was available in this case)..



                              • 12. Re: Repeating Find in a Loop

                                paulwatts wrote:

                                I have never used this step before and don't fully understand what the "Result" bit is/does


                                Whatever you want it to do. Think about common tasks like deleting a portal row after asking for user confirmation (or bypassing that dialog by pressing a modifier key). Instead of repeating these lines of code over and over in your scripts, factor them out into a sub-script.


                                When a main script calls that sub-script, it needs to know the status of the action when it regains control, i.e. has the user deleted the portal row or not? That would be a value that the sub-script can pass back using the script result.


                                So now you have a script with complete function-like behaviour: pass in arguments (script parameters), let this “black box” do something, and optionally have it return a result.

                                • 13. Re: Repeating Find in a Loop

                                  "I simply assumed that, if my condition was met then the script would terminate..."


                                  No, that's definitely not how it works.


                                  If there is an Exit Script statement then the script immediately and always exits at that point.


                                  The information you specify in Result is what gets passed back to the calling script - as described by erolst.


                                  One script can call another script. A script calling a subscript can use script parameters to send information to the subscript.

                                  A subscript can perform operations and then report back to the calling script.


                                  Pseudo code:


                                  Paul Watts: "Wash some dishes" [ the six plates in the sink ]


                                  Bruce: (washes dishes then replies) "Six plates washed sir."



                                  • 14. Re: Repeating Find in a Loop

                                    Hi Dave

                                    Thanks for your input.

                                    There is no resource table. It's a pseudo-resource.


                                    I have found a solution (see comments elsewhere).



                                    1 2 Previous Next