Pivot Table v1.0.1
Published by Widget Studio on April 13, 2019Updated on April 10, 2020
Tags: Analysis,Charting,Data Presentation
Download Send To Carafe
Created By: Geist interactive
Version: 1.0.1
Windows Tested: No
Offline Compatible: No
Description:
Version: 1.0.1
Windows Tested: No
Offline Compatible: No
Description:
This widget will summarize your data in a pivot table or cross tab report with little effort. All you have to do is provide it an array of arrays (see the data for the example).
The first element in the array is an array of columns in the rest of the data.
[
"Sales",
"Year",
"Units Solid",
"Price",
"Month",
"Type"
],
The remaining elements are the values that correspond to the columns in the first.
[
127.6,
2017,
319,
0.4,
"May",
"Blackberries"
],
There are many color options. You can also set the default columns (up to 2) and default rows (up to two), or you can leave both axes blank.
Finally there's a default renderer (how the data will be presented), a default aggregator.
A complex pivot table or cross tab chart that summarizes your data and presents it in many different ways.
There's easy drag-drop and filtering functionality to update the data at any moment.
Configuration
| Name | Description | Type |
|---|---|---|
| borderSize | number | |
| data | jsonArray |
7 Comments
Carles Barbal · April 24, 2019 at 7:11 am
How can I insert the pivot.es.js library to have it in Spanish?
Jeremiah Small · May 14, 2019 at 2:48 pm
Hello Carles,
What you’re talking about is called “localization” and it is a topic that generally applies to the underlying bundled code. Support for localization tends to vary from one library to the next. The place you’ll want to look is at the base bundled projects documentation and support resources. You might end up having to make changes to a bundle to support localization options. In that case you would split it off into your own version and develop that to suit your needs. FYI, we’ve just set up a JavaScript Integration area over at FM Forums. Please feel free to help inhabit that new community.
Jerrad · May 9, 2019 at 7:42 am
Awesome tool! Alas, since FileMaker hasn’t gotten off their duff to support anything better than IE, the Pivot Table won’t work out of the box on Windows. Specifically, there is a trailing comma in the renderers extension parameter that causes things to blow up. Remove it, or the whole extension parameter (for a more limited tool), and it works fine.
Jeremiah Small · May 14, 2019 at 2:37 pm
Thanks for the tip! FYI, we’ve just set up a JavaScript Integration area over at FM Forums. Please feel free to help inhabit that new community.
prometheus · October 11, 2019 at 8:01 pm
Can you recommend a custom function to build the correct JSON from fields on the layout? Saw some old methods out there curious if anything using the While function is more relevant?
Jeremiah Small · November 4, 2019 at 10:22 pm
Sorry for the delay in responding.
My favorite way is to do it using the native FileMaker JSON functions. Typically I start with a working sample in a text editor I can look at off to the side. Then I use the Data Viewer and work on assembling a FileMaker expression which outputs the sample using the static literal values in the sample. Then once my expression is working right, I go through and replace the sample strings with field references. Here’s a very simple demonstration:
Working Sample
{"foo": "bar",
"baz": "bat"
}
Expression With Static Values
JSONSetElement ( $json; [ "foo" ; "bar" ; JSONString ]
; [ "baz" ; "bat" ; JSONString ]
)
(Tip: format the repeatable elements in JSONSetElement expressions with the leading semi-colon on each line to keep them tidy and scannable.)
Expression With Field Values
JSONSetElement ( $json; [ "foo" ; TO::Foo ; JSONString ]
; [ "baz" ; TO::Baz ; JSONString ]
)
Keep in mind that the FileMaker functions will alphabetize your object property names, which can make it tedious to compare the model and prototype so it can be handy to put your sample data into a field or assign to a variable using Insert Text, neither of which will force you to deal with escaping quotes, and then wrap that in a JSONFormatElements, so that your sample and your prototype are formatted the same.
Format Sample Data
JSONFormatElements (TO::SampleJson
)
It can get tricky when your sample JSON is complex and nested. Typically I try to break it into parts, and use scripts and loops to assemble more complex structures.
You may see some tricks using ExecuteSQL or other techniques that don’t utilize the native JSON functions. I suggest avoiding those unless there’s a compelling reason, such as the need to load massive amounts of JSON data (performance optimization), or the need to support periods in object key names (something that FileMaker JSONSetElement function doesn’t support), because this makes the solution brittle and risks being “clever” as an end in itself.
Jeremiah Small · November 4, 2019 at 10:40 pm
As it particularly regards this bundle, I suggest this trick. If you start with an array (empty or otherwise), and you use integer keys starting with zero, you will get elements assigned to an array instead of an object. It can be a nice idea to make a stored calc field that formats each record as an array. Then you can loop over the data set adding the pre-computed JSON Array expression to your main JSON Array.
Label
JSONSetElement ( "[]"; [ 0 ; "Sales" ; JSONString ]
; [ 1 ; "Year" ; JSONString ]
; [ 2 ; "Units Sold" ; JSONString ]
; [ 3 ; "Price" ; JSONString ]
; [ 4 ; "Month" ; JSONString ]
; [ 5 ; "Type" ; JSONString ]
)
Prototype Data
JSONSetElement ( "[]"; [ 0 ; 127.6 ; JSONNumber ]
; [ 1 ; 2017 ; JSONNumber ]
; [ 2 ; 319 ; JSONNumber ]
; [ 3 ; 0.4 ; JSONNumber ]
; [ 4 ; "Month" ; JSONString ]
; [ 5 ; "Type" ; JSONString ]
)
Stored Calc Field Data
JSONSetElement ( "[]"; [ 0 ; Table::Sales ; JSONNumber ]
; [ 1 ; Table::Year ; JSONNumber ]
; [ 2 ; Table::Units Sold ; JSONNumber ]
; [ 3 ; Table::Price ; JSONNumber ]
; [ 4 ; Table::Month ; JSONString ]
; [ 5 ; Table::Type ; JSONString ]
)
Set Variable Script Step Adding Each Record in Loop
JSONSetElement ( $dataArray; [ $counter ; Table::JSON ; JSONArray ]
)