View · Index
No registered users in community rubick
in last 10 minutes

Using List Builder

Using List Builder

List Builder (LB) was introduced in acs-templating 4.6.4, and it allows you to make tables very easily. It took me about two hours to convert a table of mine to LB, and the time is well worth it, because you don't have to manually put in all the code for sorting. It also seems very flexible.

Plus, since you are benefiting from my learning process, you probably won't take two hours to learn it!


The best example I've found is at the logger admin page. It uses list-builder well, and does nifty things with filters, etc..

The basic idea

Walk with me as I convert my index.tcl and index.adp file to list-builder. This is the end of the .tcl file:
# An HTML block for the breadcrumb trail                                         
set context_bar [ad_context_bar]
set title "Organizations"

db_multirow orgs orgs_query { }
The index.adp file looks something like this:

<property name="title">@title@</property>                            
<property name="context_bar">@context_bar@</property>                

<if @orgs:rowcount@ gt 0>                                                  
        <table cellpadding="3" cellspacing="0">                            


<multiple name="orgs">                                                     


        <a href="one?organization_id=@orgs.organization_id@">Details</a>                                                                             
        <if @write_p@>                                                     
          <a href="add-edit?organization_id=@orgs.organization_id@">Edit</a>                                                                         
        <if @delete_p@>                                                    
          <a href="delete?organization_id=@orgs.organization_id@">Delete</a>                                                                         



<if @orgs:rowcount@ gt 0>                                                  

<if @create_p@>                                                            
<li><a href="add-edit">Add an organization</a></p>       
You would then change the .tcl file to look like this:
template::list::create \
    -name orgs \
    -multirow orgs \
    -key organization_id \
    -elements {
        organization_id {
        name {
        notes {
    } \
    -main_class {

db_multirow -extend { item_url } orgs orgs_query {
} {
    set item_url [export_vars -base "one" {organization_id}]
Then you change the .adp file to look like this:

<property name="title">@title@</property>                            
<property name="context_bar">@context_bar@</property>       

<listtemplate name="orgs"></listtemplate>

Common errors

If you get an error like this:
Too many positional parameters specified
     while executing
     (procedure "template::list::create" line 1)
     invoked from within
"template::list::create \ "
     ("uplevel" body line 86)
     invoked from within
Then the problem is this:
This error means one of your arguments is being interpreted not as a named switch (-elements ...) but as a positional argument (no switch).

This could easiliy be a line that doesn't have the \ at the end, or that has a space character after the \, or some other little typo like that.

Thanks, Lars!

Where the data comes from

From Alex Vorobiev:
it is not clear from the docs, and may not be obvious, but the
-multirow switch lets developer point to a db_multirow statement.
in other words, one MUST create a db_multirow statement with a name
that matches -multrow switch of the list-builder.  the db_multirow
statement, in turn, points to an sql query in the corresponding
.xql file
-to add url columns, use extend with export_vars
-to get row number, use rownum; also see rowcount (in docs for the "multiple" template tag)

Adding in sorting

See adding in sorting From Alex Vorobiev:

for the default case, one must specify column name FOLLOWED by comma and sort order such as ASC or DESC

Several lines from the database in one list-builder cell?

OpenACS has a very cool <group> tag, which allows you to show several rows from the database in one row. For example, if you have the table foo, with columns x and y, with values of
x  y
1  3
1  4
2  5
You may want the output to look like:
1   3 4
2   5
or close to that, using an HTML table. Well, the <group> tag does that. Can we do that with list-builder?

Well, quite easily, it turns out.

Just put something like this in the definition of your column (preferably the last one):

        last_name {
            label "Who"
            display_template {
                <group column="item_id">@tasks.first_names@ @tasks.last_name@<br></group>

Unfortunately, this will mess up the alternating bands of light and dark, because the odd and even rows will not be valid.

Formatting your table

If you want your table to be more compact, put this in:
    -main_class {
    } \

Adding in action buttons on top

Add as part of your definition:
    -actions {
        "Use process" "process-use" "Use a process"
        "Task calendar" "my_url" "View task calendar"
    } \
If you put variables or functions in there, you'll notice they don't get substituted. So instead, you do this:
    set actions [list "Use process" "process-use" "Use a process" "Task calendar" "my_url" "View task calendar"]

    -actions $actions \
Thanks, Dave.


See pagination for list-builder

From Alex Vorobiev:
-multirow vs -page_query it is not immediately clear from the docs, and may not be obvious, but when using pagination, both switches are necessary. the purpose of the -page_query is to fetch the ENTIRE result set, whereas the sql statement used by multirow would fetch only the current page set.
caching with current paginator (not jon griffin's new paginator) use "-page_flush_p 1" to prevent paginator from caching the page set and confusing the hell out of you (when newly added records are suddenly not appearing on the page)

Returning CSV files

If you'd like to return a comma-separated-value file, follow Lars' instructions on returning a CSV from list-builder. These CSV files are opened by Excel or other spreadsheet programs, and make an easy way of getting information out of the database for office workers.

Getting list-builder to work on OpenACS 4.6.3

From Alex Vorobiev:
- copy acs-templating/tcl-list-procs.tcl and
acs-templating/resources/lists from CVS/HEAD to the appropriate
locations in 4.6.3 distribution
- 4.6.3 does not support the new "noquote" tag; any such tags can
be safely removed from templates in versions of openacs prior to 5.0
Caroline Meeks adds:
- you have to use list-procs.tcl version 1.3.  
- Restart the server, don't just watch the files.

Displaying hierarchies

Alex Vorobiev:
[ any information below that mentions pagination refers to old "broken"
paginator ]
- basically not possible with the current implementation of list-builder, 
- poor man's hierarchies: one can build db_multirow as a join that
would fetch parent and children records in the same query, and then
use <group column=group_key> tag to iterate over the records with
the same "key" until the key changes (see "group" template tag docs).
in this case, pagination is very hairy, especially with joins:
     -> group "key" has to be same as the -page_query "key", where
     "key" is the query column
     -> if multirow query and page_query use the same joins, then the
     page_where_clause will list the same "key" id multiple times:
     for instance, project_id in ('455','455','466') - notice 455
     appears twice.  this causes fewer rows (than page_size) to be
     displayed on a page.  the solution is to change page_query -
     use table aliasing 'spm_projects j', the same as in the
     multirow query, but no joins.  if we don't do table aliasing,
     then the order by will break (order by j.project_id)

Joins with identical column names in two or more tables tables

Alex Vorobiev:
- pagination: add the aliased column name to -key: j.project_id
- sorting: add the aliased column name to orderby statements in the orderby block
- sorting by columns in more than one table (j.project_id
p.proposal_id) breaks because both tables need to appear in the
page_query, which in turn breaks # of records per page...
i thought that wrapping the page_query select in a "select distinct"
or select with a group by would yield, but both actually re-sort by
the distinct or groupby column, so there is no way to preserve the
sort order of the inner select statement... giving up for now until the new
paginator is out which may make all of this a moot point.

Misc. issues


  • The best documentation is the api-doc documentation for template::list (since the website is on a version of OpenACS before 5.0, it doesn't include documentation for list-builder. You'll need to look on your installation).
  • Look in to see examples of list-builder usage. Lars has added it in to bug-tracker, forums, and logger, so you can use them as examples. Project-manager (in /contrib) also uses list-builder.
  • More documentation is available.