How To Sort and Report from an Array ?

[Les Juby]

I need to extract records from a database subject to conditions and
only thereafter give the users the choice of which fields to sort the
results on.

In this situation I can't write back to a temporary file and then
select all from that file on the different sort orderings.

I figured that the only way out would be to write to an array from the
first recordset set creation. Then write the report to the screen
from the array and users can choose different sort orders and it would
extract again and report from that same array.

( 1 )
Only problem is that I havn't the faintest idea of how to create and
write to the array. Or how to query it. Is it similar to the first
database report.?

Would anyone perhaps know of a simple tutorial resource that
demonstrates this method.?

( 2 )
Or do I need to think right outside the box and take the first report
in a more efficient way. Problem is that I need to take only one
record whenever the value changes in a particular field. Could my SQL
statement use ordering on one field to take the recordset but order on
another field for the output. I wouldn't have tought so.

thanks

.les.

[Paxton]

Re: How To Sort and Report from an Array ?


Les Juby wrote:[color=blue]
> I need to extract records from a database subject to conditions and
> only thereafter give the users the choice of which fields to sort the
> results on.
>
> In this situation I can't write back to a temporary file and then
> select all from that file on the different sort orderings.
>
> I figured that the only way out would be to write to an array from the
> first recordset set creation. Then write the report to the screen
> from the array and users can choose different sort orders and it would
> extract again and report from that same array.
>
> ( 1 )
> Only problem is that I havn't the faintest idea of how to create and
> write to the array. Or how to query it. Is it similar to the first
> database report.?
>
> Would anyone perhaps know of a simple tutorial resource that
> demonstrates this method.?
>
> ( 2 )
> Or do I need to think right outside the box and take the first report
> in a more efficient way. Problem is that I need to take only one
> record whenever the value changes in a particular field. Could my SQL
> statement use ordering on one field to take the recordset but order on
> another field for the output. I wouldn't have tought so.
>
> thanks
>
> .les.[/color]


A quick google threw up this result:

http://www.4guysfromrolla.com/webtech/072199-1.shtml

which may give you some ideas about how to accomplish the task.
Basically you can make the column heads hyperlinks passing the 'Order
By' variable in the querystring, then dynamically create your SQL query
using the variable passed in the querystring.

/P.

[Dave Anderson]

Re: How To Sort and Report from an Array ?

Les Juby wrote:[color=blue]
> I need to extract records from a database subject to conditions and
> only thereafter give the users the choice of which fields to sort the
> results on.
>
> In this situation I can't write back to a temporary file and then
> select all from that file on the different sort orderings.[/color]

We do this commonly. A generic example, with ADODB.Recordset RS:

for (var Rows=[]; !RS.EOF; RS.MoveNext()) {
for (var Cells=[],i=0; i<RS.Fields.Cou nt; i++)
Cells.push(RS.F ields(i).Value)
Rows.push(Cells )
}
function ResultSort(x,y) {
var C = Request.Form("S ortBy").Item || 0,
O = Request.Form("O rder").Item == "Desc" ? -1 : 1
return x[C]>y[C] ? O : x[C]
}
Rows.sort(Resul tSort)

In the above example, the default behavior is to sort in ascending order on
column 0, but deferring to a passed sort column "SortBy" and ordering
"Order".




--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

[Dave Anderson]

Re: How To Sort and Report from an Array ?

Dave Anderson wrote:[color=blue]
> function ResultSort(x,y) {
> var C = Request.Form("S ortBy").Item || 0,
> O = Request.Form("O rder").Item == "Desc" ? -1 : 1
> return x[C]>y[C] ? O : x[C]
> }
> Rows.sort(Resul tSort)[/color]

I hate when OE tries to think for me. The return line should read:

return x[C]>y[C] ? O : x[C]<y[C] ? -O : 0


--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

[Les Juby]

Re: How To Sort and Report from an Array ?

Les Juby wrote:
[color=blue][color=green]
>> I need to extract records from a database subject to conditions and
>> only thereafter give the users the choice of which fields to sort the
>> results on.
>>
>> In this situation I can't write back to a temporary file and then
>> select all from that file on the different sort orderings.
>>
>> I figured that the only way out would be to write to an array from the
>> first recordset set creation. Then write the report to the screen
>> from the array and users can choose different sort orders and it would
>> extract again and report from that same array.
>>
>> ( 1 )
>> Only problem is that I havn't the faintest idea of how to create and
>> write to the array. Or how to query it. Is it similar to the first
>> database report.?
>>
>> Would anyone perhaps know of a simple tutorial resource that
>> demonstrates this method.?
>>
>> ( 2 )
>> Or do I need to think right outside the box and take the first report
>> in a more efficient way. Problem is that I need to take only one
>> record whenever the value changes in a particular field. Could my SQL
>> statement use ordering on one field to take the recordset but order on
>> another field for the output. I wouldn't have tought so.
>>
>> thanks
>>
>> .les.[/color][/color]

