How do I highlight a number on the table when a related field is not empty?

[Jose Santos]

I am using a very useful application known as GenericDB by Eli Robillard. I have a classic ASP application that requires that when a specific field contains user comments (not empty) pertaining to a particular field (which is numeric), that numeric field needs to be color highlighted or be marked with an asterisk when displayed on a table. The Lister in GenericDB has the following section that I think is where the modified code needs to be inserted. Any suggestion on how to make the modification would be appreciated.

Thanks.
jose

				Select Case aFields(x,2)
					case 2, 3, 4, 5, 17  ' Integers, Reals
						if curVal <> "&nbsp;" then curval = FormatNumber(curval,0,,0) 'use commas with no decimals
					case 6 ' Currency
						if curVal <> "&nbsp;" then curval = FormatCurrency(curval,0,-1) 'replaced 2 with 0 to round off numbers
					case 11 ' Boolean
						if curVal then
							curVal = txtTrue
						else
							curVal = txtFalse
						end if
					case 7, 135 ' Date / Time: 0=General, 1=LongDate, 2=ShortDate, 3=LongTime, 4=hh:mm
						if curVal <> "&nbsp;" then curVal = FormatDateTime(curVal, Mid(strFormatDate, x, 1))
					case 130, 201, 202, 203, 204 ' String or Memo
						' Image
						if (UCase(Left(aFields(x,1),3)) = "IMG") then
							if Session("dbMaxImageSize") = 0 then
								curVal = "<img src=" & QUOTE & curVal & QUOTE & " alt=" & QUOTE & QUOTE & " />" 
							else 
								' Opens image in a new window. Use Session("dbMaxImageSize") to set "thumbnail" size
								curVal = "<a href=" & QUOTE & curVal & QUOTE & " target = _new><img alt=" & QUOTE & txtClickToView & QUOTE & " src = " & QUOTE & curVal & QUOTE & " width=" & QUOTE & Session("dbMaxImageSize") & QUOTE & " />" & "</a>" 
							end if
							IsLink = true
						end if

[Jose Santos]

The logic seems simple enough:

strComment=Session("comment")

'Devise a function; this is simplistic

function HighlightMe(ByVal) 
If strComment <> "" then 
HighlightMe = "Black" 
Else 
HighlightMe = "Red" 
End If 
end function


'Modify code above

If curVal <> "&nbsp;" then curval = HighlightMe(FormatNumber(curval,0,,0))
.
.
.

I understand that the code needs tweaking. I would appreciate any help.

Thanks.
Jose

[jhardman]

The thing that I think is making this hard for you to grasp, is that the table code you are using has some key features that make it ideal for making a table out of an unknown db recordset. Those same features make it hard to do what you are trying to do partly because it treats the recordset as an unknown.

What I would do is look for the beginning of the loop that has the recordset.moven ext command at the end (the movenext command is going to be at teh end of the loop, but you need to go to the beginning). I would declare a variable called "isCommentEmpty " and say something like this:

if recordset("commentField") = " " then
   isCommentEmpty = true
end if

Then down in the line that says case 2,3,4,5,17 you need to add an if statement:

if aFields(x,2).name = "fieldName" then
   'highlight code here
end if

The key to this code is you have to know which fields are which, and it's not especially compatible with what you have above.

Jared

[Jose Santos]

Hi Jared:
You are right on target as regards my general feeling about the application. It is really very easy to use for simple databases but oftentimes I found myself scratching my head on how to modify it to suit my specific need. Anyway, it has helped me a lot and 'could not complain except in cases such as this one. Jared, if you think you can help me more here is the loop routine that you described above:

<%
intCount = 0
Do While (NOT xrs.EOF) AND (intCount < intStopRec)
	intCount = intCount + 1
	if Cint(intCount) >= Cint(intStartRec) then %>
<tr> <%
		' Get the key
		KeyField = afields(intKey,1)
		curKey = xrs(Keyfield)
		if NOT (curKey & "x" = "x") then 
			if isNumeric(curkey) then
				strLink = "KEY=" & curKey
			else ' assume text
				strLink = "KEY='" & LTrim(curKey) & "'"
