Select 5 records at random

[LeGauche]

Can someone please show either the Action Script or XBasic for the following idea:

Select ID# and Name fields for 5 different records at random from MyTable.

In this case, the underlying table is Access 2003.

Thanks.

[Steve Wood]

Re: Select 5 records at random

This may be more than you are looking for, but this is code from the home page of an A5 website. The important part is defining the search string and inserting it in the table.external_...() function. The rest of the code makes each text snippet a hyperlink and bullet text.

For reference the code is used on this page www.alphadevnet.com where it says "Testimonials".

<div class="adbox" style="MARGIN-TOP: 10px;">
<h2>Client Testimonials</h2>
<p>
<ul>
<%a5
dim dta1 as c=""
dta1 = "'<li><a href=\"dev_profile.a5w?devid='+devid+'\">'+left(Comment,90)+'...</a>'"
srch = "random_records(5)"
?table.external_record_content_get("[PathAlias.ADB_Path]\dev_testimonials",dta1,"",srch)
%>
</ul>
</p>
</div>

[LeGauche]

Re: Select 5 records at random

Thanks, Steve, but I really need the XBasic or Action Script. I'm trying to code the OnPush event for a button on a form. Perhaps I'm more confused than I realized; I thought that whether or not an app is online or desktop, the underlying code is XBasic rather than HTML. Is that correct?

[Steve Wood]

Re: Select 5 records at random

What I showed you is xbasic. The HTML just gives the output special formatting in the browser.

If this is Desktop, you should post in Desktop area. The xbasic might be the same, but how you go about implementing it would be different.

[Peter.Wayne]

Re: Select 5 records at random

Warren,
One of the issues is that the underlying table is in Access. So you have to get to it with AlphaDAO or else create an active link table to the Access table. The active link is probably the easiest way to go.
Assume you have created the link as
al_client

Then to select a crlf()-delimited list of 5 random records with the information you want, you could write

clientList=table.external_record_content_get("al_client",\
"ID+':'+Name","name","random_records(5)")

If it's a large table, though, this will be slow, because the entire table will have to be sent to the client for random_records() to work. There's no query optimization that can be run at the server end (by definition! -- you're asking for random records, and SQL doesn't support that!)
There's a couple of solutions. I think they all depend on the assumption that the ID field has been sequentially assigned; I don't otherwise see how one can pull out a record from a table that doesn't use record numbers.

t=table.open("al_client")
nrecs=t.records_get()   ' total records - a count(*) in SQL
i=0
dim clientList as c=""
while i<5
  testID=ceiling(rand()*nrecs)
  testClient=t.record_content_get("al_client","ID+':'+Name",\
"","ID="+testID)
  if testClient>"" then
    clientList=clientList+testClient+crlf()
    i=i+1
  end if
end while
t.close()
ui_msg_box("Client List",clientList)

And if that is still too slow, then you would need to use SQL queries -- but I don't know whether Access understands SQL. Does it?

[411tech]

Re: Select 5 records at random

I don't have the xbasic because I wrote a program that did this in Foxpro. I needed to write an application that picked employees for a random drug test.

I remember that I used the random number generator to generate a certain # of random #'s (specified by the user) based on the total amount of records in our employee file.

I then created an array with the #'s generated and I then compared them to each other to make sure none of the #'s were the same.

Once I was sure I had distinct #'s, I then opened the table, went to the top, and then I went to the record associated with that #.

This worked for us. Hope this helps.

[EricN]

Re: Select 5 records at random

I'm attempting to use the following script to obtain random results but so far it places the results in the same order. Created a grid that uses rand() and this works great but when I place it in a page, my html in the rest of the page is somehow adversely affected. I prefer the following method anyway for page load speed.

dim tbl as P
dim qry as P
tbl = table.open("[pathalias.adb_path]\Advertisements")
query.filter = "random_records(10, \"status='Active'.and.state<>''\")"
query.order = "postnum"
query.options = ""
qry = tbl.query_create()
tbl.fetch_first()
while .not. tbl.fetch_eof()
url = tbl.Link
display = tbl.Title
? "<a href=\"" + url + "\" target=\"_blank\"><strong><font color=#0066CC face=Arial size=2><center>" + display + "</center></strong></font></a>"

? "<font size=2 color=#339966>"+tbl.Content+"</font><BR><BR>"
tbl.fetch_next()
end while
end if
qry.drop()
tbl.close()

[Stan Mathews]

Re: Select 5 records at random

I'm attempting to use the following script to obtain random results but so far it places the results in the same order.

Can you try that explanation again?

query.order = "postnum"


sets the order of the results. I don't see that changed anywhere so you get the same order.

[EricN]

Re: Select 5 records at random

Hello Stan,

I've tried query.order = "" with the same results. Maybe not. Are you saying I should have something else there?

This is from the help:
query.filter = "random_records(10, \"orderdate > {5/1/2000}\")"
query.order = "order_total"

This is what I'm attempting to accomplish. Sponsored Ads to be displayed in random order each time page is refreshed. (far left of page)
http://socialservicenetwork.com/JobsBrowse.a5w?

[Steve Wood]

Re: Select 5 records at random

Here is another way to create such a list. I left in the HTML so you can see how to include that for your output. This lists the leftmost 90 chars of the Comments field in a bulleted list.


