4 Replies Latest reply on Jul 21, 2016 1:37 PM by jp98

    looping script taking for ever.

    jp98

      Can someone help me optimize a script please.

       

      What i have is one table with all products listed in it and another table where quote line items are stored. We have another piece of software that works out what products are required for a job and then generates a report saved as an excel file. I have a vbscript that runs on this file and optimizes it for easy import to filemaker.

       

      This file contains product codes, descriptions and quantities but not product categories. However, for the relationship between the earlier mentioned tables to function correctly, i need to get the product category for each product as well. Currently i have a script that loops as below.

       

      Step 1. grab product code from first imported product- set variable

      Step 2. go to products layout and perform search

      Step 3. get product category for that product - set variable

      Step 4. return to original layout and set category field for that product.

       

      This script can take ages to perform even when there are only 50 lines but sometimes we will have over 200.

       

      To distinguish between records that have already been done and ones that haven't, there is a find step which searches for records with no product category set.

      Once product category and product code are set, the relationship functions correctly and pulls all other information such as price and default markup etc. across to the quote.

       

      Looking forward to whatever you can offer!

        • 1. Re: looping script taking for ever.
          DanielShanahan

          What i have is one table with all products listed in it and another table where quote line items are stored. We have another piece of software that works out what products are required for a job and then generates a report saved as an excel file. I have a vbscript that runs on this file and optimizes it for easy import to filemaker.

          Sounds like you have three pieces of software to perform this process (presuming that "another piece of software" is neither FileMaker nor Excel).  Any reason you don't simple use FileMaker for this process?  It is very capable.

           

          This script can take ages to perform even when there are only 50 lines but sometimes we will have over 200.

          What is "ages to perform" mean?  .5 seconds?  5 minutes? 5 hours?

           

          Currently i have a script that loops as below.

           

          Step 1. grab product code from first imported product- set variable

          Step 2. go to products layout and perform search

          Step 3. get product category for that product - set variable

          Step 4. return to original layout and set category field for that product.

          Can you print a copy of the script and posted it?

          • 2. Re: looping script taking for ever.
            David Moyer

            Hi,

            my first thought is that when you perform the find, you might be in a layout showing summary field(s).  I believe that FileMaker will summarize each time you enter the layout in browse or preview mode.  That definitely takes time.

            Next, finding records with no product category - make sure you use find =.  That's simply "=" in the field as the search criteria (without quotes).

            Finally, consider using a relationship to "Go to related records".  You might need a global field that will be related to the product table.

             

            EDIT:

            Actually no, check that above, regarding using = as search criteria ... My experience is that the fastest way to search a text field that is empty in a large, large table, is to first find non-empty records, then find omitted.  I use find >0 as the search criteria for non-empty text fields.  It's much faster than find =, in my experience.

             

            And one more thing:  If you get some basic SQL down, your chore would be a breeze - one step when utilizing the ExecuteSQL function..

            • 3. Re: looping script taking for ever.
              philmodjunk

              Since you are using product code as your find criteria to determine the category, it should be possible to set up a relationship that uses product code to determine the category. No find or script needed at all in that case. The relationship will allow you to link to or look up (copy over) the category.

              1 of 1 people found this helpful
              • 4. Re: looping script taking for ever.
                jp98

                philmodjunk you are a legend!!!! the relationship was setup by someone else when the file was first created to match category AND code. all i had to do was delete the match category and the whole process takes about 1 second!!!! maybe a little bit longer - but you get the idea!!