Query Problem

[Judith]

Hi

I want to look at records in a browse table that are duplicates, I have tried using the following expression

previous("index_field")="index_field"

but without any luck. I don't want to use the view duplicates genie as I want to make changes to the duplicate records as I view them, I really just need to see the master record along with any duplicates in the browse table.

Can anyone help.

Thanks

Judith

[G Gabriel]

Re: Query Problem

Would a simple sort do?

[Tom Cone Jr]

Re: Query Problem

Judith,

There may be a more elegant solution, but if no one suggests an alternative consider pre-processing the child table in order to "mark" the records that have been duplicated. You could do this with a script that has two steps:

a) unmark all the records in the table, using a saved unmark operation
b) mark the duplicates using a saved mark operation and a filter based on the dbcount() function. i.e. mark records where dbcount() is greater than 1.

Then filter the child table in your form to only show marked records.

-- tom

[Judith]

Re: Query Problem

Hi,

A simple sort wouldn't work because I would have all the records in the table not just the master record and its duplicates.

I have tried the suggestion from Tom and unmarked all the records, but if I carry out this instruction, wouldn't I just end up with the marked duplicates in the browse table but still not be able to view the master record. Sorry if I sound a bit dumb, but I am quite new to all this, I am very much a user rather than a techie. Thanks.

[Tom Cone Jr]

Re: Query Problem

Tell us what you mean by the "master record".

I was assuming you have a two table set, with the "master" record in the parent table, linked one to many to the child table. A form based on this kind of relationship could include fields from the parent table, and an embedded browse object displaying the child table records. The child table could be filtered to display only the marked records.

It now sounds to me as though you mean something else. Please clarify. Give us an example to illustrate the explanation.

-- tom

[G Gabriel]

Re: Query Problem

Tom:
I think what Judy wants is a way to ONLY show those records and their duplicates.

For example, if you have a table with the following values in field_A

A
B
C
C
D

She wants to filter the table to only show:
C
C

It's doable with a script, or possibly a trick, but for now, I have to go to my dentist.

[Tom Cone Jr]

Re: Query Problem

G, you may be right. But she also wants to show something she calls the "master record" associated with the duplicates... I'm waiting on clarification and hopefully she'll provide an example that can bridge the language barrier between Brits and Yanks <grin> -- t

[G Gabriel]

Re: Query Problem

Tom:
If you mark duplicates and filter for marked records, alpha will only mark the duplicate record. So, in the previous example you will end up with:
A
B
C .....she is refering to this as "Master"
C (marked) .....she refers to this as the "duplicate"
D

If you filter for marked records, you will see only the marked one, but not it's pair. She is refering to the original record as the "master". She wants to be able to see both the marked as well as the unmarked duplicates.

Quite frankly I am not sure what is the purpose of this. Typically you want to do something with the duplicate, what would you want to do with both? Who knows. But nonethless, I think that's what she is looking for.

[Judith]

Re: Query Problem

Hi,

Have been to lunch and have seen you both trying to get me sorted on this, thank you. I do mean records in just one table and wanting to view the unmarked record and it duplicates:

I have attached a screenshot to show the field I am working on, hope this helps.

[Stan Mathews]

Re: Query Problem

Hi,

Have been to lunch and have seen you both trying to get me sorted on this, thank you. I do mean records in just one table and wanting to view the unmarked record and it duplicates:

I have attached a screenshot to show the field I am working on, hope this helps.

This should mark all records which have duplicates in the index_field. Change the first line to reflect the name of your table.

tbl = table.open("[COLOR="Red"]check[/COLOR]",FILE_RW_EXCLUSIVE)
tbl.unmark_range()
query.order = "index_field"
query.filter = ""
query.description = ""
query.options = "I"
tbl.query_create()
tbl.fetch_first()
ind = tbl.Index_field
tbl.fetch_next()
while .not. tbl.fetch_eof()
	if tbl.Index_field = ind
		tbl.fetch_prev()
		tbl.change_begin()
		tbl.mark()
		tbl.change_end(.t.)
		tbl.fetch_next()
		tbl.change_begin()
		tbl.mark()
		tbl.change_end(.t.)
	end if
		ind = tbl.Index_field
		tbl.fetch_next()	   
end while

[Tom Cone Jr]

Re: Query Problem

Stan, good morning.

Your approach resembles what I suspect is going on under the hood with

<tbl>.mark_dups2()

-- tom

[Stan Mathews]

Re: Query Problem

Stan, good morning.

Your approach resembles what I suspect is going on under the hood with

<tbl>.mark_dups2()

-- tom

Looks to be so. I'm still doing most of my work in V5 so I'm not at full speed on functions and methods introduced in later versions. Thanks for the heads up.

[Judith]

Re: Query Problem

Hi Stan,

I have copied and pasted the code you kindly sent. I have amended the first line to read:

tbl = codata_1.open("check",FILE_RW_EXCLUSIVE)

where codata_1 is the name of the table I am working on, but I am getting an error message which says:

line:5
tbl = codata_1.open("check",FILE_RW_EXCLUSIVE)
Not found
codata_1 not found.

Sorry I am making heavy weather of this aren't I.

[Stan Mathews]

Re: Query Problem

Should be

tbl = table.open("codata_1",FILE_RW_EXCLUSIVE)

[Judith]

Re: Query Problem

Tom, thank you so much for all your help, the job I needed to done can be completed this afternoon which would have taken me days.