×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

need to either change the dynamicForecolor or DynamicBackColor in grid when exactly dupe records

need to either change the dynamicForecolor or DynamicBackColor in grid when exactly dupe records

need to either change the dynamicForecolor or DynamicBackColor in grid when exactly dupe records

(OP)
Hi everyone,
Just still experimenting, here i have a code in a search command where i can type a job no and then display all records in grid that have similar draw_no and ball_nos but if for any reason there is a record where the part_no is identical to another record too, "then this is a duplicated record", then i would like to hilite this row by using "DynamicBackColor" in that grid row or dynamic Forecolor , so when the user look into the GRID results, they can realize the actual duplicate record.

can you help here ?
notice, that i have added code for hilite alternate records as well.
below is a pic of a two records that are identical at the bottom of the grid, so those should be the one shown in a different color or background.

here is the code i used

CODE -->

Local lcJob_no
Set Safety Off
Set Cpdialog Off
Set Exclusive Off
lcJob_no= Thisform.txtTextBox.Value
Select draw_NO As OrigDrawno, draw_NO, ball_no, part_no  From c:\mfg\eng_jobs Where job_no=lcJob_no Into Cursor junk Readwrite
Replace draw_NO With Alltrim(Str(Val(draw_NO))) For !"X"$draw_NO In junk

Select j1.OrigDrawno, j2.X, j1.draw_NO, j1.ball_no, j2.part_no, j2.xcount ;
	FROM junk j1,;
	(Select draw_NO,ball_no,draw_NO+" "+ball_no As X, part_no, Count(draw_NO+ball_no) As xcount;
	FROM junk;
	GROUP By 1,2,3,4 ;
	HAVING Count(draw_NO+ball_no) > 1 ) j2 ;
	WHERE j1.draw_NO=j2.draw_NO And j1.ball_no=j2.ball_no;
	ORDER By X;
	Into Cursor RESULTS2

If _Tally > 0
	With Thisform.grid2
		.Visible = .T.
		.ColumnCount = -1
		.RecordSource = 'RESULTS2'
		.SetAll("DynamicBackColor", ;
			"IIF(MOD(RECNO( ), 2)=0, RGB(255,255,255)    , RGB(0,255,0))", "Column") 	
		
		** so i added the below, thinking that i can get the duplicated exact records and 
		** change the Forecolor for them.
		.SetAll("DynamicForeColor", ;
			"IIF(Count(draw_NO+ball_no+part_no)>1, RGB(255,0,0) , RGB(0,255,0))", "Column")				
		.Refresh()
	Endwith
	Thisform.buttonx1.Enabled =.T.
	Thisform.Sstoexcel21.Enabled=.T.
	Thisform.command3.Enabled= .T.
	Thisform.command4.Enabled=.T.
	Thisform.command2.Enabled=.T.
	Thisform.command1.Enabled= .F.
Else
	Messagebox('There is not result for your Query, Check again your Part No. Entry',0+64,'Ok!')
	Thisform.command1.Enabled= .F.
	Thisform.command3.Enabled=.F.
	Thisform.command4.Enabled=.F.
	Thisform.command2.Enabled=.T.
	Thisform.txtTextBox.Value=''
	Thisform.txtTextBox.SetFocus
	With Thisform.grid2
		.Visible = .F.
		.ColumnCount = -1
		.RecordSource = 'resultd'
	Endwith
Endif 
thanks in advance

RE: need to either change the dynamicForecolor or DynamicBackColor in grid when exactly dupe records

Quote:

