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.
Database Fields:
Serial MFGNo
Logic: If Serial & MFGNo match with another record display red text.
_agx_
In SQL or in CFML? Can you give an example of what you mean by "dupe"?
ASKER
_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))
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))
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
Can't try it with Access, but I think something like this should work ....
Check the "DupeCount" value in CFML, if it's > 0 you know it's a dupe
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 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>ASKER
Thanks _agx_,
Can we leave the PartName out in the SQL statement?
Can we leave the PartName out in the SQL statement?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
_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.
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) <cfif DupeCount GT 1>
... apply "red" style for duplicate record
</cfif> 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.
ASKER
Thanks _agx_!
Glad it helped!