On 15 Feb 2006 13:16:28 -0800, "Paxton" <paxtonend@hotm ail.com> wrote:
[color=blue]
>
>
>A quick google threw up this result:
>http://www.4guysfromrolla.com/webtech/072199-1.shtml
>
>which may give you some ideas about how to accomplish the task.
>Basically you can make the column heads hyperlinks passing the 'Order
>By' variable in the querystring, then dynamically create your SQL query
>using the variable passed in the querystring.
>
>/P.[/color]

Thanks for that. It is pretty much what I would normally do, but I
think maybe I havn't explained the problem properly here.

My initial recordset is maybe only 10% of the database, and a whole
lot of complex ordering and filtering has happened to get the
recordset for display.

The user now needs to be able to view that recordset in different
orderings, and usually (as you've suggested) I'd be able to just
generate the search again with querystrings passed from hyperlinks in
the column titles. But I can't do that as I'd have to filter from the
database first on special orderings to get the primary recordset.
Catch 22.

The other method is to do the first filter and feed the results into a
temporary table. Then use that table for taking the different sorts
from. Problem here is that a second user could put results in there
while the first user is still looking at his different sorts. And
that's quite likely with this many users. To put a random ID on
records with a session variable to keep to your results only is the
worst case solution. This table could then get huge, would need
maintenance, and could corrupt. How do you clear it out without
wiping out someone else's results-in-progress.?

That's why I thought an array would be the solution. It's private (in
memory) for each user, fast, and disappears when they do.? Wouldn't
that work.? But can you use it like a normal table.?

.les.

[Les Juby]

Re: How To Sort and Report from an Array ?

On Wed, 15 Feb 2006 16:18:43 -0600, "Dave Anderson"
<GTSPXOESSGOQ@s pammotel.com> wrote:
[color=blue]
>Les Juby wrote:[color=green]
>> I need to extract records from a database subject to conditions and
>> only thereafter give the users the choice of which fields to sort the
>> results on.
>>
>> In this situation I can't write back to a temporary file and then
>> select all from that file on the different sort orderings.[/color]
>
>We do this commonly. A generic example, with ADODB.Recordset RS:
>
> for (var Rows=[]; !RS.EOF; RS.MoveNext()) {
> for (var Cells=[],i=0; i<RS.Fields.Cou nt; i++)
> Cells.push(RS.F ields(i).Value)
> Rows.push(Cells )
> }
> function ResultSort(x,y) {
> var C = Request.Form("S ortBy").Item || 0,
> O = Request.Form("O rder").Item == "Desc" ? -1 : 1
> return x[C]>y[C] ? O : x[C]
> }
> Rows.sort(Resul tSort)
>
>In the above example, the default behavior is to sort in ascending order on
>column 0, but deferring to a passed sort column "SortBy" and ordering
>"Order".[/color]

Sorry Dave, I'm just a simple Classic ASP child ...... I think you're
presuming here that I have created an array and am now happily
reporting from it. If so, not so.!

Is that the presumption .... and if so how would I get the first
results into the array to get out the second level reports...?

This is why I was hoping there might be a simple (but detailed)
explanation somewhere....

thanks for the help

.les.

[Chris Hohmann]

Re: How To Sort and Report from an Array ?


"Les Juby" <lesjuby@anti-spam.iafrica.co m> wrote in message
news:43f38e9a.1 3537389@news.te lkomsa.net...[color=blue]
>I need to extract records from a database subject to conditions and
> only thereafter give the users the choice of which fields to sort the
> results on.
>
> In this situation I can't write back to a temporary file and then
> select all from that file on the different sort orderings.
>
> I figured that the only way out would be to write to an array from the
> first recordset set creation. Then write the report to the screen
> from the array and users can choose different sort orders and it would
> extract again and report from that same array.
>
> ( 1 )
> Only problem is that I havn't the faintest idea of how to create and
> write to the array. Or how to query it. Is it similar to the first
> database report.?
>
> Would anyone perhaps know of a simple tutorial resource that
> demonstrates this method.?
>
> ( 2 )
> Or do I need to think right outside the box and take the first report
> in a more efficient way. Problem is that I need to take only one
> record whenever the value changes in a particular field. Could my SQL
> statement use ordering on one field to take the recordset but order on
> another field for the output. I wouldn't have tought so.
>
> thanks
>
> .les.
>[/color]

Use the Sort property of the Recordset object:

http://msdn.microsoft.com/library/en-us/ado270/htm/mdprosortpropertyado.asp

You can continue to sort/filter the data as you are currently doing, then
once you've retrieved the filtered data into a Recordset object, set the
Sort property for that object based on the sort column indicated by the
user.