.SetAll("DynamicForeColor", ;
"IIF(Count(draw_NO+ball_no+part_no)>1, RGB(255,0,0) , RGB(0,255,0))",

If I've understood your code corerctly, the above line won't work. The COUNT() function will try to count the fields across the entire cursor. You need to base the condition on the value of a field in the current record. I think you already have a field - xcount - that contains the required information. Try using that in your IIF() rather than the COUNT().

But, in any case, before you do that, suspend the program at a suitable point, and browse the cursor. That way you will know if the fault lies in the code that creates the cursor or in the DynamicForeColor code.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: need to either change the dynamicForecolor or DynamicBackColor in grid when exactly dupe records

(OP)
Hi Mike,
yes i used that to count how many records looks like dupe but Xcount it is only for the records that have in common draw_no+ball_no but i used the "Count(draw_NO+ball_no+part_no)>1" as this will actually tell that the record it is a complete duplicated record because if a record has the same part_no, draw_no and bsll_no as any other record, then it is a duplicated record, then i want this one to be hilited different, any other sugegstion thanks Mike

RE: need to either change the dynamicForecolor or DynamicBackColor in grid when exactly dupe records

Here is some sample code that will show how to flag duplicate records. I have deliberately made it simple. I don't expect you to use it as it stands, but it should give a good idea about how to proceed.

CODE -->

* Assume that the field in which we are looking for dupes
* is called Cust_ID
SELECT Cust_ID, COUNT(Cust_ID) AS lnCount FROM MyTable ;
  GROUP BY Cust_ID HAVING lnCount > 1 ;
  INTO CURSOR Dupes 
  
SELECT *, .F. AS DupeFlag  FROM MyTable ;
  INTO CURSOR GridSource READWRITE 

SELECT GridSource
SCAN
  SELECT Dupes
  LOCATE FOR GridSource.Cust_ID == Dupes.Cust_ID
  SELECT GridSource
  REPLACE DupeFlag WITH FOUND("Dupes")
ENDSCAN 


Now use GridSource to populate your grid. In your DynamicForeColor property, use DupeFlag to determine if the current record is a duplicate.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: need to either change the dynamicForecolor or DynamicBackColor in grid when exactly dupe records

(OP)
ok Mike, thanks so much will work on this, i believe so, i have to still do another select sql after this one, in order to send data to another cursor and then display all the records done by the below select sql plus find in those records the one are actually duplicated and hilite them, let me see how i accomplish it
Thanks
Ernesto

CODE -->

Select j1.OrigDrawno, j2.X, j1.draw_NO, j1.ball_no, j2.part_no, j2.xcount ;
	FROM junk j1,;
	(Select draw_NO,ball_no,draw_NO+" "+ball_no As X, part_no, Count(draw_NO+ball_no) As xcount;
	FROM junk;
	GROUP By 1,2,3,4 ;
	HAVING Count(draw_NO+ball_no) > 1 ) j2 ;
	WHERE j1.draw_NO=j2.draw_NO And j1.ball_no=j2.ball_no;
	ORDER By X;
	Into Cursor RESULTS2 

RE: need to either change the dynamicForecolor or DynamicBackColor in grid when exactly dupe records

Hi
Please find below a sketch of code how to find multiples (N>1) in a table

CODE -->

CLOSE ALL

CREATE CURSOR csrdemo (cCode C(3),cName C(10), cGender C(1))
INSERT INTO csrdemo VALUES ('001','Henry','M')
INSERT INTO csrdemo VALUES ('001','Carl','M')
INSERT INTO csrdemo VALUES ('002','John','M')
INSERT INTO csrdemo VALUES ('003','Kirk','M')
INSERT INTO csrdemo VALUES ('004','Jenny','F')
INSERT INTO csrdemo VALUES ('004','Eve','F')
INSERT INTO csrdemo VALUES ('005','Lauren','F')
INSERT INTO csrdemo VALUES ('005','Maitha','F')
INSERT INTO csrdemo VALUES ('006','Grace','F')
INSERT INTO csrdemo VALUES ('007','Roberta','F')
INSERT INTO csrdemo VALUES ('008','Mark','M')
INSERT INTO csrdemo VALUES ('008','Mike','M')
INSERT INTO csrdemo VALUES ('009','Bob','M')
INSERT INTO csrdemo VALUES ('001','Jeff','M')

SELECT csrDemo.*, csrDupes.lDupes ;
	FROM csrDemo ;
		JOIN (SELECT cCode, .T. as lDupes FROM csrDemo GROUP BY cCode HAVING COUNT(cCode) > 1) csrDupes ;
		ON csrDemo.cCode = csrDupes.cCode ;
	UNION ALL ;
		SELECT csrDemo.*, csrNodupes.lDupes ;
		FROM csrDemo ;
		JOIN (SELECT cCode, .F. as LDupes FROM csrDemo GROUP BY cCode HAVING COUNT(cCode) = 1) csrNoDupes ;
		ON csrDemo.cCode = csrNoDupes.cCode ;
	ORDER BY 1 ;
	INTO CURSOR csrAll 
	
BROWSE

CLOSE ALL
CLEAR ALL 

hth
MK

RE: need to either change the dynamicForecolor or DynamicBackColor in grid when exactly dupe records

...

and in addition a demo

CODE -->

*!*	Check for dupes in ID - Found records are hilighted, bold and italic

PUBLIC go_Form

go_Form = CreateObject ("frmForm")
go_Form.grdNames.Column1.Header1.Caption = "ID"
go_Form.grdNames.Column2.Header1.Caption = "Name"
go_Form.grdNames.Column3.Header1.Caption = "Gender"
go_Form.grdNames.Column4.Header1.Caption = "Dupes"
go_Form.grdNames.SetAll("DynamicFontItalic", "csrAll.lDupes", "Column")	  
go_Form.grdNames.SetAll("DynamicFontBold", "csrAll.lDupes", "Column")	  
go_Form.grdNames.SetAll("DynamicBackColor", "IIF(csrAll.lDupes, RGB(255,0,0), RGB(254,254,254))", "Column")	  
go_Form.Visible = .T.
go_Form.Show

READ Events
CLOSE ALL
CLEAR ALL


DEFINE CLASS frmForm As Form
  Width = 420
  MinWidth = 420
  Height = 360
  MinHeight = 360
  AutoCenter = .T.
  Caption = "Look for dupes in ID"

	ADD OBJECT grdNames as Grid WITH ;
		RecordSource = "csrAll", ColumnCount = 4, Visible = .t., Top = 12, Left = 12, Width = 396, Height = 294, Anchor = 15

	ADD OBJECT cmdCancel As CommandButton WITH;
		Width = 60, Height = 30, Left = 12, Top = 318, Caption = "Release", Anchor = 6

	PROCEDURE Load()
	
		CREATE CURSOR csrdemo (cCode C(3),cName C(10), cGender C(1))
		INSERT INTO csrdemo VALUES ('001','Henry','M')
		INSERT INTO csrdemo VALUES ('010','Carl','M')
		INSERT INTO csrdemo VALUES ('002','John','M')
		INSERT INTO csrdemo VALUES ('003','Kirk','M')
		INSERT INTO csrdemo VALUES ('011','Jenny','F')
		INSERT INTO csrdemo VALUES ('004','Eve','F')
		INSERT INTO csrdemo VALUES ('005','Lauren','F')
		INSERT INTO csrdemo VALUES ('006','Maitha','F')
		INSERT INTO csrdemo VALUES ('006','Grace','F')
		INSERT INTO csrdemo VALUES ('007','Roberta','F')
		INSERT INTO csrdemo VALUES ('008','Mark','M')
		INSERT INTO csrdemo VALUES ('008','Mike','M')
		INSERT INTO csrdemo VALUES ('009','Bob','M')
		INSERT INTO csrdemo VALUES ('002','Jeff','M')
		INSERT INTO csrdemo VALUES ('012','Jim','M')

		SELECT csrDemo.*, csrDupes.lDupes ;
			FROM csrDemo ;
				JOIN (SELECT cCode, .T. as lDupes FROM csrDemo GROUP BY cCode HAVING COUNT(cCode) > 1) csrDupes ;
				ON csrDemo.cCode = csrDupes.cCode ;
			UNION ALL ;
				SELECT csrDemo.*, csrNodupes.lDupes ;
				FROM csrDemo ;
				JOIN (SELECT cCode, .F. as LDupes FROM csrDemo GROUP BY cCode HAVING COUNT(cCode) = 1) csrNoDupes ;
				ON csrDemo.cCode = csrNoDupes.cCode ;
			ORDER BY 1 ;
			INTO CURSOR csrAll

	ENDPROC
	
	PROCEDURE cmdCancel.Click()
		CLEAR Events
		ThisForm.Release

	ENDPROC
  
	PROCEDURE Destroy()
		CLEAR Events
		ThisForm.Release

	ENDPROC
ENDDEFINE 

hth
MK

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close