8 Replies Latest reply on Apr 19, 2014 7:03 PM by philmodjunk

    Speedup a Script

    dejosejimpaul

      Title

      Speedup a Script

      Post

           Hi this is my first post in this forum and I am just beginning to learn FileMaker. I need some advice regarding one of my script. First, I have this data entry layout shown below:

            

           Then I have this first script shown below:

           I use this script to multiply the value of each size field to negative one (-1) so I will have a negative number. I will then call this script from my second script shown below:

           As you can see, I put the Toggle Sign script in a Loop script step to set the value of each size field on my layout to a negative number for each and every record in the found set.. After this, I will then export the records to a CSV (text) file. I will then call again the Toggle Sign script to change the value of the size fields back to positive.

           Now the problem is, the script takes merely five (5) minutes to complete which I found a bit slow. Is there any way to speedup the whole process? Thanks in advance.

        • 1. Re: Speedup a Script
          davidanders

               The goal is to have a negative number in 14 inventory fields for export to CSV export.

               Maybe create a calculated field that contains the negative number and export those calculated fields?

               OR export the positive numbers and use find and replace in MS Word to create the negative numbers. (depending on the number of commas in the report.

               Either would be very fast.

          • 2. Re: Speedup a Script
            dejosejimpaul

                 Hi DavidAnders thank you for the reply smiley. As much as possible, I would like to do the conversion of numbers within FileMaker to minimize the job of processing the data.

                 So far we have seventy thousand (70,000) records and say forty thousand (40,000) of them is of "Delivery" transaction meaning we have forty thousand (40,000) records that needs to be converted to negative numbers. The context of "Delivery" here is of two values, one positive and one negative. Which in my case I would like both the result to appear in my exported CSV file. My reason  of doing this has something to do with our products IN and OUT from our warehouse (negative value) to one of our branches (positive value).

                 By the way, the CSV file is used as a data source in a Microsoft Excel pivot table report. The pivot table deals with the addition of those positive and negative numbers and from there we get our product's stock level.

                 Back into my FileMaker script, I'm not quite convince doing the Toggle Sign script twice. Maybe there is a way to make the size fields into a negative number temporarily and right after the export script finishes, it would return those numbers back into positive without calling the Toggle Sign script again...?

            • 3. Re: Speedup a Script
              philmodjunk

                   There is a show all records followed by a perform find in the middle of your script that seems redundant. One seems to undo the action of the other and it looks from here that you can remove both script steps and your script will run the same (but possibly faster).

                   Your loop could also be simplified by removing the Exit Loop If step and using the "exit after last" option with the go to next record step. The go to record [last] and the second call step to perform the toggle sign script then become redundant and can also be removed.

                   DavidAnders calculaiton fields would seem to work just fine for what you want if all you need do is export the negative values. Then there are no values to set to negative and none to return to non-negative values.

              • 4. Re: Speedup a Script
                dejosejimpaul

                      Hi PhilModJunk, thank you for the reply. I have explicitly put the following line in my script:

                     Go to Record/Request/Page [Last]

                     Perform Script ["Toggle Sign"]

                     This is because base on my experience in the Loop script step, it does not process the very last record in the found set. Say I have ten (10) records that needs to be processed but using the Loop script step, it would only take nine (9) and it will not exit. It just keeps on going hence the Exit Loop script step. I have not came on this solution regarding the Loop script step as I have read here somewhere in the forum the same problem and there was a post there showing this kind of workaround.

                     The Show All Records and Perform Find and [Restore] script steps is called because:

                     1. I have four (4) transaction types: PCount, Delivery, Sales, and POut and out this four transactions, I want to convert the values of Sales and POut to negative hence the first Perform Find and Loop script step.

                     2. After the values have been converted I want to Show All Records so I will have all the records of the four transaction again.

                     3. I will perform another Perform Find script step to filter the found set not to include a particular store (warehouse) so I will have all the four transactions of store a, b, and c but not store d (warehouse).

                     4. Begin export.

                     5. Repeat step 1

                     Step 1 takes a couple of minute to complete then I will have to repeat it in step 5 which is another couple of minute to wait. Any ideas as how can I lessen those couple of minutes?

                • 5. Re: Speedup a Script
                  philmodjunk
                       

                            This is because base on my experience in the Loop script step, it does not process the very last record in the found set.

                       That's because your loop exits before calling the perform script to set fields to negative values, thus it exits before doing this for the last record. If you make the other changes that I recommended, you will be able to remove those script steps. But this will make very little difference in how long it takes your script to execute. Even if you made no other changes, you could remove the go to record [last] step as your script is already on the last record at this point.

                       

                            It just keeps on going hence the Exit Loop script step.

                       This will not be the case if you select the "exit after last" option for your go to record/request/page [next] Script step.

                       

                            The Show All Records and Perform Find and [Restore] script steps is called because:....

                       Show All records when the very next step is Perform Find has absolutely no effect on what you script does. The performed find will produce the same found set whether you have all records, some records or no records in your found set at the moment that it executes. I cannot see the criteria used in the first and second Perform Find script steps. If the criteria is the same in both, then you do not need this second perform find. If the criteria is different, you do.

                       

                            Step 1 takes a couple of minute to complete then I will have to repeat it in step 5 which is another couple of minute to wait. Any ideas as how can I lessen those couple of minutes?

                       Don't use a looping script to modify this data. As DavidAnders recommended, use a calculation field and export the calculation field in place of the data field.

                       Define a calculation that switches the sign of only the Sales and POut records.

                       Example:

                       If ( Inventory::TransactionType = "Sales" Or  Inventory::TransactionType = "POut" ; Inventory::XS27 * -1 ; Inventory::XS27 )

                       I am guessing as to how you identify the types of records that are Sales or POut, you'll need to modify this calculation to use the actual field and values that you used to do this.

                       Note that this field is not intended to replace the field XS27 in other parts of your database. It's only for use for this particular export. You would export this calculation field instead of XS27 and you'll need one such calculation field for each of the fields listed in your Toggle Sign script.

                       This can reduce your script to two or maybe three steps:

                       Find the records
                       Sort the records (if you need them to be in a particular order)
                       Export the records.

                  • 6. Re: Speedup a Script
                    dejosejimpaul
                         

                              Define a calculation that switches the sign of only the Sales and POut records.

                         Wow! Thank you PhilModJunk! That's exactly what I'm looking for! I haven't think of that before so I came with the more complex and tedious looping script!

                         

                              If ( Inventory::TransactionType = "Sales" Or  Inventory::TransactionType = "POut" ; Inventory::XS27 * 1 ; Inventory::XS27 )

                         I'm gonna try the calculation above. I am getting the big picture of this, thanks to you wink.

                         PS: I didn't have the idea at first that this was DavidAnders talking about before. Didn't get it right away. Thanks to both of you, I really appreciate it.

                    • 7. Re: Speedup a Script
                      dejosejimpaul

                           Hi again DavidAnders and PhilModJunk! Just want to know both of you that the idea of defining a calculation field that switches  the sign of TXN1 and TXN2 is really brilliant! It works flawlessly without needing to alter the original data. And it solves my problem of having to wait for about five (5) minutes for the export process to finished. Now, with the calculation field whose DavidAnders originally reccommends in place, I was able to generate the CSV file for less than a minute!

                           Again, thank you very much. You've help me a lot smiley.

                      • 8. Re: Speedup a Script
                        philmodjunk

                             Spotted and fixed an error in my example calculation. Glad that error didn't keep you from getting this to work.