10 Replies Latest reply on Dec 9, 2011 5:29 PM by CountryBoy1

    Clearing a Record

    CountryBoy1

      Title

      Clearing a Record

      Post

      Is there a convenient way to clear a record

      in Table View for Filemaker Pro 9 Advanced ?

      Sometimes information is entered that has

      already been entered or is invalid data.  It

      would be nice to just clear the record rather

      than have a script that loops through and clears

      one field at a time.  Am using a wide screen

      monitor and some Tables have up to 50 fields.

      Thanks for your help.


       

       

        • 1. Re: Clearing a Record
          philmodjunk

          If the record has not been committed yet, it can be reverted. If it has been committed, why not just delete it rather than clear each field?

          • 2. Re: Clearing a Record
            CountryBoy1

            Dear Phil:

               I am creating new records in three linked tables.  I don't particularly

               want to delete all these records.  Cleared records can be used for

               the next new data.  Also, a "bad" record may not be discovered

               until other records have been added and I don't want to delete

               a record and create a missing Index value.

             

               Sincerely, CountryBoy1

            • 3. Re: Clearing a Record
              philmodjunk

              I am creating new records in three linked tables.  I don't particularly    want to delete all these records.

              deleting a record need not delete linked records though this is often a good idea in order to maintain good data integrity. If you have a cascading delete that you don't want to trigger, have your script clear just the primary key field used in the relationship, then delete it.

              Cleared records can be used for the next new data.

              Why would this be an advantage over deleting the record and creating a new one? Deleting the record and creating a new one results in the same file size and number of records as if you cleared the fields and re-used it.

              I don't want to delete a record and create a missing Index value.

              "Index" as in auto-entered serial number? Why is such a gap a problem?

              I suppose you could add one more number field, ClearSwitch, to your table and define all the fields with this auto-enter calculation:

              If ( ClearSwitch ; "" ; Self )

              and clear the "Do not replace existing value..." option.

              Then you can clear all fields by setting ClearSwitch to True or any non-zero number.

              (Got this idea from a post by LaRetta.)

              • 4. Re: Clearing a Record
                CountryBoy1

                The ClearSwitch idea sounds pretty good.

                  I'm not a database expert but I like to keep

                my three linked tables all in order with no

                missing index values.  If a record in one table

                gets deleted or the index value gets corrupted,

                then the data in other linked tables is messed up.

                    Basically what I am saying is that when I create

                a new record (that appears in all three tables), I

                intend to use that record and keep the new Index

                value.

                .. CountryBoy1



                • 5. Re: Clearing a Record
                  philmodjunk

                  Unless these are records where an audit process requires that every record in the series be account for (and then clearing a record like this will be a much frowned upon practice, there's no need to keep your index series "perfect".

                  Keeping the sequences "gap free" does not really protect you much from issues with the values in these fields. (If, by "index" you mean FileMaker's internal indexes, these can easily be rebuilt), unless you have only one to one relationships between your tables. Such "corruption" of actual values in the field is unlikely and would very likely damage much more than just the value in this one field--frequent backups and database "best practices" are your best defense against such file damage. Backups also protect you from issues in your data created by user errors or scripts that modified data in unexpected manner due to a bug in the script.

                  • 6. Re: Clearing a Record
                    CountryBoy1

                    Dear Phil:

                       Appreciate your answer.  Perhaps someday I'll

                     become comfortable with missing Index values.  An

                     Index can also serve as an ID number.   An Index

                     number is also the order in which the data was entered.

                     You can sort the data different ways, but the Index

                     number still tells where that record is normally located.

                       Concerning backups, the problem is how often

                     do you do it ?  Every 5 minutes ?  I don't see doing

                     backups as a complete answer.

                        Right now I like the idea of being able to clear out

                     a record if I want to.  I really do appreciate your help.

                     Your recent help with disabling the "new record" feature

                     of Filemaker Pro 11 was especially helpful.

                        Sincerely, CountryBoy1



                    • 7. Re: Clearing a Record
                      philmodjunk

                      Concerning backups, the problem is how often do you do it ?  Every 5 minutes ?

                      In one of our systems, a new record is imported into a "mirror" fileMaker file each time it is completed. (Obviously, this doesn't work for frequently revised records....)

                      I agree that backups cannot protect you from every possible scenario, but am suggesting that it is still better than relying on the lack of "gaps" in your series as any kind of protection of your data integrity. At best it can tell you that something unexpected happened, but is unlikely to be of much help restoring the data--which requires a back up of one kind or another.

                      An Index number is also the order in which the data was entered.

                      And a "gap" in this series does not change this order.

                      • 8. Re: Clearing a Record
                        CountryBoy1

                        Dear Phil:

                            You obviously have a more sophisticated database design

                         than I do, if you are using a mirror image.   I am not even

                         sure how this would work.  Is it imported and checked before

                         it is allowed into the actual database ?

                             In an earlier answer, you stated that Filemakers internal
                         
                         indexes can be rebuilt.  I am interested in how that can be

                         done because there are times when Filemaker seems to want

                         to show the records in it's own internal order.

                              Thanks again.  CountryBoy1

                         

                        • 9. Re: Clearing a Record
                          philmodjunk

                          It's not a mirror image, just a file that is an exact copy, execept for the filename as the active database file. These are invoice records, so when an invoice is printed it's not subject to future editing so it's safe for a script in the mirror file to import the invoice and line items records to produce a transaction by transaction back up. Like I said, it won't work for every database.

                          To rebuild the index of a single field:

                          1. Open Manage | Database | Fields and double click the field
                          2. Use either the storage tab or the storage options button to turn off indexing.
                          3. Exit Manage | Database, then return and turn indexing back on.

                           

                          You can also rebuild all your file's indexes by importing all the data into an empty copy (clone) of your file.

                          If you have FileMaker 11, you can use Advanced Recovery options to rebuild your file's indexes:

                          1. With the file closed, select Recover from the File Menu.
                          2. Select "Use advanced Options"
                          3. Select only: "Copy File Blocks as-is" and "Rebuild Field Indexes Now".
                          • 10. Re: Clearing a Record
                            CountryBoy1

                            Dear Phil:

                                Thank you very much for your help.  I will

                              try some of these things you've informed

                              me about.   Very Sincerely,  CountryBoy1