6 Replies Latest reply on Oct 31, 2011 12:08 PM by philmodjunk

    Script with Sort Step Causes FMP11 to Freeze HELP

    JoshPoplawski

      Title

      Script with Sort Step Causes FMP11 to Freeze HELP

      Post

       I created a database to track Leave Requests. Based on the date leave is requested for and the person requesting it a serial number is generated. I wrote a script called "Prevent duplicates" that:

      1 Finds all duplicate serial numbers --> 2 puts "duplicate" in a field titled "duplicate marker" so that conditional formatting can highlight all duplicate records a distinguishing color -->3 Shows all records --> 4 sorts records.

      The problem is when i run this script it freezes up File maker pro. Even when i shut down FMP with the task manager and reopen the file it opens frozzen. I have to recover the database file to continue to work with it.

      I have manually run the find and had no problems

      I have manually run the sort and had no problems

      On a whim i removed the sort record script step (#4 above) from the script and ran the script. when i did that the script ran fine with out locking up FMP. I don't understand why sorting at the end of my script is causing FMP to lock up especially when i can do the same exact sort manually without a problem.

      The Sort runs like this:1. Leave Start Date field (ascending) 2. designate management contract field (ascending) 3. Type of Leave (custom sort) 4. approval status sort field (ascending) 5. Time stamp (ascending)

      Please help me with this. I need to know why this caused a freeze up of FMP so i don't let it happen again and also i need to have the script sort the records as listed above at the end. Thanks to all for input.

      Here is the Script:

       

      script.JPG

        • 1. Re: Script with Sort Step Causes FMP11 to Freeze HELP
          philmodjunk

          It may not have been the original cause of your problem, but force quitting Filemaker could have damaged your file. I'd run a recover to see if any damage is reported and then I would test the recovered copy (even if no problems were found) to see if it exhibits the same behavior.

          Can you explain how the Insert Text step works?

          Is Leave::Count a running total summary field perhaps? (If so, very clever!)

          Things to keep in mind about Recover:

          1. Recover does not detect all problems
          2. Recover doesn't always fix all problems correctly
          3. Best Practice is to never put a recovered copy back into regular use or development. Instead, replace the damaged file with an undamaged back up copy if this is at all possible. You may have to save a clone of the back up copy and import all data from your recovered copy to get a working copy with the most up to date information possible.
          • 2. Re: Script with Sort Step Causes FMP11 to Freeze HELP
            JoshPoplawski

             I had two copies of the original file. Both copies froze up when I tried to execute the script. Recovery, (and I have had to recover several times now) has never detected any damage.

            :) Yes the Leave:Count is a running total. (Thank you for the compliment.)

            What the script does is find all the records that have duplicate serial numbers (Find, [Serial number : !]) and then orders them in ascending order by serial number. The script then goes through each record from the top down and inserts "duplicate" into all found records in the "duplicate marker" field. That is how the insert text works.

            I have a conditional formatting on the Leave Database that highlights a record pink if the word "duplicate" is found in the "duplicate marker" field.

            Now originally this script ran fine without a problem, after a week or so is when this problem started to occur. It started right after I changed the calculation in the "approval status sort field hidden" so that instead of labeling records either "First" "Second" or "last" It labeled them 1, 2 or 3. I have tried putting the numbers in quotes also and this makes no difference. HOWEVER, just today I changed the calculation back to its original "First" "Second" "Last" and now it runs fine! Why is that? I changed the calculation to numbers b/c it wasn't sorting correctly with the text markers.

            The "approval status sort field hidden" is used to help sort records. If a leave request is approved or pending it should be at the top (both approved and pending or sorted with the same weight) and if it is denied it should be at the bottom of the list, etc.

            I thought the reason that having numbers in the "approval status sort field hidden"  might be throwing off the database is that that field is also a drop down box with options of "First" "Second" and "Last" but when I turned the drop down box off and made it just a general entry field the problem persists with numbers in place of words. Also when I change the calc to using numbers instead of words it freezes FMP.

            One last thing, before when the program froze and I closed it down with the task manager I had to recover the file b/c the original would open frozen. That has stopped as of today for no apparent reason. Even though I have been causing the freeze in the same way.

            DOES ANYONE KNOW WHY THIS IS HAPPENING? Why is it when I change:

            Case (Approval Status = "Approved" or Approval Status = "Pending"; "First"; Approval Status = "Waiting List"; "Second"; Approval Status = "Denied"; "Last")

            To

            Case (Approval Status = "Approved" or Approval Status = "Pending"; 1; Approval Status = "Waiting List"; 2; Approval Status = "Denied"; 3)

            OR

            Case (Approval Status = "Approved" or Approval Status = "Pending"; "1"; Approval Status = "Waiting List"; "2; Approval Status = "Denied"; "3")

            It causes FMP to freeze? I can work around this but I really need to know why this happens to make sure this doesn't occur again as I develop the database and need to continue to write calculations.

            Thanks.

             

            • 3. Re: Script with Sort Step Causes FMP11 to Freeze HELP
              philmodjunk

              The script then goes through each record from the top down and inserts "duplicate" into all found records in the "duplicate marker" field. That is how the insert text works.

              Not quite, and that's the clever part, Leave::DuplicateMarker [leave::count > 1] Refers to repetition 0 when leave::count is 1 or less and refers to repetition 1 when leave::count is greater than 1. Thus, the first instance of each record in the group of duplicate values will not be marked. This assumes that your running total is set to restart with each sorted group and I'd want to test that concept carefully before using it in a script.

              While it's clever, I'd just use an If function here as it's easier to decode when you look at such an expression a month or more later and need to understand how it works.

              I'd also use set field rather than insert as set field does not require that the target field be present on the current layout like Insert does.

              I don't see any reason from here why the changes in this calculation would cause FileMaker to freeze here. All return values that should sort predictably and correclty whether your return type is specified as text or number. (With text specified, numbers of two or more digits will not sort as expected, but these are single digit numbers and even with two digit numbers the sort should take place.

              What could make a difference is whether the field's storage options are specified as "stored" or "unstored", especially if the table has a very large number of records in it.

              As I've previously noted, Recover doesn't always detect damage to the file nor does it always successfully repair it. You might try creating  a small test file from scratch and checking to see if it shows the same behavior here.

              • 4. Re: Script with Sort Step Causes FMP11 to Freeze HELP
                JoshPoplawski

                Not quite, and that's the clever part, Leave::DuplicateMarker [leave::count > 1] Refers to repetition 0 when leave::count is 1 or less and refers to repetition 1 when leave::count is greater than 1. Thus, the first instance of each record in the group of duplicate values will not be marked. This assumes that your running total is set to restart with each sorted group and I'd want to test that concept carefully before using it in a script.

                :) yes this is correct b/c originally the script was created to  delete all but the origional record. I was then asked to leave them but mark them. so I changed the script. I accidently posted the old script! My appologies. Below is the actual script i'm currently running. Both scripts casued the same problem and only when the numbers are used instead of the text marker.

                 

                • 5. Re: Script with Sort Step Causes FMP11 to Freeze HELP
                  JoshPoplawski

                  Also in the "approval status sort field hidden"  storage options "Do not store calculated results" is marked.

                  • 6. Re: Script with Sort Step Causes FMP11 to Freeze HELP
                    philmodjunk

                    Make that field stored, not unstored and see what happens.

                    The script shown above could be simplified to:

                    Perform Find [Restore]
                    Replace Field Contents [no dialog; Leave::Duplicatemarker ; "duplicate" ]
                    Show All Records
                    Sort [no dialog ; restore]