Sub-totalling with List Builder is a great feature and I would definitely recommend using it over hand-coding your sub-totals but isn't fully documented, and there are a few gotchas, so I've put a few notes together to help you use it.



Let's just take a simple list with 2 columns: stage_description and financial_amount. We want to aggregate financial_amount for each stage_description. So in the beginning the elements section of our list looks like this:

  -elements {
          stage_description {
            label "[_ payment.Financial_Stages]"
          }
          financial_amount  {
            label "[_ payment.Amount]"
          }
    }





1. First we create a variable named groupby – give it a value of the name of the element we want to group by. Put this before our list definition. Behind the scenes this tells List Builder to create a filter named "groupby" (according to the docs you may need to put this in your ad_page_contract – I didn't need to):

set groupby "stage_description"



2. Add a new groupby section to end of the list. "type multivar" just tells List Builder to tell ad_page_contract that the groupby is not a multiple element. All the curly brackets are unfortunately needed – the first list element is the text which is displayed in front of each group; you can put HTML tags and more in here. The second list element tells List Builder that we are grouping by stage_description – this seems repetitive so maybe there’s a better way to do this, but I can't see any issues with it. You can also have an orderby clause in here if you need one.

  -groupby {
      type multivar
      values {
        { {[_ payment.Financial_Stage]} { {groupby stage_description} } }
      }
  }



3. Modify the financial_amount element to add aggregation information. aggregate can be one of “sum”, “average” or “count”. The aggregate_group_label allows you to put some text and a currency symbol before each sub-total. aggregate_label does the same for the grand total.

          financial_amount {
            label "[_ payment.Amount]"
            aggregate "sum"
            aggregate_group_label "[_ payment.Sub-Total]: [lc_get -locale $locale currency_symbol]"
            aggregate_label "[_ payment.Grand-Total]: [lc_get -locale $locale currency_symbol]"
          }



4. Gotchas to look out for:

you need to  use the -pass_properties section to allow List Builder to see program variables outside the list scope e.g. $locale.



List builder doesn't differentiate between currency and numbers – it just treats the aggregated column as a number. However, it does use the acs-lang formatting when displaying (specifically lc_numeric), which should be good enough for most cases. You need to do your own formatting on the individual line items. Here's how to use display_eval to format the financial_amount element:

display_eval {[lc_monetary -label_p "t" $financial_amount $locale ]}



Noquoting can be painful but the following workaround should suffice – note the triple underscore:

display_template {@financial.financial_amount___display;noquote@}




Our finished example:

  -elements {
          stage_description {
            label "[_ payment.Financial_Stages]"
          }
          financial_amount {
            label "[_ payment.Amount]"
            aggregate "sum"
            aggregate_group_label "[_ payment.Sub-Total]: [lc_get -locale $locale currency_symbol]"
            aggregate_label "[_ payment.Grand-Total]: [lc_get -locale $locale currency_symbol]"
display_eval {[lc_monetary -label_p "t" $financial_amount $locale ]}
display_template {@financial.financial_amount___display;noquote@}
          }
    }
  -groupby {
      type multivar
      values {
        { {[_ payment.Financial_Stage]} { {groupby stage_description} } }
      }
  }





A good example from OpenACS CVS:

http://cvs.openacs.org/cvs/openacs-4/packages/dotlrn-ecommerce/lib/tree-chunk.tcl?rev=1.2&view=markup

Goal: Add an autosuggest textbox similar to Google's autosuggest.

Assumptions:

1. You have installed the ajaxhelper package.
2. You do not want to fetch the data everytime and just fetch it once.

This is somewhat similar to Ryan Gallimore's smart search widget. The differences are:

• Doesn't hit the database everytime a key is pressed
• Support for search term and description in the results like Google's autosuggest
• Doesn't need an additional page for the ajaxupdater to load as it already supports query or list passing

Steps:

1. For the input field, just create a textbox with id parameter set to "autosuggest_box" (current limitation). In ad_form you can do this like so:

   {tags:text,optional {html {size 50 id autosuggest_box}}....

2. Generate the ajaxhelper source for autosuggest

   set ah_sources [ah::js_sources -source "autosuggest"]

3. Generate the array for the suggest box. The first element will be the value for autosuggestion and the second one for the description.

   set query "select username, email from cc_users"
set ah_suggestions_array [ah::generate_autosuggest_array -sql_query $sql_query]
   or
   set array_list {{apple red} {banana yellow} {orange orange}}
set ah_suggestions_array [ah::generate_autosuggest_array -array_list $array_list]

4. In the corresponding adp file, add in the following

   <property name="header_stuff">
@ah_sources;noquote@
<script type="text/javascript">
@ah_suggestions_array;noquote@
</script>
</property>


5. Call the loadAutoSuggest js proc on load

   window.onload=loadAutoSuggest();

6. You should now be able to type into the textbox and fire up the autosuggest script.

Some nice things to do

• integrate into ad_form so that one can just do "fieldname:text(autosuggest)"
• allow to be used as the values param in listfilter widgets so that it would be easier to filter by typing rather than being presented with long select boxes

a way to create/handle big dumps (by Steve):

pg_dump yourdb | split -b 1024m - yourdb_

which will create 1Gb files called yourdb_aa, yourdb_ab, yourdb_ac etc.

Then you can import them using cat:

cat yourdb_* | psql yourdb