7 Replies Latest reply on Jan 15, 2014 1:36 PM by philmodjunk

    Moving Records between Tables or use Layout Trigger?

    ChadGist

      Title

      Moving Records between Tables or use Layout Trigger?

      Post

           I know this has been discussed a few times on these forums, but I am still unclear on what the best solution is for me.

           We use our database to track orders that are in process. At some point I would like to mark a record as Shipped by marking the shipped date on a drop down calendar, which will then remove the record from the existing layout, or at least hide it. I would then like to use this field to chart our Shipped data by day, month, year, etc. and keep that history forever.

           My first shot at this was to use a Layout Enter script that would perform a find and omit records. This worked until I tried to perform an additional quick find, and then the Shipped records would show back up. Is there a better way to set up the layout script?...or do I want to import these records into a new table, and then delete them from the old table? If it makes any difference, we'll probably be adding a few thousand records per year.

           Thanks for the help!

        • 1. Re: Moving Records between Tables or use Layout Trigger?
          philmodjunk

               What exactly did that OnLayoutEnter script do? Omit all records where a date was entered into the shipped date field?

               You can set up the OnModeEnter Trigger to perform a script when entering Browse mode to constrain the found set to only unshipped records when returning to Browse mode. Then, if the user performs a find, their find results are constrained to omit the undesired records.

          • 2. Re: Moving Records between Tables or use Layout Trigger?
            ChadGist

                 OK, essentially yes, that is the way I had it set up before to answer your question.

                 First, upon OnLayoutEnter, should I just be using a Constrain Found Set step, as opposed to entering the Find mode and omitting?

                 Second, when I use the Constrain Found Set script step with OnModeEnter, some type of a loop happens or mode changes where the pointer changes to the Apple command symbol and I have to force quit. What did I do wrong?

            • 3. Re: Moving Records between Tables or use Layout Trigger?
              philmodjunk
                   

                        First, upon OnLayoutEnter, should I just be using a Constrain Found Set step, as opposed to entering the Find mode and omitting?

                   That depends on the results you want to show. A constrain will go through the records in the current found set and omit any that don't meet the specified criteria. If you perform a find, you'll find all records in your table that meet the specified find criteria (or that don't meet the omit criteria in this case.)

                   If you always want to list all unshipped records when the user enters the layout, perform the find instead of constraining the found set.

                   Your script is tripping it's own script trigger--producing an infinite loop.

                   Use this type of script where you use a global variable to keep out of the loop:

                   If [ Not $$TriggersOff ]
                      Set Variable [ $$TriggersOff ; value: True ]
                      Enter Find mode []
                      Set Field[
                      Set Error Capture [on]
                      Constrain Found Set []
                      Set Variable [ $$TriggersOff ; value: False ]
                   End If

                   When the Constrain is performed, the script trigger is still tripped due to a return to Browse mode from Find mode, but because $$TriggersOff has been set to True, the script exits without doing anything and thus without tripping the trigger a third time. Control then passes to the last set variable step where the variable is set back to False so that the next mode change to Browse mode will once again perform this script.

                   If you have FileMaker Advanced, it can be a major eye-opener to use the script debugger to watch this happen step by step--especially how a script can trip a script trigger and then it is "interrupted" by the triggered script, with the original script paused until the triggered script completes execution. In fact, that's how I first figured out this method for keeping a script from tripping script triggers.

                   This, BTW, is a very good way to set up any trigger controlled script. You can then set $$TriggersOff to True at the start of any script that might trip a script trigger that you don't want it to trip and then you set it to False at the end or each exit point in your script to re-enable script triggers now that the script is finished. I use this a lot with scripts that change layouts to do their work as a change in layouts can trip any one of a long list of script triggers. But keep in mind that each trigger controlled script must have that If block that tests the value of the global variable or setting the variable to True won't keep the script from executing.

              • 4. Re: Moving Records between Tables or use Layout Trigger?
                ChadGist

                     Thank you, this is so incredibly helpful and I appreciate it. 

                     I am successful now at filtering the results upon layout enter, and we have fixed the looping issue....but when I perform a Find or a Quick Find, it is still not filtering the results. Right now, I have the script set to perform upon entering and exiting all 3 modes. Please take a look and let me know if anything sticks out as being wrong.

                • 5. Re: Moving Records between Tables or use Layout Trigger?
                  philmodjunk

                       The script should only need be set up for a single trigger for a single mode change: either entering browse mode or exiting Find mode. (and no need for both.)

                       I'd check the value of $$TriggersOff. If it was accidentally set to True during testing, it will keep the trigger from doing anything. A simple way to check for that is to close and re-open the file as that will reset the global variable back to False.

                       And yes, quick find does not trip these script triggers. I personally don't find quick find very useful as it can find data that matches the criteria but which is in the wrong field and thus can produce misleading results. I'd use layout setup to disable quick finds on this layout and thus limit the user to performing a find from the records menu.

                       (And if you have FileMaker Advanced, you can set up a custom menu that replaces Perform Find with your own script and then you can dispense with script triggers all together and just use a script that performs the user's find and then constrains the results. )

                  • 6. Re: Moving Records between Tables or use Layout Trigger?
                    ChadGist

                         Well, after following all the steps you just told me....IT WORKS!

                         Very interesting about Quick Find, I will have to keep that in mind for the future.

                         One more question, right now I am constraining my found set to omit a large date range. Is it possible to use an operator for any date vs. empty?

                    • 7. Re: Moving Records between Tables or use Layout Trigger?
                      philmodjunk

                           If I want to omit all records if there is any data in a particular field, I would use:

                           Enter Find Mode []
                           Set Field [Table::field ; "="]
                           Set Error Capture[on]
                           Constrain found set[]

                           For more examples of what you can do with a scripted find: Scripted Find Examples