There is already some information by Jade Rubick on listbuilder. Furthermore a posting describes how to make good use of listbuilder. Not to forget the template::list::create documentation itself. Sadly I still did not know what to do, so I looked at bugtracker and my findings are posted here. I assume that you covered already the basics of the creation of the list itself along with the elements. I usually set my elements, filters and orderbys in a variable, so you would have to call them using e.g. -orderby $orderbys in the template::list::create call. 

Passing variables through

You can pass variables that your page needs using the filters. As all filters will be passed through, adding the variables in the filter section like "foo {}" does the trick. For more information look at this posting. 

HTML in Columns

If you want to have HTML included in columns you are forced to explicitly set the column to ";noquote". You can do this with the following trick

body {

label "[_ mail-tracking.Body]"

display_col body;noquote

}

Bulk Actions

If you want to pass some url-variables to the bulk-action, you can use the -bulk_action_export_vars option, but bear in mind that listbuilder makes use of some internal variables like "action" so that you cannot export a variable with this name unless you want to risk a very very nasty bug in internet-explorer.

Categories

The following code will get you one pretty list of tree-names and category-names: 

a) you can have a data query and a pagination query. in the data query, you join with category_object_map and select the category_id 

b) category::list::collapse_multirow -object_column <<id-column-of-multirowmultirow-name>> 

c) multirow extend <<multirow-name>> categories 

d) multirow foreach <<multirow-name>> { 


    set categories [category::list::get_pretty_list $category_id] 


} 

If you want to get one extra column in the multirow for each category tree with a pretty list of the category-names, then do the following: 

do a) and b) like before 

c) category::list::prepare_display -tree_ids <<list-of-tree_ids-to-displaymultirow-name>> 

d) category::list::elements -categories_column category_id -tree_ids <<list-of-tree_ids-to-display>> -name <<multirow-name>> 

e) you now add column "categories_$tree_id" to your element-list in the list::create block for each tree that you want to display

