6 Replies Latest reply on Mar 5, 2012 2:13 PM by cdurham

    Deleting certain records, it it possible?

    cdurham

      Greetings All,

       

      Is it possible to delete certain records in a table in FileMaker? For instance, I have a table with about 10000 records. I only need to delete records 1 - 8000. Would i need to write a code to do this or does FileMaker just allow you to select rows 1 - 8000?

        • 1. Re: Deleting certain records, it it possible?
          psijmons

          Is there any field on which you can find your 8000 records?

          Date entry, ID serial counter, or some other parameter to make a correct selection.

          Then delete the found set from the Records menu (this option will appear when your selection is less then All records in that table).

          • 2. Re: Deleting certain records, it it possible?
            cdurham

            The records are brought in by id. There are dates that are associated with these id's. I used something like this earlier:

             

            Go To Layout [ <name of layout> ]

            Show All Records

            Enter Find Mode []

            Set Field     "<"&(Get(CurrentDate)-61)

            set error capture on

            Perform Find []

            If Get(FoundCount) > 0

            Delete All Records []

            end if

             

            When I used this, it messed up the rest of the Sales Import process that I use the tables for. I tried exporting the tables to excel, but the tables were truncated and I did not get all of the information I needed.

            • 3. Re: Deleting certain records, it it possible?
              Stephen Huston

              You script looks clean enough, other than the unnecessary Show All Records (you are entering Find mode right after it so it doesn't matter what your found set was or even that you were in Browse mode or not). Show All Records can cause large amounts of data to cache  unnecessarily.

               

              If this is a manual cleanup, you might do better to stop it after the set is found so you can examine the group before deleting them all manually.

               

              If simply deleting records (not fields) is messing up anything, you need to look into at least 2 more areas:

              • relationships, any of which might have cascading deletes which could foul your other data
              • your scripted import order, assuming it's scripted. Remember than any change in field definitions or deletion and addition of fields to a table's definitions can gum up export and import orders so they have to be manually reset. It's a known bug, but one we are stuck with for now.
              • 4. Re: Deleting certain records, it it possible?
                psijmons

                Stephen Huston wrote:

                 

                You script looks clean enough, other than the unnecessary Show All Records (you are entering Find mode right after it so it doesn't matter what your found set was or even that you were in Browse mode or not). Show All Records can cause large amounts of data to cache  unnecessarily.

                Stephen, I never realized this and I want to make sure I understand this correctly (as my script are riddled with the ShowAll command).

                 

                When one is in a current found set, then do a find step, not ConstrainFoundSet, the search by default is again on the entire set of records?

                Just tested that and it seems to work indeed... after all these years, never too old to learn.

                Thanks for the tip, I will be cleaning up scripts.

                Peter

                • 5. Re: Deleting certain records, it it possible?
                  Stephen Huston

                  Yep, all searches other than Extend or Find are run against the full table of records, not the found set.

                   

                  My main reason for avoiding the Show All Records when you don't actually want to use the full set is that it's an extra step, takes time, causes screen refresh in some cases, and can increase record caching from the server. Taken together these can be quite a hit on performance in a served environment.

                   

                  I think a lot of us one had the habit of using Show All Records as a shortcut to make sure we started from Browse mode, when in fact there are fairly few reasons for that to ever matter immediately before entering Find mode.

                  • 6. Re: Deleting certain records, it it possible?
                    cdurham

                    @Stephen Hutson

                     

                    Thanks for the advice!