Mark duplicate records based on a query

[randygreen]

I'm trying to mark duplicate records in a browse (that is a subset of the records example city is "Los Angeles" or "Chicago"), but the "mark duplicate records" operation is selecting from all the records versus just those in Los Angeles and Chicago.

their must be simple answer? thanks in advance...

Randy

[Tom Cone Jr]

Randy, more details would be helpful. This shouldn't be a problem, but you don't tell us about your efforts to filter the operation. Are you aware that it can be filtered? What's the context in which this is being run? What's your filter expression ?

-- tom

[randygreen]

while I have a more complicated project with a number of child tables, the answer would be the same for the following scenerio:

I have a CustomerName table with 700 total records. The end goal is to generate a report that would list duplicate records (on LastName field) within the cities of Los Angeles and Chicago only (which have 150 records if I created a query with city="los angeles" .or. city="chicago"). So my plan was to do the initial los angeles and chicago query and then use the Mark Duplicate Records operation. The thought was once I have these records marked, I can print a report. However the Mark Duplicate Records command searched through all 700 records versus the 150 subset.

Any suggestions would be very much appreciated. thanks very much in advance...

Randy

[Tom Cone Jr]

Randy, I'm sorry to say that I mis-spoke. Other predefined batch operations permit one to select a range of records for processing using a filter expression. The mark duplicates operation does not. Sadly, this is news to me. Sorry. I suggest this be posted as a new feature over in the Features Wishlist forum.

Since you plan to run a report on the marked duplicates, I'd recommend you use a temporary table as the source for the report. This table would be filled before running the report, and emptied afterwards. You could define a new copy operation to copy all records with "los angeles" or "chicago" in the city field to the temporary table, then run the mark duplicates on the temporary table, then run the report (also on the temporary table), filtering for only the marked records. The entire sequence could be scripted and transparent to the user.

-- tom

[Robert Adler]

Mark duplicate records

Randy:

Using the Mark Duplicate operation you would select the fields needed to perfrom the duplication. Then in the tool bar choose "Select Records". The filter for the cities can then be chosen.

I hope this helps.

bob adler

[Tom Cone Jr]

Thanks Bob.

that's what I was looking for earlier today. Couldn't see it for love nor money! Guess this isn't my day.

Randy, you'll find it also in the "Mark" menu at the top of the design dialog.

-- tom

[randygreen]

found it and it works as described. thanks very much...

Randy

[martinwcole]

you can also do it another way:

t=table.open("patients")
query.filter=".not. key_unique(" + quote("patient_id") + ")"
query.order="patient_id"
idx=t.query_create()
t.mark_range()

[brett s]

Just an issue I can see with "marking" records in this way that arises in a multi-user environment because marking results in that marked record being marked for all users, not just the user who marked it. So if one user marks records and another unmarks the same records, or marks others, they are going to trip over one other.

I may be wrong here, I am no expert. But I remember back to my dBase days (A5 uses the dBase table format) when records were "marked" for deletion without actually being physically deleted. A table compact was required to physically delete all marked records.

I believe A5 works the same way and indeed I see examples in the A5 Xbasic documentation showing records being locked/unlocked while the marking/unmarking takes place. This tends to suggest to me that the records are being marked globally for all users, not just the user doing the marking.

So marking duplicates may be fraught with dangers in a multi-user environment. Tom's suggestion is one way around this problem. I think another is to use the "U" query option in the "<tbl>.query_create()" Xbasic method to kill duplicates.

[Tom Cone Jr]

Brett, that's a good point. It's also useful to remember that marks persist. They're part of each marked record, until unmarked. Processing that requires use of marked records probably should unmark all records in the table first. The propriety of doing this in a multi-user should be given some thought.

-- tom

[brett s]

Yes, good point. And the other thing is if I mark records and then run a database or table compact, all my marked records will be deleted. Can be a trap for the uninitiated. ;)

[Tom Cone Jr]

Marked records are not physically removed.

Yes, good point. And the other thing is if I mark records and then run a database or table compact, all my marked records will be deleted. Can be a trap for the uninitiated. ;)

There's a big difference between marking records and deleting records, though sometimes folks will describing "deleting records" as "marking records for deletion".

Records "marked for deletion" (i.e. deleted records) can't be seen using the default browse or form, and will be physically removed when the table is packed, or the database compacted.

Records that have simply been "marked" are not. When using the default form for a table, and displaying it full screen, these records remain visible and there's a check mark in the 3rd box at lower left for each marked record. These marks are not removed by packing, nor are the marked records physically deleted.

-- tom

[brett s]

Thank's, Tom. That's useful information. What about records that are simply marked/unmarked (but not for deletion). If one user simply marks a record is it also marked for another user or it just marked for the marking user?

Also, does simply marking entail an overhead that arises from having to physically write to all marked records?

(Gee, I hope I can understand this.) ;)

[Tom Cone Jr]

Brett, the "mark" is stored in the record, so a "marked" record will appear "marked" for all users. (In the same way a "deleted" record will disappear for all users). Marking/UnMarking a single record involves no more overhead than would otherwise be required to change a single byte in the record and save it back to disk. Marking/UnMarking a range of records (using a saved mark/unMark operation) will require the same overhead that would be required by any other "Update" operation which changes a single byte in the same range of records. -- t

[brett s]

Thanks, Tom. Your answer has clarified this matter for me. Clearly marking users in a multi-user application is not the way to go. Maybe you (or someone) can suggest a better alternative. Here are some details.

I have a large table (maybe up to 300k records) and I want a user to be able to "select", in some way, some of those records (maybe up to 100k but more likely to be 1 to 20k in number) and combine them with another such number of records, also selected in the same manner. Then to output the combined two selections as a CSV text file. The records fortunately do have a unique identifier, a Client Number (ClNo).

The selection is being done by an A5 Query using the Query Genie and based on many other fields in the client record too. But I am having trouble (expounded in another of my posts on the A5 forum) saving the query result, retrieving the query result by name later and combining the two selection results together into one query result and then removing duplicates on ClNo.

I don't want to use intermediary tables to achieve the selection and combining, because I would need one set of tables per user. This and it would be far too slow in an on-line multi-user environment anyway.

As you can see, this is a really demanding call. Do you have any suggestions? Perhaps there is a better way to do it?