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

[Ernesto_Paez]

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

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

 

[Mike Lewis]

.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

 

[Ernesto_Paez]

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

 

[Mike Lewis]

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.

* 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

 

[Ernesto_Paez]

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

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

 

[mjcmkrsr]

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

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

 

[mjcmkrsr]

...

and in addition a demo

*!*	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