I have 2 tables. A Ledger journal tabele with fields like company, category, description, income, expence etc. The other company table with filelds like name, type, adress, etc. The type field is a choice field with values like lanlord, tenant, creditor, debitor, etc.
Now I want to make a summary view where i can see only tenants with their sums per quarter and under it the list of their transaction for the specified quarter.
I hope you understand my question. I would be thankful for any suggestions, descriptions or links that may help me achieve this.
Easiest way is to go to the field company (if thats the reference field) in the ledger journal table and underneath the details you can add extra fields. Add the field type.
Another way, same result is to add a formula field in ledgertable and enter the formula ledger.type.
After that you can xreate summaries on type.
I put together an example here:
The page “Tables” shows all the sample data. The page “Quarter Dashboard” shows a list of tenants – it’s just the list of companies filtered by Type of
Tenant. Linked to that is a summary of the Ledger table, grouped by Company, Quarter. So it shows the selected tenant’s sums per quarter. Just below that is the list of transactions for that tenant for that quarter.
I didn’t have to use the type of the company in the Ledger table here, because the selection is by company. But if you needed to use that, you can, as @hera suggested: in the Ledger table, ensure that
Company is of type Reference to Companies; then add a formula column with the formula
$Company.Type (I think @hera’s answer has a typo there).
To get quarters, I used the recipe from Recipe for getting Months and Quarters from dates, but AI Formula Assistant does a great a job of it too
If I misunderstood, or if something isn’t working, a screenshot could help understand what you are trying to get to.
Dear hera, dear Dimitry,
I am very thankful for your answers. Both worked well. For now I have used the suggestion of Dimitry.
You both saved my day.