4 Replies Latest reply on May 31, 2012 8:23 AM by nella

    Deleting empty records


      I imported some empty records from Excel and need to delete them.

      Can someone help me with that?


      Thank you.

        • 1. Re: Deleting empty records

          How many records are “some”? 20, 200 or 2,000, ballpark?

          There are some elaborate procedures which for only a few records would be sort of overkill.


          Also, after an import the imported records are the current found set, which makes it easy

          to scan them and, if the result isn't satisfactory, delete them in one fell swoop by simply

          choosing Delete Found Records from the Records menu.

          • 2. Re: Deleting empty records

            There's about 300 or so.

            • 3. Re: Deleting empty records

              OK, this then calls for a methodical approach. The usual warning applies: Create a backup before you try any of this,

              since it involves record deletion which can't be undone! Also be sure to read the full description if you have “manually”

              added new records after the import.


              The simple method:


              Use a field which must not be empty, like the serial number (aka primary key) of your table, to search for ‘=“ (is empty).

              Check the found set, refine/repeat the search if necessary, and choose “Delete Found Records” when the desired records

              make up the found set. This should work even if you have added new records since the import.


              Depending on your field validation options, though, the import may have created new records with an automatically set pk;

              in this case you need to use another field which normally must hold data and now would be empty.

              Should there be no such field, here is …


              The (slightly) more intricate method:


              Count the rows in the Excel file; then in your FM table, choose Show All Records from the Records menu, go to the first record, choose Omit Multiple and enter the result of

              "count of all records minus count of rows in the Excel file" (which is the number of records before the import took place). Click Omit.


              (For completeness's sake: you can also go to Record no "previously existing records + 1", select Omit multiple and enter the Excel row count.)


              The found set should be your freshly imported, empty Excel rows. Check and delete.


              Be aware that this only works this way if you haven't added any new records since the import. If you have, you need to also omit the

              “manually added” records before you perform the batch deletion.


              Here's an example with some sample data: previously existing records: 1,500; Excel import: 300 (optional: added after import: 10); total: 1,800 (1,810)


              Perform these steps:

              Show All Records (Found count: 1,800 (or 1,810)) - Omit Multiple (enter 1,500, click Omit) - Found count: 300 (optional: 310; in this case Go to Record no 301 - Omit Multiple - enter 10 - Omit )


              Hope this helps.

              • 4. Re: Deleting empty records

                The simple method = Gone.


                Thank you.