'				strLink = Replace(strLink," ","%20")
			end if
		end if
		x = 0
		y = 0
		For Each xField in xrs.Fields
			IsLink = false
			x = x + 1 ' field no.
			curVal = xField.Value
			' Every other line will have a shaded background
			bgcolor="#FFFFFF"
			if intCount mod 2 = 0 then bgcolor=strRowColor
			bgcolorShade = gdbShade(bgcolor)
			if Mid(strDisplay, x, 1) = "1" then 
				y = y + 1 
				' Empty / Null / Blank
				if IsNull(curVal) OR (Trim(curVal) & "x" = "x") then 
					curVal = "&nbsp;"
				else
					if (Mid(strTotalFields, x, 1) = "1") AND IsNumeric(curVal) then aFields(x,4) = aFields(x,4) + curVal
				end if %>
	<td bgcolor="<%=bgcolor%>" valign="top" <% if IsNumeric(curVal) then response.write "align=" & QUOTE & "right" & QUOTE %>><font size="<%=intFontSize%>" face="<%=strFont%>"> <%
				' Password
				if UCase(Left(aFields(x,1),8)) = "PASSWORD" then curVal = "*****"
				' Everything else, by field type
				Select Case aFields(x,2)
					case 2, 3, 4, 5, 17  ' Integers, Reals
						if curVal <> "&nbsp;" then curval = FormatNumber(curval,0,,0) 'use commas with no decimals
					case 6 ' Currency
						if curVal <> "&nbsp;" then curval = FormatCurrency(curval,0,-1) 'replaced 2 with 0 to round off numbers
					case 11 ' Boolean
						if curVal then
							curVal = txtTrue
						else
							curVal = txtFalse
						end if
					case 7, 135 ' Date / Time: 0=General, 1=LongDate, 2=ShortDate, 3=LongTime, 4=hh:mm
						if curVal <> "&nbsp;" then curVal = FormatDateTime(curVal, Mid(strFormatDate, x, 1))
					case 130, 201, 202, 203, 204 ' String or Memo
						' Image
						if (UCase(Left(aFields(x,1),3)) = "IMG") then
							if Session("dbMaxImageSize") = 0 then
								curVal = "<img src=" & QUOTE & curVal & QUOTE & " alt=" & QUOTE & QUOTE & " />" 
							else 
								' Opens image in a new window. Use Session("dbMaxImageSize") to set "thumbnail" size
								curVal = "<a href=" & QUOTE & curVal & QUOTE & " target = _new><img alt=" & QUOTE & txtClickToView & QUOTE & " src = " & QUOTE & curVal & QUOTE & " width=" & QUOTE & Session("dbMaxImageSize") & QUOTE & " />" & "</a>" 
							end if
							IsLink = true
						end if		
						' Check for dbEMailFor setting
						strContainsURL = "dbemailfor" & CStr(x)
						if (Session(strContainsURL) > 0) and NOT(IsLink) then
							strURL = xrs(aFields(Session(strContainsURL),1))
							if Trim(strURL) & "x" <> "x" then
								IsLink = true
								strURL = Replace(strURL,"mailto:","")
								strURL = "mailto:" & LTrim(strURL)
								curVal = "<a href=" & QUOTE & strURL & QUOTE & ">" & curVal & "</a>"
							end if
						end if
						' Check for dbURLFor or dbUpload setting
						strContainsURL = "dbURLfor" & CStr(x)
						strContainsUpload = "dbUpload" & CStr(x)
						if NOT(Session(strContainsUpload) & "x" = "x") and (NOT IsLink) then 
							' Convert it as though it were a dbURLFor setting
							Session(strContainsURL) = x
						end if
						if (Session(strContainsURL)) > 0 AND (NOT IsLink) then
							strURL = xrs(aFields(Session(strContainsURL),1))
							if strURL & "x" <> "x" then
								IsLink = true
								curVal = "<a href=" & QUOTE & strURL & QUOTE & ">" & curVal & "</a>"
		' *** Uncomment the following line to strip all #'s from hyperlink fields
		'						curVal = Replace(curVal,"#","")
							end if
						else
							if (UCase(Left(curVal,7)) = "HTTP://") then
								IsLink = true
								curVal = LT & "a href=" & QUOTE & curVal & QUOTE & " target=" & QUOTE & "_blank" & QUOTE & GT & curVal & LT & "/a" & GT
		' *** Uncomment the following line to strip all #'s from Access hyperlink fields
		'						curVal = Replace(curVal,"#","")
							end if
		 				end if
						' Check for other hyperlink setting (dbLinkField)
						strContainsURL = "dbLinkField" & CStr(x)
						if (Session(strContainsURL) & "x" <> "x") AND (NOT IsLink) then
							IsLink = true
							if curVal & "x" <> "x" then curVal = gdbBuildLink(strContainsURL) & curVal & "</a>"
							if left(curVal,5) = "EMPTY" then curVal = "&nbsp;"
						end if
						' Replace potentially harmful code
						if (NOT IsLink) then curVal = gdbSterilizeText(curVal,CInt(Mid(strDispHTML, x, 1)))
				end select
				Response.Write curVal & "&nbsp;</td>"
			end if 

		Next 

		' Show links set by dbLink
		x = 1
		do while NOT(Session("dbLink" & x) & "x" = "x")	%>
	<td bgcolor="<%=bgcolor%>" align="center"><font size="<%=intFontSize%>" face="<%=strFont%>"><%
			curVal = gdbBuildLink("dbLink" & x)
			if curVal = "EMPTY" then 
				Response.write "<a name=""BLANK"">"
			else
				Response.write curval 
			end if
			response.write "</a></font>"
			x = x + 1
		loop

		if (Session("dbDispView") <> "")  and intKey > 0 then %>
	<td bgcolor="<%=bgcolor%>" align="center"><font size="<%=intFontSize%>" face="<%=strFont%>"><a href="<%=Session("dbGenericPath")&strViewer%>?<%=strLink%>"><%=txtView%></a></td> <%
		end if 
		if (Session("dbCanEdit") = 1)  and intKey > 0 then %>
	<td bgcolor="<%=bgcolor%>" align="center"><font size="<%=intFontSize%>" face="<%=strFont%>"><a href="<%=Session("dbGenericPath")%><%=strEditor%>?<%=strLink%>"><%=txtEdit%></a></td> <%
		end if 
		if (Session("dbCanCopy") = 1) and session("dbCanAdd") = 1 and intKey > 0 then %>
	<td bgcolor="<%=bgcolor%>" align="center"><font size="<%=intFontSize%>" face="<%=strFont%>"><a href="<%=Session("dbGenericPath")%><%=strEditor%>?<%=strLink%>&CMD=COPY"><%=txtCopy%></a></td> <%
		end if
		if (Session("dbCanDelete") = 1)  and intKey > 0 then %>
	<td bgcolor="<%=bgcolor%>" align="center"><font size="<%=intFontSize%>" face="<%=strFont%>"><a href="<%=Session("dbGenericPath")%>GenericDelete.asp?<%=strLink%>"><%=txtDelete%></a></td> <%
		end if %>
