Pivot/Transpose Data

BAP · 03-03-2024 12:26 PM

I have payment data that is collected where I can select multiple months if a tenant pays for multiple months at a time (see first image). I then have a table that splits up those payments into their individual months for a report that I need to create (see second image). The report looks like my third image where the pivot table makes it so I can show the names of the tenants and then the amount paid for each month. Each month will have it's own column and show the amount paid. I can't get the pivot table to actually show the amount paid.

Any ideas on how I can create a pdf report with the data looking like my third image? I don't know if it can just be done with the data in the second image and using formulas and stuff on the pdf template or if I need to split the data even further onto another table and then pull from that data in the template, or just something else entirely. There are times that data needs to be edited or deleted, so keep that in mind.

Bonus points for helping me figure out how to get totals for each month at the bottom of the pdf template.

Payment Data

Split Payment Data

Split Payment data

Pivot Data from Split (Report looks something like this)

Pivot Data (Report Looks somewhat like this)

Marc_Dillon

Appsheet is lacking in pivot table functionality. To make a pivot table from a PDF template, you have to manually hard-code each column. Something like:

Name	month1	month2
<<START:people[id]>><<[name]>>	<<SELECT(payment[amount] , AND( [person]=[_THISROW-1] , [month] = "month1" ) )>>	<<SELECT(payment[amount] , AND( [person]=[_THISROW-1] , [month] = "month2" ) )>>

BAP

How would I go about making it so that first and last names don't repeat when using the start function?

Marc_Dillon

Do you have a table of people, or is it just those 2 name columns?

BAP

I do have a tenants table with all of their info. I then have a table called assignments that connects the tenants with a contract period. The Assignment ID is what connects the data to the tenant and where I get it to pull the name columns from.

I did notice your use of -1 or -2 when using [_THISROW]. Are you using that to reach up a couple of generations of parent to child relationships to grab that info?

Marc_Dillon

You should run the start expression on the tenants table then, which will guarantee only 1 row per person.

https://support.google.com/appsheet/answer/11541779?hl=en#access-columns-in-parent-grandparent-recor...

BAP

Ok, I will have to look into doing it that way. Any recommendations for how those expressions would look in the template? I assume I need to get things set up so that there is a reference chain from the Tenants to Assignments to Payments to Split Payment tables. I struggle a little bit with all of the different ways to reference data between tables.

Thanks for your help.

Marc_Dillon

Probably change the [person] to [assignment].[tenant]

https://help.appsheet.com/en/articles/1090811-dereference-expressions

BAP

Ok, I am so so close to getting this to work. I realized that I needed to base the PDF template off of my Contracts table and then get the relevant child and grandchild records referenced to it.

Below is how I have the table set up, the data it is pulling from, and the results in the "September" column. It seems to be pulling all of the relevant info I need for the one tenant, but it is repeating 350 for the three entries for the same tenant. I am having a hard time setting up a filter() or select() function that works.

<<Start:[Related Alpha Lists]>><<select(Alpha List[Paid],[Month]=”September”)>><<End>><<End>>

There is the function I came up with.

Thoughts?

Marc_Dillon

Why is there a 2nd start expression? And why does your select expression not compare against the current tenant from the 1st start expression? Take another look at my original example.