1 2 Previous Next 18 Replies Latest reply on Dec 14, 2009 10:36 AM by philmodjunk

    Using Global Loops, Variable Loops and other means...

    SaterInc

      Title

      Using Global Loops, Variable Loops and other means...

      Post

      Hello all,

          I've been making a database to run at our paint shop for some time, and alot of the scripts rely heavily on running loops through all the records to spot out orders, delivery slips, etc.  So far i've only built up about 250 records and it is running good for the time being.. I recently added freeze window before my loops to speed up the process which helps.

           For giggles I made a new file and imported my records over and over until I had about 26,000 and tried running my scripts.  I wasn't to suprised to find that it is taking 5+ minutes to loop through my records.  I've read around that a variable loop is alot faster then the global loop(which i was not aware of such a loop). 

       My question is does anyone here know of a way to speed up loops running through 10,000 plus records?  I'm using Filemaker pro 10

        • 1. Re: Using Global Loops, Variable Loops and other means...
          ninja
            

          Howdy SaterInc,

           

          This isn't a direct answer to the question you asked.

          Now that that's out of the way...

           

          If you've scripted loops looking for specific things, could you avoid looping through ALL the records, and do a Find for those things instead?  Then loop through the found set, if you even need to loop anymore at all.

          I guess the thought came to me as I considered that the quickest loop is a loop that doesn't exist.

          Time=0 ;)

           

          If you have criteria that define a record as a delivery slip, and another criteria on it that make it a delivery slip you want to do something with, find for those two criteria and avoid the loop.  If the loop further does something to the records meeting the criteria, you can ReplaceFieldContents on the found set rather than looping.

          Note that FindTime>0...but usually less than looping. 

           

          Just food for thought.

          Enjoy the day!

          • 2. Re: Using Global Loops, Variable Loops and other means...
            SaterInc
               wow, that really speeded up the process.  Never really used the find command before. Thanks for the tip
            • 3. Re: Using Global Loops, Variable Loops and other means...
              SaterInc
                

              Is there a way to run multiple specific finds.... for example, I am only using the find command to sort out my open orders, lets say I now want to find jobs based on color that are both open and closed. Can I do this or am I stuck having the find command sort out my opens and the color?  Seems i can't remove the find for open orders from the list in my new find without causing problems... I'm trying to keep down user input as well so I can't really have the receptionist typing in the field she is looking for. If that makes sense :P

              • 4. Re: Using Global Loops, Variable Loops and other means...
                ninja
                  

                Always glad to give someone a new toy to play with.

                 

                If you've not played with Find much before, please be aware that you can script it, used together with "SetVariable", "SetField" and Set ErrorCapture[on].

                 

                I think you'll 'find' your task speeds getting pretty fast compared to the loops you're used to.

                Doing finds on unstored calc fields will slow you back down though...so choose/design wisely.

                • 5. Re: Using Global Loops, Variable Loops and other means...
                  ninja
                    

                  SaterInc wrote:

                  Is there a way to run multiple specific finds.... for example, I am only using the find command to sort out my open orders, lets say I now want to find jobs based on color that are both open and closed. Can I do this or am I stuck having the find command sort out my opens and the color?  Seems i can't remove the find for open orders from the list in my new find without causing problems... I'm trying to keep down user input as well so I can't really have the receptionist typing in the field she is looking for. If that makes sense :P


                   

                  If I understand correctly, you could have a global field (gFind) with a dropdown value list of colors (value list based on PartColor field).  The user would then choose the color in that field and hit the "Find Opens" button which launches the script:

                   

                  SetVariable [$color ; gFind] 

                  Enter Find Mode

                  Setfield [ PartColor ; $color]

                  setfield [ Status ; "open"]

                  Set ErrorCapture [on]

                  Perform Find []

                   

                  If you want them all, leave out the Setfield [Status ; "open"]

                   

                  While you're learning find options, note that you can do a find for Blue AND open (script above), or Blue OR open:

                   

                  SetVariable [$color ; gFind] 

                  Enter Find Mode

                  Setfield [ PartColor ; $color]

                  New Record/Request  *********This makes it an "or"

                  setfield [ Status ; "open"]

                  Set ErrorCapture [on]

                  Perform Find []

                   

                  Or constrain or extend a found set (ie. which of the current found set of "blue" are also "open"?)

                   

                  There are many permutations, many of which may be helpful.  I've used a bunch of terms, check out the help index for them...lots o' goodies.

                   

                  Note, however, that you cant reference normal fields once you enter find mode...the SetVariable step traps the info you want prior to entering find mode to get around that. Global Fields, however, are still referenceable when in find mode.

                   

                  As I look at this post, it looks like a data dump...how you use the techniques really are defined by what you need to do.  These are just a bunch of tools...

                   

                  Is there a specific task (found set) that you're trying to achieve?


                  • 6. Re: Using Global Loops, Variable Loops and other means...
                    SaterInc
                      

                    Not particulary,  As I said alot of my scripts relied heavily on loops, the one that slowed me down the most was the "Whats open" script because it is run in many of my other reports before a new loop is started to narrow it down further.

                      Here is a break down of my reports

                                                                  Open Orders

                                                     Plant 1 Orders     Plant 2 Orders

                     All Open Plant 1   | Search all Open By PT#  | Search All Open by Color |  All Open Plant 2 

                       Search by Cust Plant 1                           |               Search by Customer plant 2

                    All Case Jobs Plant 1   |  All Other Jobs Plant 1 

                     

                    Search all Open/Closed by cust

                    Search all Open/closed by Job #

                    Search all Open/closed by DelSlip #

                    Show all Closed

                     

                      - Those are just the reports, I also am currently using loops for...

                         Generate Pack Slip  - Loops through all open records and copies records with Given DelSlip to blank template

                                         (And any given record could have up to 9 DelSlip fields to search through )

                         Generate Traveler   -  Loops through open records, first order user picks then sorts out all of same color, user then picks which orders to add to traveler.

                         Goto Record - Goes to record/records based on job #(in actual DB, not listed in report so user can modify found set)

                     

                     As you can see I have alot of reports/commands that were handled by loops so I'll probaly just test out which ones I can use the find command for to speed it all up.  I really appreciate your help on this.

                    • 7. Re: Using Global Loops, Variable Loops and other means...
                      ninja
                        

                      SaterInc wrote:

                        Here is a break down of my reports

                                                                    Open Orders

                                                       Plant 1 Orders     Plant 2 Orders

                       All Open Plant 1   | Search all Open By PT#  | Search All Open by Color |  All Open Plant 2 

                         Search by Cust Plant 1                           |               Search by Customer plant 2

                      All Case Jobs Plant 1   |  All Other Jobs Plant 1 

                       

                      ...I'll probaly just test out which ones I can use the find command for to speed it all up. 


                      From my outsider view, it looks like all of them would benefit from the Find rather than the loop.

                       

                      The most rigorous one strikes me as "All other jobs Plant 1".  For that one try something like:

                       

                      Enter Find mode

                      Setfield [ Packaging; "Case" ]

                      Set ErrorCapture [on]

                      Perform Find []

                      Show Omitted Only

                      Enter Find Mode

                      SetField [ Plant ; "Plant 1" ]

                      Constrain Found Set

                       

                      There are other, more direct ways toward that one, but this way is easy to follow the logic on.

                      Have a great tour of the Find Function!


                      • 8. Re: Using Global Loops, Variable Loops and other means...
                        SaterInc
                          

                        Thanks again Ninja, I probaly will use it for all my reports, i even managed to use it for my Pack Slip generation, I still have to use the loop to copy the data from the sorted records, but it definately goes faster because i can sort out the records, then loop through the found set like you mentioned earlier.

                        • 9. Re: Using Global Loops, Variable Loops and other means...
                          philmodjunk
                            

                          I've been watching Ninja give great advice here....

                           

                          "I still have to use the loop to copy the data from the sorted records"

                           

                          There are ways to accelerate even that and copying the data may or may not be truly necessary in the first place. If you're interested, post a description of why you need to do this and how your script is doing it and we might just succeed in suggesting a few improvements here.

                           

                          BTW, eliminating these looping scripts not only makes your system faster, it makes it simpler. And that makes for a solution that is easier to analyze/modify/update in the future.

                          • 10. Re: Using Global Loops, Variable Loops and other means...
                            ninja
                              

                            One last idea for you to consider:

                             

                            Is the loop&copy needed, or can you reference the data through a relationship?  Pack Slip sounds like a chance to put a field on the layout from a related table...a portal of goods ordered, with details of each item from a LineItems table.  PhilModJunk has posted a number of times about using line items for invoicing solutions (search Invoicing on this forum)...the same approach works well for packing lists (I assumed that this is what PackSlip meant).

                             

                            but you know your workflow/dataflow best...

                             

                            End of week, have a great weekend!

                            • 11. Re: Using Global Loops, Variable Loops and other means...
                              SaterInc
                                

                              Ok, here is my scripts for generating a packslip...

                                  This first script is initiated by a button on my DelSlip log which is an assigned delivery number that can be found in any record tied to that shipment.

                               

                              If [$$PAUSE_STATUS = TRUE]

                                  Show Custom Dialog[ Title: " Error: Please resume script"; Message: "Please click the red "Resume Script" button before attempting anything else."; Buttons: "OK" ]

                                  Exit Script []

                              End if

                                 #Copy D/S # to global in inventory table.

                                  Set Field[ Inventory::DS_SEARCH; Delivery Log::DEL SLIP#]

                                  Goto Layout["JOB DATABASE" (Inventory) ]

                                  Goto Record/Request/Page [First]

                                  Enter Browse Mode

                                #Reset Counter

                                  Set Field[ Inventory::DS_COUNT; 0 ]

                                #Loop goes through all records searching the 9 DEL_SLIP fields for a matching outgoing shipment

                               Loop

                                If [ Inventory::DS_SEARCH = Inventory::DEL_SLIP_2 ]

                                    Perform Script["COPY PACKSLIP DATA" ]

                                    Perform Script["PACK_DEL_2"]

                                end if

                                If [ Inventory::DS_SEARCH = Inventory::DEL_SLIP_3 ]

                                    Perform Script["COPY PACKSLIP DATA" ]

                                    Perform Script["PACK_DEL_3"]

                                end if

                                If [ Inventory::DS_SEARCH = Inventory::DEL_SLIP_4 ]

                                    Perform Script["COPY PACKSLIP DATA" ]

                                    Perform Script["PACK_DEL_4"]

                                end if

                                If [ Inventory::DS_SEARCH = Inventory::DEL_SLIP_5 ]

                                    Perform Script["COPY PACKSLIP DATA" ]

                                    Perform Script["PACK_DEL_5"]

                                end if

                                If [ Inventory::DS_SEARCH = Inventory::DEL_SLIP_6 ]

                                    Perform Script["COPY PACKSLIP DATA" ]

                                    Perform Script["PACK_DEL_6"]

                                end if

                                If [ Inventory::DS_SEARCH = Inventory::DEL_SLIP_7 ]

                                    Perform Script["COPY PACKSLIP DATA" ]

                                    Perform Script["PACK_DEL_7"]

                                end if

                                If [ Inventory::DS_SEARCH = Inventory::DEL_SLIP_8 ]

                                    Perform Script["COPY PACKSLIP DATA" ]

                                    Perform Script["PACK_DEL_8"]

                                end if

                                If [ Inventory::DS_SEARCH = Inventory::DEL_SLIP_9 ]

                                    Perform Script["COPY PACKSLIP DATA" ]

                                    Perform Script["PACK_DEL_9"]

                                end if

                                If [ Inventory::DS_SEARCH = Inventory::DEL_SLIP_10 ]

                                    Perform Script["COPY PACKSLIP DATA" ]

                                    Perform Script["PACK_DEL_10"]

                                end if

                               If [Inventory::PACK_JOB = 0]

                                     Omit Record

                                End If

                                Goto Record/Request/Page [ Next; Exit after Last ]

                               end loop

                              Goto Layout["PACK SLIP" (PACK SLIP)]

                              Enter Browse Mode

                               If [ Inventory::DS_COUNT = 0 ]

                                 Show Custom Dialog[Title: " No such Del Slip found."; Message: "No matches of requested D/S# is in database records."; Buttons: "OK", "CANCLE"]

                              else

                                   Perform_Script[ COUNT PACK SLIP RECORDS ]

                                   Set Variable[$$PAUSE_STATUS; Value:True]

                                   Pause/Resume Script[ Indefinitely ]

                                   Set Variable[$$PAUSE_STATUS; Value:FALSE]

                                end if

                                Perform Script[" CLEAR PACK SLIP FIELDS "]

                                Goto Layout[ original layout]

                                Show all Records

                               

                              ---------------------------------------------------------------------------------------------------------   

                              #COPY PACKSLIP DATA SCRIPT

                              #Copy all relavent data to global variables for transfer between tables.

                              Set Field[Inventory::DS_COUNT; + 1]

                              Set Field[Inventory::PACK_JOB; Inventory::JOB_NUMBER ]

                              Set Field[Inventory::PACK_PO; Inventory::PO_Number ]

                              Set Field[Inventory:: PACK_PN; Inventory:: Part_Number ]

                              Set Field[Inventory:: PACK_SERIAL; Inventory:: Serial_Number ]

                              Set Field[Inventory:: PACK_DESC; Inventory:: Description ]

                              Set Field[Inventory:: PACK_CUSTOMER; Inventory:: Customer ]

                              Set Field[Inventory:: PACK_RACK_TYPE; Inventory:: RACK_TYPE ]

                              Goto Layout[ "PACK SLIP" (PACK SLIP) ]

                              #PASTE COPIED DATA TO PACK TABLE

                              Set Field[PACK SLIP::PACK_JOB; Inventory::PACK_JOB ]

                              Set Field[PACK SLIP::PACK_PO; Inventory::PACK_PO ]

                              Set Field[PACK SLIP:: PACK_PN; Inventory:: PACK_PN ]

                              Set Field[PACK SLIP:: PACK_SERIAL; Inventory:: PACK_SERIAL ]

                              Set Field[PACK SLIP:: PACK_dESC; Inventory:: PACK_DESC ]

                              Set Field[PACK SLIP:: PACK_CUSTOMER; Inventory:: PACK_CUSTOMER ]

                              Set Field[PACK SLIP:: PACK_RACK_TYPE; Inventory:: PACK_RACK_TYPE ]

                              Perform Script["PACK SLIP - CUSTOMER FILL IN" ]

                              Goto Layout["JOB DATABASE" (Inventory)]

                               

                              -------------------------------------------------------------------------------------------------------

                              PACK SLIP - CUSTOMER FILL IN SCRIPT

                              #MATCH CUSTOMER NAME TO ASSOCIATED ADDRESS IN CUSTOMER TABLE

                              Goto Layout["CUSTOMERS" (CUSTOMERS)]

                              Goto Record/Request/Page [ First ]

                              Loop

                               #Copy ship/sold to information from customer table to pack slip

                               If [ CUSTOMERS::Name = Inventory::PACK_CUSTOMER]

                                    Perform Script["CUSTOMER DATA - SHIP TO"]

                                    Perform Script["CUSTOMER DATA - SOLD TO"]

                               end if

                                Goto Record/Request/Page [ Next; exit after last ]

                              End Loop

                              Goto Layout[ Original layout ]

                              ---------------------------------------------------------------------------------------------------------------

                              CUSTOMER_DATA - SHIP TO SCRIPT

                              #COPY DATA FROM CUSTOMER TABLE TO GLOBAL VARIABLE FOR TRANSFER TO PACK TABLE

                              Goto Layout ["CUSTOMERS" (CUSTOMERS) ]

                              Set Field[ Inventory::PACK_SHIP_TO_ADD; CUSTOMERS:: STREET ]

                              Set Field[ Inventory::PACK_SHIP_CITY; CUSTOMERS:: CITY ]

                              Set Field[ Inventory::PACK_SHIP_STATE; CUSTOMERS:: STATE ]

                              Set Field[ Inventory::PACK_SHIP_ZIP; CUSTOMERS:: ZIP ]

                              Goto Layout["PACK SLIP" (PACK SLIP)"]

                              Set Field[ PACK_SLIP::PACK_SHIP_TO_NAME; Inventory:: PACK_CUSTOMER ]

                              Set Field[ PACK_SLIP::PACK_SHIP_TO_ADD; Inventory:: PACK_SHIP_TO_ADD  ]

                              Set Field[ PACK_SLIP::PACK_SHIP_CITY; Inventory:: PACK_SHIP_CITY  ]

                              Set Field[ PACK_SLIP::PACK_SHIP_STATE; Inventory:: PACK_SHIP_STATE  ]

                              Set Field[ PACK_SLIP::PACK_SHIP_ZIP; Inventory:: PACK_SHIP_ZIP  ]

                              Goto Layout[ Original Layout ]

                               

                              -------------------------------------------------------------------------------------------------------------------

                              CUSTOMER DATA - SOLD_TO SCRIPT

                              #Copy data to a global for transfer to pack slip

                              Goto Layout ["CUSTOMERS" (CUSTOMERS) ]

                              Set Field[ Inventory::PACK_CUSTOMER; CUSTOMERS:: BILL_NAME ]

                              Set Field[ Inventory::PACK_SHIP_TO_ADD; CUSTOMERS:: BILL_STREET ]

                              Set Field[ Inventory::PACK_SHIP_CITY; CUSTOMERS:: BILL_CITY ]

                              Set Field[ Inventory::PACK_SHIP_STATE; CUSTOMERS:: BILL_STATE ]

                              Set Field[ Inventory::PACK_SHIP_ZIP; CUSTOMERS:: BILL_ZIP ]

                              Goto Layout["PACK SLIP" (PACK SLIP)"]

                              Set Field[ PACK_SLIP::PACK_SOLD_TO_NAME; Inventory:: PACK_CUSTOMER ]

                              Set Field[ PACK_SLIP::PACK_SOLD_TO_ADD; Inventory:: PACK_SHIP_TO_ADD  ]

                              Set Field[ PACK_SLIP::PACK_SOLD_CITY; Inventory:: PACK_SHIP_CITY  ]

                              Set Field[ PACK_SLIP::PACK_SOLD_STATE; Inventory:: PACK_SHIP_STATE  ]

                              Set Field[ PACK_SLIP::PACK_SOLD_ZIP; Inventory:: PACK_SHIP_ZIP  ]

                              #For certain customers special data must appear on pack slip

                               If [ PACK SLIP::SOLD_TO_NAME = "CNH RMO"]

                                   Set Field[ PACK_SLIP::SUBFIELD; " CL675" ]

                                 end if

                               if [ PACK SLIP::SHIP_TO_NAME = "CASE FARGO" ]

                                  Goto Layout["DEL LOG (Delivery Log) ]

                                  Set Variable[$$LOAD; Value:Delivery Log::LOAD]

                                  Goto Layout["PACK SLIP" (PACK SLIP) ]

                                  Set Field[ PACK_SLIP::LOAD#; $$LOAD ]

                               End if

                              Goto Layout[ Original Layout ]

                               

                              -------------------------------------------------------------------------------------------------------------------

                              PACK_DEL_2 SCRIPT   ( WILL ONLY POST ONE SINCE ALL 8 OTHERS ARE FUNDAMENTALLY THE SAME)

                              #Outgoing shipment data from actual job records is copied to global for transfer to pack slip

                              Set Field[ Inventory::PACK_DEL_SLIP; Inventory:: DEL_SHIP_2 ]

                              Set Field[ Inventory::PACK_QTY; Inventory:: SHIP_QTY_2 ]

                              Set Field[ Inventory::PACK_DATE_SHIP; Inventory:: Ship_Date_2 ]

                               Goto Layout [ "PACK SLIP" (PACK SLIP) ]

                              Set Field[ PACK_SLIP::PACK_DEL_SLIP; Inventory:: PACK_DEL_SLIP ]

                              Set Field[ PACK_SLIP::PACK_QTY; Inventory:: PACK_QTY]

                              Set Field[ PACK_SLIP::PACK_DATE_SHIP; Inventory:: PACK_DATE_SHIP]

                              Goto Layout["JOB DATABASE" (Inventory) ]

                               

                              ----------------------------------------------------------------------------------------------------------------

                              COUNT PACK SLIP RECORDS SCRIPT

                              #Counts lines on pack slip

                               Set Variable[$$RACK_COUNT; Value:0 ]

                              Goto Record/Request/Page [ First ]

                              Loop

                                 Set Variable[$$RACK_COUNT; Value:$$RACK_COUNT+1 ]

                                 Goto Record/Request/Page [Next; Exit after last]

                              End Loop

                              Set Field[ PACK_SLIP::RACK_QTY; $$RACK_COUNT ]

                               

                              ----------------------------------------------------------------------------------------------------------------

                               

                               

                              I believe that is all my scripts that handle the packing slip.... hell of alot of typing :)

                              As you can see i have loops running all over the place, the few in the pack slip table are not bad because there are never more then 15 records, and after each pack slip is printed the records are all deleted.. The D/S# number allows me to regenerate a pack slip if i need to.

                              • 12. Re: Using Global Loops, Variable Loops and other means...
                                SaterInc
                                   That didn't translate quite as well as i hoped, but hopefully you get the gist of it
                                • 13. Re: Using Global Loops, Variable Loops and other means...
                                  SaterInc
                                    

                                  Whoops, that was actually my old script.... In my newest version I replaced the iniatial loop through the 9 DEL_SLIP fields with a find, looks more like this now for the button script....

                                   

                                  Enter Find mode[]

                                   

                                  Set Field [Inventory:: DEL_SLIP_2; Inventory:: DS_SEARCH ]

                                  New Record/Request

                                  Set Field [Inventory:: DEL_SLIP_3; Inventory:: DS_SEARCH ]

                                  New Record/Request

                                  Set Field [Inventory:: DEL_SLIP_4; Inventory:: DS_SEARCH ]

                                  New Record/Request

                                  Set Field [Inventory:: DEL_SLIP_5; Inventory:: DS_SEARCH ]

                                  New Record/Request

                                  Set Field [Inventory:: DEL_SLIP_6; Inventory:: DS_SEARCH ]

                                  New Record/Request

                                  Set Field [Inventory:: DEL_SLIP_7; Inventory:: DS_SEARCH ]

                                  New Record/Request

                                  Set Field [Inventory:: DEL_SLIP_8; Inventory:: DS_SEARCH ]

                                  New Record/Request

                                  Set Field [Inventory:: DEL_SLIP_9; Inventory:: DS_SEARCH ]

                                  New Record/Request

                                  Set Field [Inventory:: DEL_SLIP_10; Inventory:: DS_SEARCH ]

                                  New Record/Request

                                   Perform Find []

                                   

                                   It then loops through these sorted records like it did in my first post, one by one before kicking it into the COPY_PACKSLIP script, doing that alone really speeded up the whole generation process... I can generate a packslip from 30,000 records in maybe 5 seconds the way it is now.

                                  • 14. Re: Using Global Loops, Variable Loops and other means...
                                    philmodjunk
                                       A table of related records instead of mutliple DEL_SLIP_ fields would likely make your entire solution much easier to work with.
                                    1 2 Previous Next