</tr> <%
	end if
	xrs.MoveNext
Loop

Thanks for your help.
Jose

[jhardman]

Jose, do you know the names of the db fields that you are talking about? What are the field names for the comment field and the numeric field that needs to be highlighted?

Jared

[Jose Santos]

Hi Jared:
I got 34 of them. But for sample purposes let's use just the five of them as follows:

Numeric Field (in $):...........C omment Field (memo):
FederalAlloc... ............... ..C_FederalAllo c
StateAlloc ............... ......C_StateAl loc
CountyAlloc ............... .....C_CountyAl loc
NonNIFAAlloc... ............... ..C_NonNIFAAllo c
NIFAAlloc...... ............... ..C_NIFAAlloc

Thanks. I really appreciate you helping me on this.

Jose

[Chuck Speerly]

Jose
You might want to look at my revised version of Genericdb
I just releasd GDBR4. It uses CSS style sheets, which is required. The Generic File are completely rewritten with somthings put into functions, but the concept is still the same.
I have a Session("dbFiel dShowAs6") = "FONT,<font Color=#990000>
whis will display that field red

dbShowField as can be set to CONTAINS,FONT,T EXT and IMAGE

See my gdbHelp at "http://www.stauctions. com/GDBHelp/Default.asp

or here is the code i used.
'CAS Added for Session("dbFiel dShowAsX")
if Session("dbFiel dShowAs" & x) & "x" <> "x" then

arrFieldShowAs = split(Session(" dbFieldShowAs" & x),",")

select case arrFieldShowAs( 0) --------------------------------------

Case "FONT"
curVal = arrFieldShowAs( i+1) & curVal & "</font>"

Case "IMAGE"
for i = 1 to ubound(arrField ShowAs) step 1
if ucase(trim(curV al)) = ucase(trim(arrF ieldShowAs(i))) then curVal = "<img src=" & Quote & arrFieldShowAs( i+1) & Quote & ">"
next

Case "TEXT"
for i = 1 to ubound(arrField ShowAs) step 2
if ucase(trim(curV al)) = ucase(trim(arrF ieldShowAs(i))) then curVal = arrFieldShowAs( i+1) & curVal & "</font>"
next

Case "CONTAINS"

' To determine if a string contains a substring,
if ucase(Session(" dbReplaceShowAs " & x)) = "YES" then curVal = replace(curVal, " ","")

for i = 1 to ubound(arrField ShowAs) step 2
If InStr(ucase(cur Val),ucase(arrF ieldShowAs(i))) <> 0 Then curVal = replace(curVal, arrFieldShowAs( i),arrFieldShow As(i+1) & arrFieldShowAs( i) & "</font>")
next

Case "NOTNULL"
If curval


end select

end if
end if