In a nutshell, to combine multiple categories (including subcategories with the "and" clause, you would use this subquery:

select v.object_id 


from (select distinct m.object_id, c.category_id 


from category_object_map m, categories c, 


categories c_sub 


where c.category_id in ([join $category_ids ,]) 


and m.category_id = c_sub.category_id 


and c_sub.tree_id = c.tree_id 


and c_sub.left_ind >= c.left_ind 


and c_sub.left_ind < c.right_ind) v 


group by v.object_id having count(*) = :category_ids_length 


 

to include subtrees and combine selected categories with "or", use this subquery:

select distinct m.object_id 


from category_object_map m, categories c, 


categories c_sub 


where c.category_id in ([join $category_ids ,]) 


and m.category_id = c_sub.category_id 


and c_sub.tree_id = c.tree_id 


and c_sub.left_ind >= c.left_ind 


and c_sub.left_ind < c.right_ind 


 

for exact categorization combined with "and", you would use:

select m.object_id 


from category_object_map m 


where m.category_id in ([join $category_ids ,]) 


group by m.object_id having count(*) = :category_ids_length 

for exact categorization combined with "or", use:

select distinct m.object_id 


from category_object_map m 


where m.category_id in ([join $category_ids ,]) 

Filters

Filters work exactly like ordering. You first to have to extend the template::list::create with a filter statement:

set filters {
    expertises {
        label "Expertises"
        values $expertises_options
        where_clause {t.expertises like '%$expertises%'}
        default_value ""
    }
}

Once this is achieved you can the filter clause to your SQL Query (before the orderby):

[template::list::filter_where_clauses -and -name "contact"]

Last but not least, you need to enable *each* element that you filter as a form variable in the ad_page_contract declaration.  If you want to support a filter for a multiple choice option (e.g. a checkbox) and the result is stored in a TCL List (as is the default), you can make use of a trick. Let's assume that the expertises field is a checkbox, then you have to ammend the code a little bit to work with this situation.

In addition it is adviseable to join the multiple choosen values in the db_multirow call, otherwise the display might be a little bit distorted in your table.

set expertises [join $expertises ", "]

Note: It is mandatory to add the filter_where_clause to both queries (the normal one as well as the one for pagination). 

Note2: If you want to clear a filter, you have to unset the variable. Setting it to empty string will result in a match against "NULL" (where country_id = NULL).

Formats and Forms

Formats allow you to display your list in multiple different ways. The two things we are using it for are 

1. CSV Output 
   
   
   If you want to have a format for CSV output alternatively, just add the following to your template::list::create. Note that the rows are the rows that actually show up on the list (see 2. on how we can make good use of it). 
   

   -formats {
   
   
   normal {
   
   
   label "[_ project-manager.Table]"
   
   
   layout table
   
   
   row {
   
   
   project_name {}
   
   
   customer_name {}
   
   
   category_id {}
   
   
   earliest_finish_date {}
   
   
   latest_finish_date {}
   
   
   actual_hours_completed {}
   
   
   }
   
   
   }
   
   
   csv {
   
   
   label "[_ project-manager.CSV]"
   
   
   output csv
   
   
   page_size 0
   
   
   row {
   
   
   project_name {}
   
   
   customer_name {}
   
   
   category_id {}
   
   
   earliest_finish_date {}
   
   
   latest_finish_date {}
   
   
   actual_hours_completed {}
   
   
   }
   
   
   }
   
   
   } \

2. Limit the displayed elements dynamically (e.g. when calling the list as an include). Take note that you have to make use of the selected_format otherwise listbuilder will display all available elements: 

Adding form elements

You can easily add a form variable to listbuilder when creating the elements section. Here is a snippet which I took from anOpenACS posting:

Possible values are

• absolute_links: '0'
• abstract_p: 'f'
• auto_save: 'false'
• creation_date: '2006-12-23 18:50:36.673497+01'
• creation_user: '454'
• creator: 'Malte Sussdorff'
• current_user: '454'
• description: ''
• do_substitutions: '1'
• folder_id: '-100'
• form: '::xowiki::WikiForm'
• id_column: 'page_id'
• last_modified: '2007-08-14 17:14:10.479271+02'
• mime_type: 'text/html'
• modifying_user: '454'
• name: 'en:list_formats'
• nls_language: 'en_US'
• object_id: '80394'
• object_type: '::xowiki::Page'
• package_id: '53314'
• page_id: '80394'
• page_order: 'III.3.3'
• parent_id: '53329'
• pretty_name: 'XoWiki Page'
• pretty_plural: 'XoWiki Pages'
• publish_date: '2007-08-14 17:14:10.479271+02'
• publish_status: 'ready'
• recursion_count: '0'
• references: ''
• render_adp: '1'
• revision_id: '80394'
• security_inherit_p: 't'
• sql_package_name: '::xowiki::Page'
• storage_type: 'text'
• supertype: 'content_revision'
• table_name: 'xowiki_page'
• title: 'Formats'
• unresolved_references: '0'
• with_table: 'true'

Making the list an include 

Now you should have a perfectly nice page with filters, pagination, odering and so on. What we are going to do now is to rip the core (aka the list) out from this page and store it in a library file. This has the advantage that you can reuse the list from wherever you are. Lets take a look at the .tcl file first. 

After copying your file to the /lib directory, strip off the whole ad_page_contract, you won't need it. On the other hand you will need three more variables set: 

• page_size will define how many items will be displayed on the page
• display_mode defines whether the filters and / or list shall be displayed. This is very useful if you want to split up the filter part and put it somewhere else than the list or if you want to add HTML code below the list. 
• package_id defines the package_id as it is perfectly possible to call this page now from outside the current package

This should look in the end like this:

# expects
#    orderby:optional
#    page:optional
#    {page_size "25"}
#    themes:optional
#    {country ""}
#    activity:optional
#   {searchterm ""}
#    {filters_p "1"}
#   project_id:required

foreach required_param {project_id} {
    if {![info exists $required_param]} {
        return -code error "$required_param is a required parameter."
    }
}

foreach optional_param {orderby searchterm page themes country activity} {
    if {![info exists $optional_param]} {
        set $optional_param {}
    }
}

if ![info exists page_size] {
    set page_size 25
} 

if ![info exists display_mode] {
    set display_mode "all"
}

if ![info exists package_id] {
    set package_id [ad_conn package_id]
}

As you might have realized, you should exchange the hardcoded 25 for the page_size with the $page_size variable used now. The .adp file has to be changed as well. All mentioning of "<master>" and "<property>" has to go as well. Furthermore, we need to check if we should display the filters. The result looks like this:

<if @display_mode@ eq "list">
  <listtemplate name="tasks">
  </listtemplate>
</if>

<if @display_mode@ eq "filter">
  <form method="post" name="search" action="tasks">
    Suchen:<br />
    <input type="text" name="searchterm" value="@searchterm@" size="12" />
    @hidden_vars;noquote@
  </form>
  <listfilters name="tasks">
  </listfilters>
</if>

<if @display_mode@ eq "all">
  <table cellpadding="3" cellspacing="3">
    <tr>
      <td class="list-filter-pane" valign="top" width="200">
        <form method="post" name="search" action="tasks">
          Suchen:<br />
          <input type="text" name="searchterm" value="@searchterm@" size="12" />
          @hidden_vars;noquote@
        </form>
        <listfilters name="tasks">
        </listfilters>
      </td>
      <td class="list-list-pane" valign="top">
        <listtemplate name="tasks">
        </listtemplate>
      </td>
    </tr>
  </table>
</if>

Use dynamic elements

If your list is an include you might want to call it from various locations. Sadly, some locations need more information in the list than others. This is where a trick comes in handy that let's you set elements dynamically. 

Limit the displayed elements dynamically (e.g. when calling the list as an include):

# Get the rows to display 

set row_list "project_name {}\n" 


foreach element $elements { 


append row_list "$element {}\n" 


} 

template::list::create \ 


-formats { 


normal { 


label "[_ project-manager.Table]" 


layout table 


row $row_list 


} 


csv { 


label "[_ project-manager.CSV]" 


output csv 


page_size 0 


row $row_list 


} 


} \ 

This example assumed that you will use the orderby and filter of an element if the element is to be displayed as per the original file. If your list allowed you to order and filter by country before you will have the option to order and filter by country if the country should be displayed in the list. If you want more flexibility, you need to take a different approach (the description that follows does exactly the same as above). 

First, decide which elements have to be displayed in the list. This is usually the element that let's you click on the name and direct you to the object that is behind the name, e.g. the last_name for a list of users or the project_name for a list of projects. Once you made the decision, instanciate the three lists for the elements, orderby and filters. Note that the orderby_list contains the default order. 

set element_list [list project_name [list \ 


label "[_ project-manager.Project_name]" \ 


link_url_col item_url \ 


link_html {title "[_ project-manager.lt_View_this_project_ver]"} 


] 


] 

set orderby_list [list default_value $default_orderby \ 


project_name [list \ 


label "[_ project-manager.Project_name]" \ 


orderby_desc "upper(p.title) desc" \ 


orderby_asc "upper(p.title) asc" \ 


default_direction asc 


] 


] 

set filter_list [list searchterm [list \ 


label "[_ project-manager.Search_1]" \ 


where_clause {$search_term_where} 


] 


] 

 

Now we have to add the elements accordingly. I will only post a snippet for one additional element, you should get the picture:

foreach element $elements { 


switch $element { 


category_id { 


lappend element_list category_id [list \ 


display_template "<group column=\"project_item_id\"></group>" 


] 


lappend orderby_list category_id [list \ 


label "[_ project-manager.Categories]" \ 


orderby_desc "c.category_name desc" \ 


orderby_asc "c.category_name asc" \ 


default_direction asc 


] 


lappend filter_list category_id [list \ 


label Categories \ 


where_clause {c.category_id = [join [value_if_exists category_id] ","]} 


] 


} 


} 


} 

Last but not least, replace the current element, orderby and filter options in the template::list::create command template::list::create \ 


-name projects \ 


-multirow projects \ 


-selected_format $format \ 


-key project_item_id \ 


-elements $element_list \ 


-actions [list "[_ project-manager.Add_project]" "add-edit" "[_ project-manager.Add_project]" "[_ project-manager.Customers]" "[site_node::get_pack\age_url \ 


-package_key contacts]" "[_ project-manager.View_customers]"] \ 


-bulk_actions [list "[_ project-manager.Close]" "bulk-close" "[_ project-manager.Close_project]"] \ 


-sub_class { 


narrow 


} \ 


-filters $filter_list \ 


-orderby $orderby_list

Possible values are

• absolute_links: '0'
• abstract_p: 'f'
• auto_save: 'false'
• creation_date: '2006-12-23 19:15:49.658247+01'
• creation_user: '454'
• creator: 'Malte Sussdorff'
• current_user: '454'
• description: ''
• do_substitutions: '1'
• folder_id: '-100'
• form: '::xowiki::WikiForm'
• id_column: 'page_id'
• last_modified: '2007-08-14 17:13:48.448734+02'
• mime_type: 'text/html'
• modifying_user: '454'
• name: 'en:list_includes'
• nls_language: 'en_US'
• object_id: '80393'
• object_type: '::xowiki::Page'
• package_id: '53314'
• page_id: '80393'
• page_order: 'III.3.2'
• parent_id: '53329'
• pretty_name: 'XoWiki Page'
• pretty_plural: 'XoWiki Pages'
• publish_date: '2007-08-14 17:13:48.448734+02'
• publish_status: 'ready'
• recursion_count: '0'
• references: ''
• render_adp: '1'
• revision_id: '80393'
• security_inherit_p: 't'
• sql_package_name: '::xowiki::Page'
• storage_type: 'text'
• supertype: 'content_revision'
• table_name: 'xowiki_page'
• title: 'List as includes'
• unresolved_references: '0'
• with_table: 'true'

    -filters {  
        user_id {}
    } \

<form method=post name=solutions-list-search  action=solutions-list>
  Search:<br />
  <input type=text name=searchterm value="@searchterm@" size="12" />
  @hidden_vars;noquote@
</form>

Sorting

If you want to sort the columns of the list, you will need to add an orderby sections. For each element (column) that you want to have sortable, add a code to the -orderby switch. This code can contain a label, the orderby value (alternatively, orderby_asc and orderby_desc if the order should be different from what the database suspects when appending "asc" or "desc" to the "order by" clause) and the default direction. Furthermore you can offer a default_value to the whole orderby section, which defines the default order. A result can look like this:

Once the orderbys are defined like this you can add the orderby_clause to your SQL query. At the end of your existing query add the following call to template::list::orderby_clause

This will create the orderby_clause for you and due to the "-orderby" switch prevent the need to type "order by" manually in your query. If you already have an "order by" statement in your SQL, just append the above without the "-orderby" switch 

If you call the list now in your webbrowser you will see that the column labels of your sortable elements have a link associated to toggle the sorting. Sadly clicking on it won't work, as you first need to tell your page to accept the "orderby" form value. Do this in the ad_page_contract header with:orderby:optional