dim dta1 as c=""
?"<ul>"
dta1 = "'<li><a href=\"dev_profile.a5w?devid='+devid+'\">'+left(Comment,90)+'...</a>'"
srch = "random_records(5)"
?table.external_record_content_get("[PathAlias.ADB_Path]\dev_testimonials",dta1,"",srch)
?"</ul>"

[Stan Mathews]

Re: Select 5 records at random

Eric,

Steve likely has your answer. As for the question about the query.order, I think you must understand what "random_records(10, \"orderdate > {5/1/2000}\")"
does.

Not that I know exactly but I imagine whatever algorithm is behind it has some means of selecting 5 record numbers for records where the orderdate > {2/1/2000} out of the available records. Those five records are likely reurned in record number order to the script where they can be further ordered by query.order.

IOW I don't imagine the random_records() function jumps around the table selecting records at random. Even if it did, query.order would rearrange them.

[EricN]

Re: Select 5 records at random

Thank you Stan and Steve! Here is what I did...

uids = table.external_record_content_get("[PathAlias.ADB_Path]\Advertisements","postnum","","random_records(6)")
For each y in uids
dim vAdLink as c = lookup("[pathalias.adb_path]\advertisements","Postnum=" + quote(y),"Link")
dim vTitle as c = lookup("[pathalias.adb_path]\advertisements","Postnum=" + quote(y),"Title")
dim vContent as c = lookup("[pathalias.adb_path]\advertisements","Postnum=" + quote(y),"Content")

? "<a href=\"" + vAdLink + "\" target=\"_blank\"><strong><font color=#0066CC face=Arial size=2><center>" + vTitle + "</center></strong></font></a>"
? "<font size=2 color=#339966>"+vContent+"</font><BR><BR>"
Next

I use for/next quite a bit but couldn't figure how to randomize the array. This displayed results are exactly what I desire but wondering if this is the most processor efficient method.

[Steve Wood]

Re: Select 5 records at random

Eric, work on the table.external_record_content_get function. You can get your entire list including HTML and multiple fields out of that one function. Right now your code requres 19 lookups to get the result. If you are going to use the lookups, use lookupC() instead which uses an index to find. Finally, if the values in any of your fields are greater than 1024, lookup() will truncate. table.external_record_content_get() does not truncate and can bring back an unlimited amount.

[EricN]

Re: Select 5 records at random

You can get your entire list including HTML and multiple fields out of that one function.

Hi Steve,
Can you give an example?? Help only shows how you can obtain contents from one field... unless I'm missing something.

For now I not using dim and am using lookupc()
vAdLink = alltrim(lookupc("F",alltrim(y),"alltrim(link)","[pathalias.adb_path]\advertisements","postnum"))

Update:
Went back to this which works the way I want it to. I think I now understand this better Stan. :)

dim tbl as P
dim qry as P
tbl = table.open("[pathalias.adb_path]\advertisements")
query.filter = "random_records(6)"
query.order = ""
query.options = ""
qry = tbl.query_create()
tbl.fetch_first()
while .not. tbl.fetch_eof()
? "<a href=\"" + tbl.Link + "\" target=\"_blank\"><strong><font color=#0066CC face=Arial size=2><center>" + tbl.Title + "</center></strong></font></a>"
? "<font size=2 color=#339966>"+tbl.Content+"</font><BR><BR>"
tbl.fetch_next()
end while
qry.drop()
tbl.close()

[Steve Wood]

Re: Select 5 records at random

Here is one with a little HTML mixed in. It creates a Title and then a short_description under it in smaller font.

dta = "'<div class=\"adbox\" style=\"margin-left:5px\"><h3 style=\"margin-top:0;margin-bottom:2px\">'+title+'</h3><small>'+short_desc+'</small></div>'"
?table.external_record_content_get("[PathAlias.ADB_Path]\event_item",dta,"sortlevel","category=\"web\"")

The above one is self-contained, here is a much more complex one that does require a FOR NEXT loop.

'TESTIMONIALS
dim dta as c
dta = "id+'|'+devid+'|'+Name+'|'+Company+'|'+stritran(Comment,crlf(),\"~\")+'|'+Date_Added"
tmp = table.external_record_content_get("[PathAlias.ADB_Path]\dev_testimonials",dta,"","devid="+quote(devid))

if tmp <> ""
	lst = lst + "<div class=\"tabbertab\">"
	lst = lst + "<br>"
	lst = lst + "<h2>Testimonials</h2>"
	dim tlst as c = ""
	for each foo in tmp
		tlst = tlst + "<p>"
		tlst = tlst + "<strong>Name:</strong> " + word(foo,3,"|") + "<br>"
		tlst = tlst + "<strong>Company:</strong> " + word(foo,4,"|") + "<br>"
'		tlst = tlst + "<strong>Dated:</strong> " + word(foo,6,"|") + "<br>"
		tlst = tlst + "<strong>Comment:</strong> " + stritran(word(foo,5,"|"),"~","<br>")
		tlst = tlst + "</p>"
		tlst = tlst + "<hr>" + crlf()
	next
	lst = lst + tlst
	lst = lst + "</div><!-- TABBER -->"
end if

This produces a list like the one under the Testimonial tab on this page: http://alphadevnet.com/dev_profile.a...94e0277e3a4e7a

(Glad I did this, I see I have bogus content at that address on a live website.)