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?
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.
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.)
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
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.
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.
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.
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
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:
- Open Manage | Database | Fields and double click the field
- Use either the storage tab or the storage options button to turn off indexing.
- 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:
- With the file closed, select Recover from the File Menu.
- Select "Use advanced Options"
- Select only: "Copy File Blocks as-is" and "Rebuild Field Indexes Now".
Thank you very much for your help. I will
try some of these things you've informed
me about. Very Sincerely, CountryBoy1