Hopefully my title makes sense…
I am setting up a Drupal 9 website for internal use as a financial reporting engine and a CRM. I am making use of the following modules:
- Paragraphs
- Views
- Viewfield
- View Fields View
- Views Aggregator Plus
I have one Paragraph (Entity Reference) unlimited-value field on a paragraph type called “Project”. That field is called “Transactions”. It refers to a paragraph type called “Transaction”.
The “Transaction” paragraph type has the following fields:
- Transaction Amount (decimal field))
- Status (term reference field. Values are PAID and UNPAID)
- Paid On (date field)
- Year (a taxonomy entity reference field that contains pre-set years (2019, 2020, 2021, etc).
I have a content type called “Company”. It has the following fields:
- Title
- Project (a Paragraph/Entity Reference field)
- Transaction Amount – Paid (a Viewfield)
- Transaction Amount – Unpaid (a Viewfield)
- These two Viewfields are of content type “Company”
- One is filtered to the Transaction paragraph’s status “Paid”; the other is filtered to “Unpaid”.
- They contain this field, which is SUMmed through Views Aggregation: (field_transactions: Paragraph) SUM(Paragraph: Transaction Amount)
- They contain these relationships:
field_projects_paragraph: Paragraph
(field_projects_paragraph: Paragraph) field_transactions: Paragraph
(field_projects_paragraph: Paragraph) field_transactions
The output displays the sum of the paid or unpaid transactions. For example, if a company has a project which has 3 PAID transactions of $100 each, this view will output “$300”. The intent is to display this value on the “Overview of Transactions” view, as described below:
Overview of Transactions view…
I have created a View of “Company” content of display format: “Table with Aggregation Options” that will display a table of the following columns:
- Company Title
- Total Transaction Amount (a SUM aggregation of all transaction “Transaction Amount” values.
- Total Transaction Amount with status = PAID
- Total Transaction Amount with status = UNPAID
The intent is to display an at-a-glance table of all Companies on the site, showing a line for each Company, that Company’s Total Transaction amount (which includes paid and unpaid), that company’s PAID total amount, and that company’s UNPAID total amount.
Further, the intent is to be able to filter this table based on the “Year” taxonomy term value from the Transaction paragraph type (filter it to only contain data that is tagged with “2020”, for example), or to filter it by date range using the “Paid On” value from the Transaction paragraph type (filter it to only contain data that contains a date value of 01/01/2020 – 01/03/2020, for example). The purpose is to be able to see all dollar amounts per company on a yearly, monthly, weekly, daily, or any other date range basis. For example: “display all company transaction data for Jan 1, 2020 through March 31, 20020, and then run that same report for that same time period in 2021.
To the View, I added the following settings:
Relationships:
- field_projects_paragraph: Paragraph
- (field_projects_paragraph: Paragraph) field_transactions: Paragraph
Filter Criteria:
Content: Content type (= Company)
(field_transactions: Paragraph) Paragraph: Year (exposed)
(field_transactions: Paragraph) Paragraph: Paid On (exposed)
Fields:
Content: ID (ID) (hidden)
(field_transactions: Paragraph) Paragraph: ID (ID) (hidden)
Content: Title (Title)
(field_transactions: Paragraph) SUM(Paragraph: Transaction Amount) (Transaction Amount)
Content: Transaction Amount - Paid (Transaction Amount - Paid) - This is one of the Views Field View fields from the Company content type
Content: Transaction Amount - Unpaid (Transaction Amount - Unpaid) - This is one of the Views Field View fields from the Company content type
Format:Table with aggregation options
ID: Apply Group Function: Group and Compress
(field_transactions: Paragraph) Transaction Amount: Column Aggregation: Sum
My current end result is as follows:
- Each Company has its only single line in the table.
- The “Total
Transaction Amount” field correctly sums the company’s total
transactions (paid and unpaid), and the column successfully
aggregated the total transactions of all Companies on the site.
- The
“Content: Transaction Amount – Paid (Transaction Amount – Paid)”
field correctly displays the company’s total PAID transactions, but
it does not adhere to the Year and Paid On filters. Additionally, the
column will not SUM with the Table Aggregation settings at all.
- The
“Content: Transaction Amount – Unpaid (Transaction Amount – Paid)”
field correctly displays the company’s total UNPAID transactions, but
it does not adhere to the Year and Paid On filters. Additionally, the
column will not SUM with the Table Aggregation settings at all.
How can I display each company’s Paid and Unpaid transaction totals in this Views aggregated table, and also make those two values subject to the Year and Paid On filters?
I am open to completely re-thinking the organization of the node types, data, etc.