Loading...
 

Merge Tiki Spreadsheet into Tiki Trackers

This is an idea

Pros

  • Get rid of former and unfinished for too long spreadsheet integration in Tiki, with partial support from the developer (not much support at all in the last years, in addition)
  • Use maintained code by other people, such as handsontable or others
  • Get a table editor (for formulas, adding or editing data like in a spreadsheet) back working as expected in the supported stable tiki versions (including LTS versions)
  • Link trackers and spreadsheets trough pretty mature software: pivottable with handsontable editor

Cons

  • handsontable doesn't have the best FLOSS model that we would like
  • Some extra options and libraries needed in handsontable (formula support, etc) doesn't seem to come pre-integrated in the floss versions of handsontable, even if they can be obtained through github and other means with the right license to get them integrated within tiki code base also.

Objections/Questions

  • Even I like the idea and we have proof that it is possible to import Tiki tracker data to a Tiki integrated spreadsheet directly in Tiki with a simple dialogue, there are challenges to match the different concepts of Trackers and Spreadsheets :
    Trackers are updated dynamically with a pre-defined form which is usually not altered by users (only by admins) on a per item base where one item represents a full row of a spreadsheet. Thus columns and formulas are not altered, but automatically added on item creation.
    Spreadsheets are more static, but when they are edited, users, not only admins, can (and likely will) alter formulas, add or delete colums etc.. Think about a dynamically connected tracker or a spreadsheet that fetches data from a tracker and users add or delete formulas, columns etc. - this will not continue to work.
  • basic differences:
TrackerSpreadsheet
Content added/collected easily by/from large number of non-tecchie users with limited permissions on the tracker, but limited calculation capabilities - even not (yet) a way to sum the content of a column (of one tracker_field over several items) Very flexible when it comes to calculations - like a matrix, but content is added by few users with high permission level
High control level of altering columns and formulas Low to zero control of who is altering columns, formulas and content in which way, once access is granted
Calculation is set up in field definitions and thus valid for all future added content/items Calculation is setup on a per field or field-range basis, which is very easy for static calculations. Even likely possible in Spreadsheets to calculate added content dynamically to some extent without altering the formulas, this seems to become very complicated to setup when content has to be added automatically by users using forms
  • Maybe it makes more sense to integrate a more maintained jQuery spreadsheed (vendor project) and add a dynamic tracker to spreadsheet export, where the imported table would be automatically locked for all non-superuser users, but calculations could be performed in additional tables of the same spreadsheet ... similar like in Excel/Calc tables can fetch and process data from other tables!?
  • In a Calculations Use Case I described a possible way to use a new (not yet existing) field_type very similar to the field_type 'mathematical calculation' and existing field types for optional filtering to calculate the culomns of another tracker.
  • I can still imagine, that a good setup spreadsheet with a dynamic tracker data import and calculation triggered automatically could provide kind of autoupdated statistical data ... thus kind of a mini-R system inside a Tiki on a shared hosting without the need of external servers and hired specialists.

Opinions

  • I think a decent integration of Trackers and Spreadsheets or autodynamic data import from trackers to (then locked) spreadsheets makes a lot of sense, but both features for now make imho more sense as two different features in favor of a merge of two different concepts. Basically a tracker is a data fetching form with a lot of display, filter, transition etc. and some calculation capability, which can output aswell a table view, whilst a spreadsheet is nothing else than a table with high calculation capabilities and some options to display results. Too different to merge, similar enough to interlink dynamically.

How would we do this?

x-spreadsheet

A web-based JavaScript (canvas) spreadsheet https://myliang.github.io/x-spreadsheet

Handsontable (no longer Open Source)

[+]

jExcel

  • Alternatively, use jExcel library (MIT Licensed): a lightweight alternative in case we like better the open source model and in case it allows easily to add/hack more formulas than the ones they provide, add ability to make charts from its data, etc.:
    https://jspreadsheets.com/jexcel.html
    https://github.com/paulhodel/jexcel
    jExcel is is a very light jquery plugin to embed a spreadsheet, compatible with Excel, in your browser. You can load data straight to a jExcel table from a JS array, json or even a CSV file. You can copy and paste from or to Excel straight to a jExcel table. You can easily integrate jExcel with other third party jquery plugin to create your own custom columns, custom editors, and much more. jExcel has a plenty of nice features such as key-value dropdown, CSV loading/exporting, multiple spreadsheets and much more. We have a large roadmap ahead and we are constantly improving, so don't forget to send us your ideas.


Keywords

The following is a list of keywords that should serve as hubs for navigation within the Tiki development and should correspond to documentation keywords.

Each feature in Tiki has a wiki page which regroups all the bugs, requests for enhancements, etc. It is somewhat a form of wiki-based project management. You can also express your interest in a feature by adding it to your profile. You can also try out the Dynamic filter.

Accessibility (WAI & 508)
Accounting
Administration
Ajax
Articles & Submissions
Backlinks
Banner
Batch
BigBlueButton audio/video/chat/screensharing
Blog
Bookmark
Browser Compatibility
Calendar
Category
Chat
Comment
Communication Center
Consistency
Contacts Address book
Contact us
Content template
Contribution
Cookie
Copyright
Credits
Custom Home (and Group Home Page)
Database MySQL - MyISAM
Database MySQL - InnoDB
Date and Time
Debugger Console
Diagram
Directory (of hyperlinks)
Documentation link from Tiki to doc.tiki.org (Help System)
Docs
DogFood
Draw -superseded by Diagram
Dynamic Content
Preferences
Dynamic Variable
External Authentication
FAQ
Featured links
Feeds (RSS)
File Gallery
Forum
Friendship Network (Community)
Gantt
Group
Groupmail
Help
History
Hotword
HTML Page
i18n (Multilingual, l10n, Babelfish)
Image Gallery
Import-Export
Install
Integrator
Interoperability
Inter-User Messages
InterTiki
jQuery
Kaltura video management
Kanban
Karma
Live Support
Logs (system & action)
Lost edit protection
Mail-in
Map
Menu
Meta Tag
Missing features
Visual Mapping
Mobile
Mods
Modules
MultiTiki
MyTiki
Newsletter
Notepad
OS independence (Non-Linux, Windows/IIS, Mac, BSD)
Organic Groups (Self-managed Teams)
Packages
Payment
PDF
Performance Speed / Load / Compression / Cache
Permission
Poll
Profiles
Quiz
Rating
Realname
Report
Revision Approval
Scheduler
Score
Search engine optimization (SEO)
Search
Security
Semantic links
Share
Shopping Cart
Shoutbox
Site Identity
Slideshow
Smarty Template
Social Networking
Spam protection (Anti-bot CATPCHA)
Spellcheck
Spreadsheet
Staging and Approval
Stats
Survey
Syntax Highlighter (Codemirror)
Tablesorter
Tags
Task
Tell a Friend
Terms and Conditions
Theme
TikiTests
Federated Timesheets
Token Access
Toolbar (Quicktags)
Tours
Trackers
TRIM
User Administration
User Files
User Menu
Watch
Webmail and Groupmail
WebServices
Wiki History, page rename, etc
Wiki plugins extends basic syntax
Wiki syntax text area, parser, etc
Wiki structure (book and table of content)
Workspace and perspectives
WYSIWTSN
WYSIWYCA
WYSIWYG
XMLRPC
XMPP




Useful Tools