Line item rollup for specific months and clients

[vibhudawar]

I am working on a database, where I manage data of clients and their youtube channels. I am trying to develop a Finance report automation but for that facing challenges in setting up fields in my table.

Currently I have theses tables:

TABLE 1: YouTube Channels Table (attached to client table through client ID)

Fields are:
- Id (primary key, RECORDID())
- Name of the channel
- Channel id
- Client id (foreign key)

TABLE 2: YouTube Channel Earnings
- Id (primary key, RECORDID())
- Name of Channel (lookup field from YouTube Channels)
- Channel id (lookup field from YouTube Channels)
- client id (lookup field from YouTube Channels)
- Date (Date field)
- Month and Year (formula field, getting month and year from date field)
- YouTube earnings for particular Month (currency field)
- Tax Deduction (currency field)
- Net Earnings (formula field, Earnings - tax)

Now I have grouped the data first with the month and year, and then based on client id. Below is the table screenshot.
Youtube channel earnings table after grouping

Now here is my 3rd table: Finance Reports:
fields are:
- id (primary key, record_id)
- YouTube Channel Earnings (foreign key, linked to YouTube Channels Earnings Table)
- Total Earnings
- Client id (lookup field from youtube channel earnings)
- prepare report (checkbox)
- reports in docs form (url field)
- reports in PDF form (attachment form)

Finance Reports Table:

Now I finally want that, when I click on the prepare report checkbox, the finance report for a particular client for that particular month is created.

Here is the dummy report style I want:

 Channel Name: Mapped to particular client ie: White Gold Entertainment
Total Revenue: After adding all the revenue by individual channels.


So How do I make the total Earning column in Finance Reports Table, so that I can have the sum of revenue from all the youtube channels linked to a particular client id? Should I use Roll-up field, but how?

[TheTimeSavingCo]

Personally, I'd set up the "Finance Reports" table so that each record represented a single client <> month link and set up an automation to keep stuff in sync for me

To power that automation, I'd have a formula field in Youtube Channel Earnings that output the text of "[CLIENT ID] - [MONTH YEAR]" and the automation would just paste that formula field's output into the linked field to "Finance Reports"

The rollups should populate correctly after that, and you should be able to use that linked field to populate your reporting software of choice.  The Page Designer extension works, of course, but would need to be manually done per report

[vibhudawar]

Can you please explain the solution a bit in detail. Actually I am a beginner in Airtable and this solution seems a bit complicated. Therefore, it would be amazing if you could explain the solution in detail. (if possible, attaching some screenshots will be amazing).