ColdFusion How can I change the color of text if a match is detected?

I have a searchable database producing a list of records that matches the criteria the user inputted. How can I turn the results of a specific field red if a dupe is detected?

Database Fields:
Serial     MFGNo

Logic: If Serial & MFGNo match with another record display red text.
DJPr0Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_agx_Commented:
In SQL or in CFML? Can you give an example of what you mean by "dupe"?
DJPr0Author Commented:
_agx_,

In ColdFusion with an MS Access database.

Dupe example:
Record Results after inputting search criteria:
Fields
PartName  Serial  MFGNo
Gasket        123     456         (123 text should be red due to dupe in Wrench (Serial & MFGNo need to match))
Sprocket     554    823    
Wrench       123    456         (123 text should be red due to dupe in Gasket (Serial & MFGNo need to match))
_agx_Commented:
Serial & MFGNo need to match

You mean mark it as a dupe because both of their Serial and MFGNo's are the same?

Gasket        123     456
Sprocket     554    823    
Wrench       123    456
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

_agx_Commented:
Can't try it with Access, but I think something like this should work ....  

select  PartName
		, Serial
		, MFGNo
		, (select count(*) 
		   from   yourTable dupe
		   where  dupe.Serial = t.Serial
		   and    dupe.MFGNo = t.MFGNo
		   and    dupe.PartName <> t.PartName
		) AS DupeCount
from   yourTable t		

Open in new window


Check the "DupeCount" value in CFML, if it's > 0 you know it's a dupe

<cfoutput query="....">

    <cfif DupeCount>
          ... apply "red" style for duplicate record
    </cfif>
</cfoutput>

Open in new window

DJPr0Author Commented:
Thanks _agx_,

Can we leave the PartName out in the SQL statement?
_agx_Commented:
Yes. If you mean omit it from the dupe count subquery, that would change the CFML slightly.  Note the addition of "(search filter criteria here ...)"

 select  Serial
		, MFGNo
		, (select count(*) 
		   from   yourTable dupe
		   where  dupe.Serial = t.Serial
		   and      dupe.MFGNo = t.MFGNo
                 and     (search filter criteria here ...)
		) AS DupeCount
from      yourTable t		
where    (search filter criteria here ...)

Open in new window


With the new SQL, the dupe count would always be at least "1". CF considers that to mean true, so the CFML would become:
<cfoutput query="....">

    <cfif DupeCount GT 1>
          ... apply "red" style for duplicate record
    </cfif>
</cfoutput>

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DJPr0Author Commented:
_agx_,
Seems to work fine without the search filter criteria in the subquery:
    and      dupe.MFGNo = t.MFGNo
                 and     (search filter criteria here ...)
            ) AS DupeCount

The code below seems to be working fine; I'm not sure why I need to input 2 search criteria's, one in the sub and one in the main.

SELECT * , (select count(*) 
FROM   AccessTable dupe
where  dupe.Serial = t.Serial
and    dupe.MFGNo = t.MFGNo
		   
		) AS DupeCount
from   AccessTable t
WHERE 1=1  
(Bunch of CFif statements here)  		

Open in new window




<cfif DupeCount GT 1>
          ... apply "red" style for duplicate record
    </cfif>       

Open in new window

_agx_Commented:
The code below seems to be working fine; I'm not sure why I need to input 2 search criteria's, one in the sub and one in the main.

Because without it the count includes all records in the table, not just records returned in the search.  Not sure that's what you want.
DJPr0Author Commented:
Thanks _agx_!
_agx_Commented:
Glad it helped!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.