Forum OpenACS Development: SharePoint: AJAX Tables for List Builder?

Hi,

I've just had a look at M$ SharePoint, and I'm amazed by the list structures available (lists make up 60%-80% of all ]po[ GUI elements in wome way or another). Apart from that I was struck by the similarities between OpenACS and Sharepoint. The concepts are more or less the same, just the user base is some 1.000 times larger...

I'm just wondering if something similar to SharePoint's tables have already been developed for OpenACS. We're particularly looking for:

- Pagination (only pull out those 50 lines from the database that you need, out of the millions of data sets in your table).
- In-place eding (click on an element, enter a new value in an appropriate _widget_ that opens and submit the value)
- Customized appearance (allow users to select the columns they are interested in) and
- Sorting

Cheers!
Frank

Hi Nathan,

Great to see there's work going on.

works around list builder

I know that there are many fans of the current List Builder around. We also use it a lot in ]po[, but only for small lists, because of it's client-side only pagination. So I don't expect that our requirement of server-side pagination will become part of an OpenACS standard...

inline cell-editing

I believe you're underestimating this one. At least in ]po[ we are working with DynFields (Quest name: FlexBase, an extension of AMS) to determine the widgets for editing object attributes. "Widgets" frequently contain the ranges of values for drop-down boxes, but can also include default values, security permissions and data range constraints. Widgets would also include AJAX stuff like calendar selects, auto-completion fields etc.

Also, you'll need a generic "receiver" for the results from in-line editing, which is definitely not trivial. This receiver would have to perform data-range and permission checking (permissions in DynField can be per column) and possibly convert object names to object IDs etc.

So I think that inline editing would be tightly related to Form Builder...

Could you please let us know if you are progressing on your side?

Thanks for your reply!
Frank

Frank, you mentioned:

We also use it a lot in ]po[, but only for small lists, because of it's client-side only pagination

What do you mean by client side only pagination? Last time I developed with list builder (http://cognovis.de/developer/en/list_pagination) the pagination made sure only to retrieve the rows from the database which were needed to display the result to the end user, taking into account any filters first.

With regards to the editing of table values with widgets, I am wondering if from a user point of view we provide an editable version of the table (actually displaying a table with from builder) or if we are providing a cell based editing (e.g. if you click on a value, a single widget form pops up which allows you to edit the content if you have editing permissions, otherwise just use the display part of formbuilder).

That's essentially what my Datatable table.adp list template does. My table.adp is a drop-in replacement for the existing one and does progressive enhancement. I actually did it for cosmetic reasons (to match other non-list Datatables), and to get the row highlighting. Beyond that, the only thing I can think of that this provides is client-side sorting, which ultimately is quite difficult to do right in a template because of the varying data types.

If we could define data types for the columns and specify a URL for retrieving JSON data to populate, we'd have pretty much everything we need for an "ajaxified" table except inline editing, but as mentioned previously, the inline editing might be better handled by a form template. I'm really not sure about that, yet.

I've not done Drag and Drag with listbuilder, but I used ajaxhelper and scriptaculous to drag and drop divs, if that helps any.

Ya'll should check out Sencha and their product called Ext JS:

https://www.sencha.com/products/js/

It has a grid with pagination, sorting, in-line editing, drag-drop (say to another grid or from a tree), some searching plugins, complex event handlers, etc.

Its dual license, so you might be able to use it free, but a commercial license is pretty cheap for 1 developer.

We've used it extensively with openacs. One cool thing we do is let users pick which columns they want to see (on an admin page), then have the grid render with those columns in their specified order.

Taras

I did not use list builder. The examples from Sencha are basically where I started. You will notice that JSON is used for the data for the table, the list of columns for the table, and table definition itself - so its easy to programatically insert something dynamic instead of writing that manually. For example, instead of columns 'a', 'b' you could have a multirow that returns the columns thats specific to a group or user.

This is a really really basic example I wrote for someone a year ago of how to get data from a db_multirow into a grid:

Sencha Thread

There are of course better ways to do what I was doing, but the idea is to start with the Sencha grid then get dynamic data into it as a starting point. We've since written a db_multirow2json function, plus postgresql 9.0 will have an extension that converts the query directly to json (that my company is actually the sponsor for).

Here is a simple multirow2json proc written by one of our guys:

namespace eval prototype {}

ad_proc -public prototype::mr2json {
{-key_column "id"}
datasourcename
} {
Converts a template::multirow to a JSON string. The current design creates an
object where each value is a record and the key is the value that could be used
to update or retrieve the original record.

Future designs could include the option to return an array of objects.

@author Justis Peters justis AT ivc.com

} {
set cols [template::multirow columns $datasourcename]
append ret "{\n"
template::multirow foreach $datasourcename {
append ret "'[set $key_column]':"
append ret "{"
foreach c $cols {
set currval [regsub -all {'} [set $c] {\'}]
append ret "'$c': '$currval',"
}
append ret "},\n"
}
append ret "}"
return $ret
}

BTW, the perl module for postgres is about done:

andrew=# select query_to_json('select usename, usecreatedb, valuntil,usesysid from pg_user limit 2');
query_to_json
---------------------------------------------------------------------------------------------------------------------------------------------------
[{"usesysid":10,"usecreatedb":true,"valuntil":null,"usename":"andrew"},{"usesysid":16392,"usecreatedb":false,"valuntil":null,"usename":"blurfl"}]

Its very nice for doing ajax calls with - including tables.