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.
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?