Why you no pivot Quick Base?

  • 0
  • 1
  • Question
  • Updated 1 month ago
  • In Progress
The concept of the PivotTable in Excel seems so simple and novice. It is one of the first tools many people go to when they want to report out on data or try to learn something from data. PivotTable, PivotChart, Top 10, Filter buttons for all fields in a table (with select checkboxes for instances including [Select All] and [Blanks]), all seem like basic functions.

Why is it that this can't be done in Quick Base? I just don't get it.

It can be done from Access:
https://www.myonlinetraininghub.com/create-a-pivottable-direct-from-access
Photo of Adam Keever

Adam Keever

  • 996 Points 500 badge 2x thumb

Posted 1 month ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
>Why you no pivot Quick Base?

Because the feature did not clear the threshold decision of being implemented. You could put in a suggestion over at UserVoice and cross your fingers or empower yourself to implement it yourself using script. I will help you if you are reluctant to try. It is easy.

Nevertheless it is simple to implement using JavaScript using any of the available pivot table implementations. For example:

jQuery Plugin - PivotTable.js
https://pivottable.js.org/examples/

Simple Example:


https://pivottable.js.org/examples/simple.html

If you look at the code for this example you will see that the raw data for the pivot table has been already been grouped. In QuickBase you would have to grab the table data from the API and group it using Underscore's _.groupBy() method. 

This is a simple task and I might create a demo over the weekend.
Photo of Adam Keever

Adam Keever

  • 996 Points 500 badge 2x thumb
Please share if you do Dan. I find myself going back to excel for pivot tables and pivot charts. The goal with using Quick Base in our organization was to minimize the number of application platform used and automate work as much as possible, but that has been a no-go in regards to reporting using pivot tables and charts.
While those who don't know quickbase use excel at my work and create pivot tables, I use QuickBase for that and much more. QuickBase has summary reports, it has relationships, summary fields, and more, With all these ammunition, I can do Pivot and more. QuickBase is the BEST!
Photo of Adam Keever

Adam Keever

  • 996 Points 500 badge 2x thumb
I would appreciate it if you would spread your knowledge Surya by sharing an example app in the Quick Base Exchange. That would be most helpful.
That would be a tough call at my work place. Sorry!
Photo of Adam Keever

Adam Keever

  • 996 Points 500 badge 2x thumb
You can strip the data so that you are not sharing any proprietary or confidential information. It is easy.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
Sorry for the delay in posting a pivot table demo but I had a heck of a time finding an appropriate image macro for the demo. So here goes:


Unknown Comic Image

As many of you may know I am deep into math and I often use QuickBase to organize my work. I do a lot of work with something called Sparse Matrices which is basically a matrix with a lot of zeroes. You can think of a sparse matrix as a spreadsheet where the majority of the cells are empty or zero. 

Sparse Matrices arise naturally in science and engineering where entities of some type interact with a neighbor entities but not with distant entities. Sparse Matrices are so important that researchers from various organizations have compiled registries of Sparse Matrices for research and testing purposes. 

And it goes without a lot of explanation that a great way to view these Sparse Matrix registries is to produce a pivot table organized by the contributor's Group and the Year the contribution was made.

So here is the demo:



Sparse Matrices Pivot Table (Group x Year)
https://haversineconsulting.quickbase.com/db/bn38wdgzy?a=dr&rid=1

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=676

Notes:

(1) This demo uses the jQuery pivottable plugin:

https://pivottable.js.org/examples/

(2) The pivottable plugin was modified slightly to prevent it from loading as an AMD module and as modified hosted from a QuickBase code page.

(3) I tried to make my code as short as possible so that it could be adopted by other users with minimal changes. Other than the customary parameters for the dbid's, apptoken, and two code page URLs, the only parameters you would have to change in the code are the clist and and the related parent fid:
    query: `{14.EX.${kRid}}`,
    clist: "7.12",
(4) I implemented my pivot table through the view page of a parent table. If you want your pivot table to show up elsewhere it would be a simple matter of repackaging the same basic code.

(5) My example uses the new ES6 backticks:
query: `{14.EX.${kRid}}`,
and shortcut properties:
  $.ajaxSetup({data: {apptoken}});
(6) The plugin pivottable has an extensive set of features. You can aggregate on any quantity you can calculate, generate clickable drill-down pivot tables or create pivot charts with graphics and controls.

(7) You can think of that tic-tac-toe board on top of the Unknown Comic's paper bag as a sparse matrix!

As always feel free to contact me off-world using the information in my profile if you need individual assistance with your pivot table project:

 https://getsatisfaction.com/people/dandiebolt
Photo of Adam Keever

Adam Keever

  • 996 Points 500 badge 2x thumb
Thanks Dan.

Based on the PivotTable.js page it seems like this should be something that could relatively easily be added to the reporting functionality the same way that KanBan was recently added.

Having completed the work you did to display a PivotTable in Quick base, would you agree?
Photo of Adam Keever

Adam Keever

  • 996 Points 500 badge 2x thumb
Quick Base told me back in 2016 not to use a spreadsheet if i have a sparse matrix, then why you no pivot Quick Base? Quick Base has an onus to help me "find those numbers across all that empty space" and a basic summary report just doesn't cut it. I like the Dan Diebolt image on load pivot and I approve this message.

https://www.quickbase.com/blog/recognize-the-warning-signs-of-spreadsheet-abuse

Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
>Based on the PivotTable.js page it seems like this should be something that could relatively easily be added to the reporting functionality the same way that KanBan was recently added.

I would not underestimate the time and resources it takes to add a new feature to QuickBase or any other software product. When I create a demo I might spend an hour or two on it but I am unconstrained as to what library or API I might use or under what conditions the demo operates. I am sure QuickBase spends 1000x more time developing their features as they have to address a multitude of issues including sales, marketing, branding, technical, licensing, security and devops concerns.

But I am not a cheerleader for everything QuickBase does and I have no inside scoop on how they operate internally. 



Photo of Adam Keever

Adam Keever

  • 996 Points 500 badge 2x thumb
I was hoping you would say that it is super easy to add a new feature and that the PivotTable feature was added back in 2016 before the article was published and I had just missed it. Glass half full.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
It is super easy using script. That demo used a mere 29 lines of code (7 of which were blank lines for spacing) and some boilerplate setup for the IOL technique.