"essentially a duplicate" is a phrase that brings up the question: "do you really need it?" as duplicating data is what a relational database is intended to avoid. But it's your database and there's no reason to get into that kind of discussion if you don't want to.
You've indicated that you use a script and Set Field to create the records in Table 2. But that's all that you have told us. Any number of factors might be involved here from an incorrectly written script, a script that is performed at the wrong time, a damaged field index, some other form of file corruption or you might actually have discovered a bug in the software.
But without a more detailed description of how/when you create a record in Table 2 from a record in table 1, we really can't narrow down this list of possible issues.
Thanks for the quick reply.
So, here is the reason for the duplication. So, Table 0 is called "Pairs", and it represents a pair of DNA sequences that need to be assembled, one forwards and one backwards. The design for forward version and the backwards version are each stored in a separate table; it is when these records are created that each of the 16 position values are parsed out.
Now, when I want to actually build these guys, they are copied into Table 2, each from their separate table--essentially I'm collating them back into list that I can build. The copy is via a simple looped Set Field script. I tag the ones that are ready to build, and then it goes through and uses Set Field to copy the values over.
99% of the time, it works. It is just that some of the records created in Table 2 are definitively NOT the original record. Instead, they contain other values that are derived somehow from random *other* fields in Table 0.
PS. Yeah, I know its kludgy. But it worked perfectly until now, when suddenly and randomly it inserts these weird records.
I can't follow all the details of that, but don't think that I need to.
How do you "tag" a record to select it?
How does your script find the records that are "tagged"?
The script find a set of records in the forward version of table 1 and then a script lops through and copies them one after another to table 2 by setting the fields; it then goes to the "reverse" version and does it again.
Let me stress that it faithfully copies almost all of the records, but on occasion, instead of copying one of those records record to table 2, it appears to copy something that doesn't appear to exist in the table. Instead it inserts values that seem to be derived from random fields in parent table 0.
You didn't answer my first question. And I have a reason for asking it:
How do you "tag" a record to select it?
Sorry. There is a "tag" field in the table. 0 if it needs to be built. The script sets it 1 once it has been copied over. So I find for all records where the tag is 0 and goes from there.
You mentioned that this database is hosted from Server. Is there ever more than one user doing this at the same time?
If there is, your find might well be finding records tagged by another user.
A script might also fail to clear this field if another user has the field open for editing at the time that you run this script and then a subsequent run of this script might find that record as it is still "tagged".
If these prove to be possible scenarios, you may need to use a different method for tagging the records that creates records in a related table with copies of the "tagged" record's primary key. These can be further marked with an account name to keep your selections separate from another user's.
But if the above issues aren't why this is happening, it's possible that you might have a corrupted index that is keeping your script from correctly finding the right records. You may need to take the file down off the server and use the following advanced recover options to rebuild the field indexes in your file:
Copy File Blocks "As Is"
Rebuild all Field Indexes [Now]
You're missing the point.
Every so often, a record being copied from Table 1 to Table 2 isn't actually copied. Instead, something bogus gets placed into table 2 instead. And what is put in is a bit of a random field from Table 0. And to make matters more interesting, the values of the other 16 fields are correct for the bogus field, and those values are only created when a record is created in Table 1. So it is as if what I see in a record in Table 1 is NOT what the database sees when it is copying the record.
Which is why I also included the info on rebuilding the field indexes. In fact, given that last post, I'd take either the copy on the server or a very recent back up copy and run a full recover on the file and see what is reported. Even if no problems are reported by the recover, I'd then test that recovered copy to see if this issue still occurs.
OK. I'll give it a try. I guess the test will be whether the problem happens again. The problem, of course, is that it is intermittent, but if it doesn't recur after the recovery, we'll call it good and chalk it up to a ghost in the machine.
Still, it's seriously weird.
At least I've now built in a check system that compares what gets copied to the original and flags me if it is wrong.
Thanks for the help! I'll come back to this thread if it doesn't work.
Have a great evening!
intermittent issues are a major pain in the neck an parts south. I'm tracking such an issue in FM GO with TSGal's assistance and it gets really frustrating.
And my concerns about how your tag your records are still valid even if they are not a factor in the current situation.
OK, well, I've rebuilt it with no errors found. Now we just wait and see if it happens again.
I guess the tagging could still be a problem, though how that leads to ghost records is a complete mystery.
Maybe I should just put on my big boy pants and redesign the thing without the silly kludge. It'd would be better all around, methinks.
Thanks again, and good luck with your own slice of the weird.
I didn't bring up the tagging issue just because it may be a factor in these "ghost" records, but because it can also affect the results you get in the future even after this issue is fixed.
User A tags a record by putting a 1 in the tag field. User B tags a different record by putting a 1 in the tag field of a different record. User A runs the script and it then pulls up a found set of records tagged by both User A and B....