Change cell or font color based on data value 1

[beetlebailey]

I am using FrontPage 2002 on Windows 2000 sp4. I am pulling data fron a SQL server and displaying it in a table containing many cells. I want to change either the font color or background color of a cell if the returned value falls below 90. Similar to conditional formatting in Excel. Is this possible in FrontPage ?

Thanks, Mark

 

[xwb]

Yes but you have to use scripting. This test sets the values manually. Try the following in a blank doc and change the values of v(1) and v(2)

<script language="vbscript">
' Set up the test data
dim v(10)
vcolmax = 2
v(1) = 85
v(2) = 95
</script>
<table border="1" cellspacing="1" width="100%" id="AutoNumber1">
  <tr>
<script language="vbscript">
   for i = 1 to vcolmax
      if v(i) > 90 then
         document.write "<td width=""50%"" bgcolor=""#008000"">" + "<font color=""#FFFF00"">" + cstr(v(i)) + "</font></td>"
      else
    	 document.write "<td width=""50%"" bgcolor=""#800000"">" + "<font color=""#FFFFFF"">" + cstr(v(i)) + "</font></td>"
      end if
   next
</script>
  </tr>
</table>

 

[beetlebailey]

I am new to FrontPage and need some additional advice. Where would this code go in the page ? What size table is you code based on ? Thanks so much.

 

[xwb]

It goes in the body.

Code is based on a 1 row, 2 (vcolmax) column table.

 

[beetlebailey]

OK, great that works wonderful. The next question is this: I am using DRW to return SQL data to a table 8 columns by 14 rows. I need to read the value of the cell at column 5 row 3 and determine if it's > or < 90 then apply the appropriate color. Am I heading in the right direction and is this even possible ?

Mark

 

[xwb]

Anything is possible: just how. A few questions to start.

What is DRW? Is it the same as the frontpage generated database stuff - lots of grey commented out bits and if you modify any of the code that follows, it changes it back to what it used to be.

Is it a DSN-less connection type thing?

Is it ASP (has some <% and %> in it)?

 

[xwb]

I just looked up DRW on the net - Database Results Wizard. Never knew that that was what it was called. I'll run a few experiments and get back to you.

In the mean time, have a look at

http://www.tek-tips.com/viewthread.cfm?qid=1050743&page=1

It is on how you can bypass the autogenerated stuff.

 

[beetlebailey]

DRW = Database results wizard
Yes, it's an ASP page.
Yes, greyed out comments and stuff.
Yes, <% and %>
System DSN connection to SQL database
Thanks so much for the assistance, Mark

 

[xwb]

Put this before the generated table

<%
dim count, colourcell
sub xmit (colname)
   count = count + 1
   if count = colourcell then
      cellstr = FP_FieldVal (fp_rs,colname)
      cellval = cint(cellstr)
      if cellval < 90 then
         response.write "<td bgcolor=""#800000"">" + cellstr + "</td>"
      else
         response.write "<td bgcolor=""#008000"">" + cellstr + "</td>"
      end if
   else
      response.write "<td>" + FP_FieldVal(fp_rs,colname) + "</td>"
   end if
end sub

' Number of columns
colmax = 14
' Row and column to be coloured
colourcol = 5
colourrow = 3
colourcell = (colourrow - 1) * colmax + colourcol
' Start counting
count = 0
%>

Look for <!--webbot bot="DatabaseRegionStart"
In the generated code between each <td> </td> pair, look for something like FP_FieldVal(fp_rs,"xxx")
Change this to xmit "xxx"
Now replace the <tr> </tr> pairs with result.write statements. You should end up with something like

<!--webbot bot="DatabaseRegionStart" i-CheckSum="63110" endspan -->
<%
response.write "<tr>"
xmit "ID"
xmit "Stock Number"
xmit "Description"
xmit "Type"
xmit "Model Number"
xmit "Price"
xmit "In Stock"
response.write "</tr>"
%>
    <!--webbot bot="DatabaseRegionEnd" startspan b-tableformat="TRUE"

And that's it!!!