Generate Querystring Item From Array

LongFeiFengWu · Jul 6, 2004

Here is the script I'm working with:

FName = request.querystring("FName")
LName = request.querystring("LName")

YEAR1 = Year(Now())

SQL = "SELECT * "
SQL = SQL & "FROM SCORE WHERE SUPID = '"& request.querystring("SUPID") &"' "
SQL = SQL & "AND YEAR1 = "& YEAR1 &" "

objrec.open SQL,objcon,3,3

Dim months, vars

months = Split("JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC",",")
vars = Split("AHTC,AHTE,AHTO,CPH,ATT,TIPS,OBS,REV,MEET,DEV",",")

FOR v=0 TO ubound(vars)
Execute("Dim " & vars(v))
Execute(vars(v) & "=0")
Execute("Dim " & vars(v) & "_count")
Execute(vars(v) & "_count=0")
Execute("Dim YTD" & vars(v))
Execute("YTD" & vars(v) & "=0")
next

For v=0 to ubound(vars)
For m=0 to ubound(months)
Execute(vars(v) & "=" & vars(v) & " + objrec(""" & months(m) & vars(v) & """)")
Execute("IF objrec(""" & months(m) & vars(v) & """) > 0 THEN " & vars(v) & "_count=" & vars(v) & "_count+1")
next
next

FOR v=0 TO ubound(vars)
Execute("YTD" & vars(v) & " = formatDIV(" & vars(v) & "," & vars(v) & "_count,1)")
next

do while not objrec.eof

Link = "<a href='edit.asp?SUPID="& objrec("SUPID") &"&YEAR1="& objrec("YEAR1") &"'

What I want to do is take the arry and add part of it to the Link = "<a href='edit.asp?SUPID="& objrec("SUPID") &"&YEAR1="& objrec("YEAR1") &"' as &Item= whatever, so that when the link is clicked, it carries the exact MONTH & VARIABLE combination that was clicked.

The array was written by a nice person on this forum for me, so I don't quite understand it well enough yet to do this.

Any help would be greatly appreciated.

"If nothing within you stays rigid, outward things will disclose themselves. Moving, be like water. Still, be like a mirror. Respond like an echo." ~ Bruce Lee

DreXor · Jul 6, 2004

objrec is still a recordset and is being pulled thru the array loops.. so you can use it just as you wrote in the sample above...

dont know what pattern you're trying to use on the link, if you could write out more of a full sample of it to work with i would be happy to oblidge... for "month" and "variable"

[thumbsup2]

DreX
aKa - Robert

LongFeiFengWu · Jul 6, 2004

Here is the script down to the first link I'd like to use. Basically, I want to be able to click the link and carry the data for that particular data value over to another page, so that it can be edited using a form on that page. There are so many that I'd like to harness the power of this array to do it instead of writing out 120 IF THEN statements on each page if I can help it.

<%

FName = request.querystring("FName")
LName = request.querystring("LName")

YEAR1 = Year(Now())

SQL = "SELECT * "
SQL = SQL & "FROM SCORE WHERE SUPID = '"& request.querystring("SUPID") &"' "
SQL = SQL & "AND YEAR1 = "& YEAR1 &" "

objrec.open SQL,objcon,3,3

Dim months, vars

months = Split("JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC",",")
vars = Split("AHTC,AHTE,AHTO,CPH,ATT,TIPS,OBS,REV,MEET,DEV",",")

FOR v=0 TO ubound(vars)
Execute("Dim " & vars(v))
Execute(vars(v) & "=0")
Execute("Dim " & vars(v) & "_count")
Execute(vars(v) & "_count=0")
Execute("Dim YTD" & vars(v))
Execute("YTD" & vars(v) & "=0")
next

For v=0 to ubound(vars)
For m=0 to ubound(months)
Execute(vars(v) & "=" & vars(v) & " + objrec(""" & months(m) & vars(v) & """)")
Execute("IF objrec(""" & months(m) & vars(v) & """) > 0 THEN " & vars(v) & "_count=" & vars(v) & "_count+1")
next
next

FOR v=0 TO ubound(vars)
Execute("YTD" & vars(v) & " = formatDIV(" & vars(v) & "," & vars(v) & "_count,1)")
next

do while not objrec.eof

Link = "<a href='edit.asp?SUPID="& objrec("SUPID") &"&YEAR1="& objrec("YEAR1") &"&MONTHS="& months &"&VARS="& vars &"'>"

response.write "<tr>"
response.write "<td width=263 colspan=6 align=center>"& FName &" "& LName &"</td>"
response.write "<td width=266 colspan=8 align=center>eCenter "& Year(Now()) &" Monthly Score Card</td>"
response.write "<td colspan=3 width=95><input type=button value=Print This Page name=B1 style=font-family: Book Antiqua; font-size: 10pt; font-weight: bold; background-color: #990033; color: #FFFFFF; border-style: solid; border-color: #990033></td>"
response.write "</tr>"
response.write "<tr>"
response.write "<td width=165 align=center>item</td>"
response.write "<td width=75 align=center>objective</td>"
response.write "<td width=13 align=center colspan=2 bgcolor=#990033> </td>"
response.write "<td width=28>jan</td>"
response.write "<td width=25>feb</td>"
response.write "<td width=34>mar</td>"
response.write "<td width=30>apr</td>"
response.write "<td width=30>may</td>"
response.write "<td width=28>jun</td>"
response.write "<td width=27>jul</td>"
response.write "<td width=30>aug</td>"
response.write "<td width=29>sep</td>"
response.write "<td width=30>oct</td>"
response.write "<td width=30>nov</td>"
response.write "<td width=29>dec</td>"
response.write "<td width=28>ytd</td>"
response.write "</tr>"
response.write "<tr>"
response.write "<td colspan=17 width=632 bgcolor=#990033>efficiency / productivity / cost</td>"
response.write "</tr>"
response.write "<tr>"
response.write "<td width=165 align=center>aht - calls</td>"
response.write "<td width=75 align=center colspan=2>405</td>"
response.write "<td width=13 align=center bgcolor=#990033 valign=middle> </td>"
response.write "<td width=28 align=center valign=middle>"& LINK & objrec("JANAHTC") &"</td>"

"If nothing within you stays rigid, outward things will disclose themselves. Moving, be like water. Still, be like a mirror. Respond like an echo." ~ Bruce Lee

LongFeiFengWu · Jul 6, 2004

Here is all the script for this page; I apologize for its length. The parts in bold are the objrec("month&var") references which are links and are linked to the line in red. Right now, the querystring only carries SUPID and YEAR1. I'd like it to carry the value and label of each item as well. That's the part I don't understand how to do.:



<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
<style fprolloverstyle>A:hover {color: red; font-weight: bold}
</style>
</head>

<script language="VBScript">

sub B1_onclick()

window.print()

end sub

</script>

<body bgcolor="#000000" link="#C0C0C0" vlink="#C0C0C0">

<form method="POST" action="scoreview.asp" name="theform">

<div align=center>
<center>
<table border="1" cellspacing="0" width="697" bordercolor="#990033">
<%

FName = request.querystring("FName")
LName = request.querystring("LName")

YEAR1 = Year(Now())

SQL = "SELECT * "
SQL = SQL & "FROM SCORE WHERE SUPID = '"& request.querystring("SUPID") &"' "
SQL = SQL & "AND YEAR1 = "& YEAR1 &" "

objrec.open SQL,objcon,3,3

Dim months, vars

months = Split("JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC",",")
vars = Split("AHTC,AHTE,AHTO,CPH,ATT,TIPS,OBS,REV,MEET,DEV",",")

FOR v=0 TO ubound(vars)
Execute("Dim " & vars(v))
Execute("Dim " & vars(v) & "_count")
Execute("Dim YTD" & vars(v))
next

do while not objrec.eof

FOR v=0 TO ubound(vars)
Execute(vars(v) & "=0")
Execute(vars(v) & "_count=0")
Execute("YTD" & vars(v) & "=0")
next

For v=0 to ubound(vars)
For m=0 to ubound(months)
Execute(vars(v) & "=" & vars(v) & " + objrec(""" & months(m) & vars(v) & """)")
Execute("IF objrec(""" & months(m) & vars(v) & """) > 0 THEN " & vars(v) & "_count=" & vars(v) & "_count+1")
next
next

FOR v=0 TO ubound(vars)
Execute("YTD" & vars(v) & " = formatDIV(" & vars(v) & "," & vars(v) & "_count,1)")
next

Link = "<a href='edit.asp?SUPID="& objrec("SUPID") &"&YEAR1="& objrec("YEAR1") &"'>"

response.write "<tr>"
response.write "<td width=263 colspan=6 align=center>"& FName &" "& LName &"</td>"
response.write "<td width=266 colspan=8 align=center>eCenter "& Year(Now()) &" Monthly Score Card</td>"
response.write "<td colspan=3 width=95><input type=button value=Print This Page name=B1 style=font-family: Book Antiqua; font-size: 10pt; font-weight: bold; background-color: #990033; color: #FFFFFF; border-style: solid; border-color: #990033></td>"
response.write "</tr>"
response.write "<tr>"
response.write "<td width=165 align=center>ITEM</td>"
response.write "<td width=75 align=center>OBJECTIVE</td>"
response.write "<td width=13 align=center colspan=2 bgcolor=#990033> </td>"
response.write "<td width=28>JAN</td>"
response.write "<td width=25>FEB</td>"
response.write "<td width=34>MAR</td>"
response.write "<td width=30>APR</td>"
response.write "<td width=30>MAY</td>"
response.write "<td width=28>JUN</td>"
response.write "<td width=27>JUL</td>"
response.write "<td width=30>AUG</td>"
response.write "<td width=29>SEP</td>"
response.write "<td width=30>OCT</td>"
response.write "<td width=30>NOV</td>"
response.write "<td width=29>DEC</td>"
response.write "<td width=28>YTD</td>"
response.write "</tr>"
response.write "<tr>"
response.write "<td colspan=17 width=632 bgcolor=#990033>EFFICIENCY / PRODUCTIVITY / COST</td>"
response.write "</tr>"
response.write "<tr>"
response.write "<td width=165 align=center>AHT - CALLS</td>"
response.write "<td width=75 align=center colspan=2>405</td>"
response.write "<td width=13 align=center bgcolor=#990033 valign=middle> </td>"
response.write "<td width=28 align=center valign=middle>"& LINK & objrec("JANAHTC") &"</td>"
response.write "<td width=25 align=center valign=middle>"& LINK & objrec("FEBAHTC") &"</td>"
response.write "<td width=34 align=center valign=middle>"& LINK & objrec("MARAHTC") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("APRAHTC") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("MAYAHTC") &"</td>"
response.write "<td width=28 align=center valign=middle>"& LINK & objrec("JUNAHTC") &"</td>"
response.write "<td width=27 align=center valign=middle>"& LINK & objrec("JULAHTC") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("AUGAHTC") &"</td>"
response.write "<td width=29 align=center valign=middle>"& LINK & objrec("SEPAHTC") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("OCTAHTC") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("NOVAHTC") &"</td>"
response.write "<td width=29 align=center valign=middle>"& LINK & objrec("DECAHTC") &"</td>"
response.write "<td width=28 align=center valign=middle>"& YTDAHTC &"</td>"
response.write "</tr>"
response.write "<tr>"
response.write "<td width=165 align=center>AHT - EMAILS</td>"
response.write "<td width=75 align=center colspan=2>470</td>"
response.write "<td width=13 align=center bgcolor=#990033 valign=middle> </td>"
response.write "<td width=28 align=center valign=middle>"& LINK & objrec("JANAHTE") &"</td>"
response.write "<td width=25 align=center valign=middle>"& LINK & objrec("FEBAHTE") &"</td>"
response.write "<td width=34 align=center valign=middle>"& LINK & objrec("MARAHTE") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("APRAHTE") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("MAYAHTE") &"</td>"
response.write "<td width=28 align=center valign=middle>"& LINK & objrec("JUNAHTE") &"</td>"
response.write "<td width=27 align=center valign=middle>"& LINK & objrec("JULAHTE") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("AUGAHTE") &"</td>"
response.write "<td width=29 align=center valign=middle>"& LINK & objrec("SEPAHTE") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("OCTAHTE") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("NOVAHTE") &"</td>"
response.write "<td width=29 align=center valign=middle>"& LINK & objrec("DECAHTE") &"</td>"
response.write "<td width=28 align=center valign=middle>"& YTDAHTE &"</td>"
response.write "</tr>"
response.write "<tr>"
response.write "<td width=165 align=center>AHT - ORDERS</td>"
response.write "<td width=75 align=center colspan=2>470</td>"
response.write "<td width=13 align=center bgcolor=#990033 valign=middle> </td>"
response.write "<td width=28 align=center valign=middle>"& LINK & objrec("JANAHTO") &"</td>"
response.write "<td width=25 align=center valign=middle>"& LINK & objrec("FEBAHTO") &"</td>"
response.write "<td width=34 align=center valign=middle>"& LINK & objrec("MARAHTO") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("APRAHTO") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("MAYAHTO") &"</td>"
response.write "<td width=28 align=center valign=middle>"& LINK & objrec("JUNAHTO") &"</td>"
response.write "<td width=27 align=center valign=middle>"& LINK & objrec("JULAHTO") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("AUGAHTO") &"</td>"
response.write "<td width=29 align=center valign=middle>"& LINK & objrec("SEPAHTO") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("OCTAHTO") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("NOVAHTO") &"</td>"
response.write "<td width=29 align=center valign=middle>"& LINK & objrec("DECAHTO") &"</td>"
response.write "<td width=28 align=center valign=middle>"& YTDAHTO &"</td>"
response.write "</tr>"
response.write "<tr>"
response.write "<td width=165 align=center>CPH</td>"
response.write "<td width=75 align=center colspan=2>7.8</td>"
response.write "<td width=13 align=center bgcolor=#990033 valign=middle> </td>"
response.write "<td width=28 align=center valign=middle>"& LINK & objrec("JANCPH") &"</td>"
response.write "<td width=25 align=center valign=middle>"& LINK & objrec("FEBCPH") &"</td>"
response.write "<td width=34 align=center valign=middle>"& LINK & objrec("MARCPH") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("APRCPH") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("MAYCPH") &"</td>"
response.write "<td width=28 align=center valign=middle>"& LINK & objrec("JUNCPH") &"</td>"
response.write "<td width=27 align=center valign=middle>"& LINK & objrec("JULCPH") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("AUGCPH") &"</td>"
response.write "<td width=29 align=center valign=middle>"& LINK & objrec("SEPCPH") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("OCTCPH") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("NOVCPH") &"</td>"
response.write "<td width=29 align=center valign=middle>"& LINK & objrec("DECCPH") &"</td>"
response.write "<td width=28 align=center valign=middle>"& YTDCPH &"</td>"
response.write "</tr>"
response.write "<tr>"
response.write "<td width=165 align=center>TEAM ATTENDANCE</td>"
response.write "<td width=75 align=center colspan=2>94.5%</td>"
response.write "<td width=13 align=center bgcolor=#990033 valign=middle> </td>"
response.write "<td width=28 align=center valign=middle>"& LINK & objrec("JANATT") &"%</td>"
response.write "<td width=25 align=center valign=middle>"& LINK & objrec("FEBATT") &"%</td>"
response.write "<td width=34 align=center valign=middle>"& LINK & objrec("MARATT") &"%</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("APRATT") &"%</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("MAYATT") &"%</td>"
response.write "<td width=28 align=center valign=middle>"& LINK & objrec("JUNATT") &"%</td>"
response.write "<td width=27 align=center valign=middle>"& LINK & objrec("JULATT") &"%</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("AUGATT") &"%</td>"
response.write "<td width=29 align=center valign=middle>"& LINK & objrec("SEPATT") &"%</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("OCTATT") &"%</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("NOVATT") &"%</td>"
response.write "<td width=29 align=center valign=middle>"& LINK & objrec("DECATT") &"%</td>"
response.write "<td width=28 align=center valign=middle>"& YTDATT &"%</td>"
response.write "</tr>"
response.write "<tr>"
response.write "<td width=165 align=center>TIPS</td>"
response.write "<td width=75 align=center colspan=2>80%</td>"
response.write "<td width=13 align=center bgcolor=#990033 valign=middle> </td>"
response.write "<td width=28 align=center valign=middle>"& LINK & objrec("JANTIPS") &"%</td>"
response.write "<td width=25 align=center valign=middle>"& LINK & objrec("FEBTIPS") &"%</td>"
response.write "<td width=34 align=center valign=middle>"& LINK & objrec("MARTIPS") &"%</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("APRTIPS") &"%</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("MAYTIPS") &"%</td>"
response.write "<td width=28 align=center valign=middle>"& LINK & objrec("JUNTIPS") &"%</td>"
response.write "<td width=27 align=center valign=middle>"& LINK & objrec("JULTIPS") &"%</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("AUGTIPS") &"%</td>"
response.write "<td width=29 align=center valign=middle>"& LINK & objrec("SEPTIPS") &"%</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("OCTTIPS") &"%</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("NOVTIPS") &"%</td>"
response.write "<td width=29 align=center valign=middle>"& LINK & objrec("DECTIPS") &"%</td>"
response.write "<td width=28 align=center valign=middle>"& YTDTIPS &"%</td>"
response.write "</tr>"
response.write "<tr>"
response.write "<td colspan=17 width=632 bgcolor=#990033>DEVELOPMENT</td>"
response.write "</tr>"
response.write "<tr>"
response.write "<td width=165 align=center>OBSERVATIONS / 1 ON 1</td>"
response.write "<td width=75 align=center colspan=2>10 TOUCHES PER ASSOCIATE</td>"
response.write "<td width=13 align=center bgcolor=#990033 valign=middle> </td>"
response.write "<td width=28 align=center valign=middle>"& LINK & objrec("JANOBS") &"</td>"
response.write "<td width=25 align=center valign=middle>"& LINK & objrec("FEBOBS") &"</td>"
response.write "<td width=34 align=center valign=middle>"& LINK & objrec("MAROBS") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("APROBS") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("MAYOBS") &"</td>"
response.write "<td width=28 align=center valign=middle>"& LINK & objrec("JUNOBS") &"</td>"
response.write "<td width=27 align=center valign=middle>"& LINK & objrec("JULOBS") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("AUGOBS") &"</td>"
response.write "<td width=29 align=center valign=middle>"& LINK & objrec("SEPOBS") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("OCTOBS") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("NOVOBS") &"</td>"
response.write "<td width=29 align=center valign=middle>"& LINK & objrec("DECOBS") &"</td>"
response.write "<td width=28 align=center valign=middle>"& YTDOBS &"</td>"
response.write "</tr>"
response.write "<tr>"
response.write "<td width=165 align=center>PERFORMANCE REVIEWS</td>"
response.write "<td width=75 align=center colspan=2>1 PER ASSOCIATE</td>"
response.write "<td width=13 align=center bgcolor=#990033 valign=middle> </td>"
response.write "<td width=28 align=center valign=middle>"& LINK & objrec("JANREV") &"</td>"
response.write "<td width=25 align=center valign=middle>"& LINK & objrec("FEBREV") &"</td>"
response.write "<td width=34 align=center valign=middle>"& LINK & objrec("MARREV") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("APRREV") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("MAYREV") &"</td>"
response.write "<td width=28 align=center valign=middle>"& LINK & objrec("JUNREV") &"</td>"
response.write "<td width=27 align=center valign=middle>"& LINK & objrec("JULREV") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("AUGREV") &"</td>"
response.write "<td width=29 align=center valign=middle>"& LINK & objrec("SEPREV") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("OCTREV") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("NOVREV") &"</td>"
response.write "<td width=29 align=center valign=middle>"& LINK & objrec("DECREV") &"</td>"
response.write "<td width=28 align=center valign=middle>"& YTDREV &"</td>"
response.write "</tr>"
response.write "<tr>"
response.write "<td width=165 align=center>TEAM MEETINGS</td>"
response.write "<td width=75 align=center colspan=2>1</td>"
response.write "<td width=13 align=center bgcolor=#990033 valign=middle> </td>"
response.write "<td width=28 align=center valign=middle>"& LINK & objrec("JANMEET") &"</td>"
response.write "<td width=25 align=center valign=middle>"& LINK & objrec("FEBMEET") &"</td>"
response.write "<td width=34 align=center valign=middle>"& LINK & objrec("MARMEET") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("APRMEET") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("MAYMEET") &"</td>"
response.write "<td width=28 align=center valign=middle>"& LINK & objrec("JUNMEET") &"</td>"
response.write "<td width=27 align=center valign=middle>"& LINK & objrec("JULMEET") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("AUGMEET") &"</td>"
response.write "<td width=29 align=center valign=middle>"& LINK & objrec("SEPMEET") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("OCTMEET") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("NOVMEET") &"</td>"
response.write "<td width=29 align=center valign=middle>"& LINK & objrec("DECMEET") &"</td>"
response.write "<td width=28 align=center valign=middle>"& YTDMEET &"</td>"
response.write "</tr>"
response.write "<tr>"
response.write "<td width=165 align=center>DEVELOPMENT POINTS</td>"
response.write "<td width=75 align=center colspan=2>100</td>"
response.write "<td width=13 align=center bgcolor=#990033 valign=middle> </td>"
response.write "<td width=28 align=center valign=middle>"& LINK & objrec("JANDEV") &"</td>"
response.write "<td width=25 align=center valign=middle>"& LINK & objrec("FEBDEV") &"</td>"
response.write "<td width=34 align=center valign=middle>"& LINK & objrec("MARDEV") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("APRDEV") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("MAYDEV") &"</td>"
response.write "<td width=28 align=center valign=middle>"& LINK & objrec("JUNDEV") &"</td>"
response.write "<td width=27 align=center valign=middle>"& LINK & objrec("JULDEV") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("AUGDEV") &"</td>"
response.write "<td width=29 align=center valign=middle>"& LINK & objrec("SEPDEV") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("OCTDEV") &"</td>"
response.write "<td width=30 align=center valign=middle>"& LINK & objrec("NOVDEV") &"</td>"
response.write "<td width=29 align=center valign=middle>"& LINK & objrec("DECDEV") &"</td>"
response.write "<td width=28 align=center valign=middle>"& YTDDEV &"</td>"
response.write "</tr>"

objrec.movenext

loop

%>
</table>
</center>
</div>

</form>

</body>

</html>

"If nothing within you stays rigid, outward things will disclose themselves. Moving, be like water. Still, be like a mirror. Respond like an echo." ~ Bruce Lee

DreXor · Jul 6, 2004

egads, but gets the point across, the same thing that was done before to the code to make it into a self handling set of loops based on the arrays can be re-applied here. shortening the source of your page dramatically, and saving you having to copy /paste /edit over and over again to achieve a small repeatitive task. currently buried a little bit in a project at the moment, should be able to get you a tested output here in a few hours. (6pm cst now)

[thumbsup2]

DreX
aKa - Robert

LongFeiFengWu · Jul 6, 2004

Thank you very much for all of your help!

"If nothing within you stays rigid, outward things will disclose themselves. Moving, be like water. Still, be like a mirror. Respond like an echo." ~ Bruce Lee

DreXor · Jul 6, 2004

you know you can use asp like regular html and you need not have to response.write the ENTIRE page ... just a lil tidbit of info is all, and there's ALOT going on in all those response.writes, and is even confusing me, all that code can be pretty much shoved into less than 10 lines of loop, because you're doing the same thing over and over again.

if anything it would drastically help to see what you're trying to accomplish, is this page available online somewhere? perhaps dupe it to a temporary location until completed?

[thumbsup2]

DreX
aKa - Robert

LongFeiFengWu · Jul 6, 2004

I've placed a shell of this on my personal web hosting server. You can check it out at http://www.dragons-den.us/ecenter/aid/score/scoreview.asp
.

The real one is on an intranet site and you wouldn't be able to see it unless you had access to my company's network.

"If nothing within you stays rigid, outward things will disclose themselves. Moving, be like water. Still, be like a mirror. Respond like an echo." ~ Bruce Lee

DreXor · Jul 6, 2004

thanks, that will help quite a lot, i'll get to working on some code here for you in the next few hours.

[thumbsup2]

DreX
aKa - Robert

LongFeiFengWu · Jul 6, 2004

You must drink a lot of coffee.

"If nothing within you stays rigid, outward things will disclose themselves. Moving, be like water. Still, be like a mirror. Respond like an echo." ~ Bruce Lee

DreXor · Jul 6, 2004

nah.. mountain dew [lol]

bu no i work nights, so i occationally will post in the day, and tend to do everything else night time

will be posting soon... and try to get all of the layout and so forth in the source sample.

[thumbsup2]

DreX
aKa - Robert

DreXor · Jul 6, 2004

you dont happen to have a generated list of group headers/item headers somewhere? or is it static?

referring to as group headers : DEVELOPMENT,EFFICIENCY / PRODUCTIVITY / COST

and referring to as item headers : AHT - CALLS,AHT - EMAILS,AHT - ORDERS,CPH,TEAM ATTENDANCE,TIPS,OBSERVATIONS / 1 ON 1,PERFORMANCE REVIEWS,TEAM MEETINGS,DEVELOPMENT POINTS

[thumbsup2]

DreX
aKa - Robert

LongFeiFengWu · Jul 6, 2004

it's all static. i'm really new to this stuff, so i do it all the hard way. a lot of easy to manage small parts. probably way more work than necessary.

"If nothing within you stays rigid, outward things will disclose themselves. Moving, be like water. Still, be like a mirror. Respond like an echo." ~ Bruce Lee

DreXor · Jul 7, 2004

in the sample page is that all the items you're planning on having ?

[thumbsup2]

DreX
aKa - Robert

Tarwn · Jul 7, 2004

Thats some interesting code. What are the chances you could do a re-design on the database? Is this being filled by another application or does it exist purely for this one? I see several places that imply some of the structure of your table and from what I can see you would gain quite a bit of efficiency and simplicity from a minor rewrite.

Right now it looks like you have columns set up for SUPID, Year1, 120 fields for item vs month, and possibly more that you aren't accesssing from this page. This could easily be split into three tables (Score, Month, Item) or even Two( Score, Item) with Month and VarItem being lookup tables. This would not only make your database smaller (instead of storing a whole lot of empty fields, etc) it will decrease transmision time between your database and web server, allow you to increase the efficiency of your script by quite a bit (right now your executing 160 Executes per record found in the database, we could cut that to 0), and allow you to simplify a good deal of your code for later maintainability (like if you get hit by a bus

). With the changes you also wouldn't have to rewrite your code if they suddenly came up with some magic new Item they wanted added, in fact itshould take all of 30 seconds to add it with a better db design. Th code should just adapt without changes.

Ideally I would think you would want a setup like this:
Score
score_id- autonumber or seeded integer
SUPID - text? dunno what this field stands for
Score - number (dunno what kind, probably integer)
aDate - short date
item_id - integer (long)

Item
item_id - autonumber or seeded integer
item_short - text field or varcar, 4 character length
item_long - text field or varchar, 25 character length
objective - text value to cover all types

If there are necesary fields that I haven't included I could add them fairly easily for you, just mention it.

Now to start a display page we could use SUM functions in our SQL statement, group by the month and we could use COUNT to get the counts. This takes some of the burden off our script and reduces the amount of memory we will need for the ASP script. We would also want to order it by the Date field and the Item field to get groups of records with the same item in order by month (ORDER BY item_id, aDate).

So lets throw together some sample code real quick:

Code:

<%
Option Explicit
%>
<!--#Include file="../Includes/openDBconnections1.asp" -->
<!--#Include file="../Includes/format.asp" -->

<!-- All the top html -->
<%

FName = request.querystring("FName")
LName = request.querystring("LName")

YEAR1 = Year(Now())

SQL = "SELECT Score.SUPID, SUM(Score.Score) as ScoreTotal, SUM(Score.Score) as ScoreCount, Month(Score.aDate) as aMonth, Item.item_id, Item.item_short, Item.item_long, Item.objective "
SQL = SQL & "FROM (Score INNER JOIN Item ON Score.item_id = Item.item_id) "
SQL = SQL & "WHERE SUPID = '"& request.querystring("SUPID") &"' "
SQL = SQL & "AND YEAR(aDate) = "& YEAR1 & " "
SQL = SQL & "GROUP BY Score.SUPID, Month(Score.aDate) as aMonth, Item.item_id, Item.item_short, Item.item_long, Item.objective "
SQL = SQL & "ORDER BY Item, Month(aDate)"

objrec.open SQL,objcon,3,3

Dim aMonth, anItem, yearTotal
Dim t_ctr

'--- Output stuff to start the table
Response.Write "<table><tr><th colspan=""5"">Representative Name Here</th>...etc </tr>"
Response.Write "<tr><td>Item</td><td>Objective</td><td>Pretty Color Cell</td>"
For t_ctr = 1 to 12
   Response.Write "<td>" & Left(MonthName(t_ctr),3) & "</td>"
Next
Response.Write "</tr>"

'--- Loop to output data
If Not objrec.EOF Then objRec.MoveFirst
Do Until objRec.EOF
   'if first row or item dosn't match item from last loop, start a new row
   If anItem <> objRec("item_id") Then
      'if there is something in anItem, end the previous row by looping through remaining months
	  If len(anItem) > 0 Then
         For t_ctr = aMonth to 12
            Response.Write "<td>&nbsp;</td>"
		 Next
		 
		 'output the yearly total from the yearTotal variable
		 Response.Write "<td>" & yearTotal & "</td>"

		 'end the row
		 Response.Write "</tr>"
      End If

	  'start a row for the new item
	  Response.Write "<tr><td>" & objRec("item_long") & "</td><td>" & objRec("objective") & "</td><td>pretty column here</td>"
	
      'reset the month counter
	  aMonth = 1
   End If

   'if the month for the current record matches our month counter, display it and add to total
   If objRec("aMonth") = aMonth Then
      Response.Write "<td>" & objRec("ScoreTotal") & "</td>"
      yearTotal = yearTotal + objRec("ScoreTotal") & "</td>"

      'increment the recordset
      objRec.MoveNext
   Else
      'otherwise output an empty square
	  Response.Write "<td>&nbsp;</td>"
   End If

   'increment month
   aMonth = aMonth + 1
Loop

'--- output more pretty html to end the table and page
Response.Write "</table></body></html>"
%>

One thing this doesn't cover is the percentages for a couple of your fields. My advice on that would be to add a field to the Item table called ItemType and have a way to define sum vs percentage. The in your code add a second year variable called yearTotalCount. Set it to 0 where yearTotal is set to 0, then replace the current record output if statement with three cases instead of just two. The first case is were the months are equivalent and the type is sum, the second is months equivalent and percentage, the third is the blank cell. Unfortunatly I can't show an example for this because I don't know how your obtaining your percentage. The only other change would be tat where your outputting the yearTotal you would first check if yearTotalCount > 0, if so assume you want a percentage and calculate the years percentage, otherwise output just the yearTotal.

There are a couple fields in my select statement that aren't being used and I also did not output links. The links can be built pretty much the same by including the item_id and aMonth values. You could then output all inputs for that item and month combination as well as a blank entry so that they could edit existing entries forthat month or add another. It ends up a little more complicated but you also have finer control over the data that way.

The only other factor mising here is the pretty sction headings. My advice on that matter is to create a section table with section_id and section_text. Add the section id to the Item table, alter the SQl statement to inner join ith the section table on section_id's, add it as the first field in the ORDER BY, then in the item change section of the main loop after you end a row see if the section has changed (keep it in a variable like anItem), if it has changed output a pretty row with the section name.

I know this seems like a lot of work on something you have almost completed, but if nothing else relies on your current database design I would at least consider it. The efficiency, maintainability, and useability will all increase dramatically with this redesign. If you have any questions, feel free to ask, and even if this dosn't help during your current proect hopefully it will help in future projects,

-T

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
The never-completed website:

http://www.tiernok.com/

DreXor · Jul 9, 2004

did you ever get this resolved?

[thumbsup2]

DreX
aKa - Robert

LongFeiFengWu · Jul 9, 2004

sure did. the page is all done. thanks for all of your help!

"If nothing within you stays rigid, outward things will disclose themselves. Moving, be like water. Still, be like a mirror. Respond like an echo." ~ Bruce Lee

DreXor · Jul 9, 2004

good to hear

DreX
aKa - Robert

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Generate Querystring Item From Array

Technical User

Programmer

Technical User

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Programmer

Technical User

Programmer

Programmer

Programmer

Technical User

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor