Pull/Pivot record values from rows to columns

App Development
1

Hey Tada Community-

Question…does anyone have options or ideas to be able to pull data values that are entered into rows in one table and add them to column values in another table?

Below you can see two pictures that depict what I have a need for…I welcome all suggestions including third party integration such as Zapier or Integromat.

DATA PULLED FROM THIS TABLE

pivot
pivot2210×696 258 KB

DATA DISPLAYED IN THIS FORMAT

example final pivot
example final pivot1536×470 383 KB

Cheers,
Adam

2

Hi Adam,

I don’t think this would be possible natively, but I’ve seen this done using custom code. If I recall, it was quite complex.

I will track down the code someone once shared with me and see if it can be applied here as well.

3

I hope this can help you at least get started, but this is very complex and requires a developer to likely make it happen. Hopefully, this can at least help with the direction.

You can use an external library, something like this: https://github.com/nicolaskruchten/pivottable?utm_source=cdnjs&utm_medium=cdnjs_link&utm_campaign=cdnjs_library

Add the script to the footer of your app:

<script src="https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.23.0/pivot.min.js" integrity="sha512-XgJh9jgd6gAHu9PcRBBAp0Hda8Tg87zi09Q2639t0tQpFFQhGpeCgaiEFji36Ozijjx9agZxB0w53edOFGCQ0g==" crossorigin="anonymous"></script>

Add this to the header of the app:

<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.23.0/pivot.min.css" integrity="sha512-BDStKWno6Ga+5cOFT9BUnl9erQFzfj+Qmr5MDnuGqTQ/QYDO1LPdonnF6V6lBO6JI13wg29/XmPsufxmCJ8TvQ==" crossorigin="anonymous" referrerpolicy="no-referrer" />

image
image1094×490 34.5 KB

Let’s assume you’re able to load all these records on the page (in my example, its component_3.

image
image1202×255 7.06 KB

Next, I would add an HTML snippet to the page where we want the pivot table to appear:

<div id="output">&nbsp;</div>

image
image1542×820 31.7 KB

Finally, we can take our data and display it in a configurable pivot table by adding this to the JS section:

TB.render('component_3',function(data){
    console.log(data.records); 
    var utils = $.pivotUtilities;
  
    $("#output").pivotUI(
        data.records, 
        {
        
        }
    );   
});

You will now have a custom configurable pivot table:
image

The issue now is that the fields are identified by id vs their name. I’m sure with a bit more tweaking we can convert the field ids to names.

Here’s my example:
https://localtest.tadabase.io/adam-pivot#!/test-data

Once you’ve gotten all the settings configured how you like it, you can set it to not show the UI and set all the parameters programmatically in the Javascript, like this:

TB.render('component_3',function(data){
    console.log(data.records); 
    var utils = $.pivotUtilities;
  
    $("#output").pivot(
        data.records, 
        {
            rows: ["field_36"],
            cols: ["field_38"]
        }
    );
});

image

I hope this helps, this is a bit beyond the scope of our support, but I’d be happy to keep assisting if I can.

4

Many thanks @moe! I was able to get it all to work out.

Cheers,
Adam