Printed Icetips Article

Allowing your user to tag some records in a list box for further processing (like printing on a report) is a good technique for selective processing.

Basically, there are three ways to let the user tag records in a browse.

1. There is an icon that looks like an empty check box on each row. If the user clicks the mouse on the check box, the icon is replaced with one that looks like a checked box. Checking it again turns it of. This works well with small lists, no more than about 20 or so. More than that, and the user will have navigation problems double checking his work.

2. You allow the user to select multiple rows that remain highlighted. I don't recommend this, because programming it the way Windows users are used to is difficult, and once you've done that, it's too easy for the user to have a mouse slip and undo all his selections without meaning to.

3. You have two browses on the window. On the left browse are unselected records. On the right are the records that have been selected. When the user double-clicks on the left box, the selected record is added to the queue for the right list, and removed from the queue for the left list. And double-clicking on the right box works the opposite way. Even for large lists, this works very well, as the user can see at a glance what has been selected.

But first, let's think about the way tagging is often done with TPS files.

One of the most common way of tagging records is store some information about the selected records in a "tag file" which is dedicated to that purpose alone. Then when, say, the user prints his report, the main file is read, record by record, and for each record, the tag file is examined to see if there's a matching entry. If there is a matching entry, then the record is printed. If there is no matching record, the record is ignored--not printed--and the processing continues with the next record.

This works fine for TPS files. How well does it work with SQL? It's about the worst technique you can use.

If you can't immediately see why I said it's such a bad choice, then reread two paragraphs back the description of the process. It's not the tagging itself that is bad. With user interaction going on, the time taken to add to or delete a record from the tag file might not be noticed at all. But think about what happens when the user selects one record from 10,000 possibilities, and then decides to print that one. Now your code will read the ENTIRE primary table, and for each record retrieved, it will send another separate query to see if the record is in the tag file. So you will send 10,000 queries to the server to print one record. It's not hard to imagine the user's frustration in taking 2 hours to print one line on a one page report! On the other hand, the user may never see the report. He might conclude it's Ctrl-Alt-Del time, and report to you that "the computer just locked up!"

The way to do the tagging is to store the information at the workstation. Don't use a tag file at all. You'll have the tagged records information ready to construct a query that will be limited to just the records selected. Like this (assume MyTagString is a very large CSTRING--if you are using Clarion 5.5, use an iDynStr object to make this easier):

mytagstring = ''
loop i=1 to records(BrowseQueue)
  get(BrowseQueue, i)
  if BrowseQueue.TagField = 1  
    ! whatever your condition is...
    if i > 1
      mytagstring = mytagstring & ', '
    end
    mytagstring = mystagstring & BrowseQueue.CustomerID
  end
end

Now you have a comma delimited list of CustomerIDs that you can use in your SQL. You can use it in the WHERE clause with the IN condtion, like this:

'select customername from customer' &|
' where customerid in (' & mytagstring & ')'

Now you will retrieve the customers the user tagged, and ONLY the customers the user taged, you will do it in one query, and you won't have to query any tag table--in fact, you won't even need a tag table.

Of course, this techniqueue requires that you be able to handle lists to do the tagging. It's not my purpose here to go into all that, but it is well worth the time for every Clarion programmer to learn to handle queues and list controls like this. Learn about ALERT, CHOICE(), and all the list properties pertaining to mouse clicks. And learn how to do it all with pure source code.

Be aware that you are under some size constraints in constucting a list of values for the IN condition of the WHERE clause. For example, the maximum size of a MySQL statement is 8k bytes. You'll have to plan accordingly.

Better SQL - a tip from Scott Ferrett
31-July-2002 -- Dan Pressnell

Scott sent me a tip that I should have known about. Actually, I did know about it, but for some reason I thought it didn't apply to using prop:sql.

Use BUFFER.

Today I did this on an MSSQL browse:

    open(sqlfile)
    buffer(sqlfile,200)
    ! etc...

What took 5.5 seconds to load 6,000 records now took only 0.8 seconds.

Experimenting with much larger buffer sizes degraded performance.

Also, this might not work for all SQL systems.

Thanks, Scott, for pointing this out to me!

Better SQL - addendum from Gus M. Creces
02-August-2002 -- Arnor Baldvinsson

Dan et all...

Re: Buffer....

If you do this in an ABC browse you should set the correct ABC properties rather than issuing the Buffer statement yourself, since the ABC's are managing BUFFER parameters with properties. ViewManager.Open does this as you can see from the ABC code below.

BUFFER(SELF.View,SELF.PageSize,|
       SELF.PagesBehind,SELF.PagesAhead,SELF.TimeOut)

FWIW (the pitch )

For the last number of years our browse templates have provided a "Smart Buffering" button that lets you manage view size, forward and behind paging from our template.