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.movenext 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 $):...........Comment Field (memo):
FederalAlloc....................C_FederalAlloc
StateAlloc .....................C_StateAlloc
CountyAlloc ....................C_CountyAlloc
NonNIFAAlloc....................C_NonNIFAAlloc
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("dbFieldShowAs6") = "FONT,<font Color=#990000>
whis will display that field red

dbShowField as can be set to CONTAINS,FONT,TEXT and IMAGE

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

or here is the code i used.
'CAS Added for Session("dbFieldShowAsX")
if Session("dbFieldShowAs" & 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(arrFieldShowAs) step 1
if ucase(trim(curVal)) = ucase(trim(arrFieldShowAs(i))) then curVal = "<img src=" & Quote & arrFieldShowAs(i+1) & Quote & ">"
next

Case "TEXT"
for i = 1 to ubound(arrFieldShowAs) step 2
if ucase(trim(curVal)) = ucase(trim(arrFieldShowAs(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(arrFieldShowAs) step 2
If InStr(ucase(curVal),ucase(arrFieldShowAs(i))) <> 0 Then curVal = replace(curVal,arrFieldShowAs(i),arrFieldShowAs(i+ 1) & arrFieldShowAs(i) & "</font>")
next

Case "NOTNULL"
If curval


end select

end if
end if