Re: Creating a “Monthly Billing” table
Hello guys!
I would like help creating a “Monthly Billing” table.
The question is: I have a table called “Jobs” where I add all the jobs that my clients ask me for, this table contains several columns, including a “month/year”, and also a column referring to the value of each job requested. Now I would like to create a table referring to the “Monthly Billing”, containing the “Gross Monthly Billing” and the “Net Monthly Billing”.
In “Monthly Gross Billing” I would like it to be the sum of the value of all jobs referring to month “X” in the “Jobs” table and in “Monthly Net Billing” I would like it to be {Monthly Gross Billing} - all values from column “Cost/Month” of a third table called “Cost of Operation”.
It would be very important that this table called “Monthly Billing” could be organized by Month.
I’m new to Airtable so I’m still learning how it works, thanks so much for all the help.
I put some pictures of my tables to make it easier to understand.
Perfect, Kamille!
With your suggestions I managed to make it work, the only problem now is that I started to analyze and realized that I cannot use the “Cost/month” column to calculate the Net Billing, this column was created just so I could have predictability of all expenses monthly and uses this formula:
IF({(REF) Installment}<{Total Installments},{(REF) Installment Value},0)
As such, it will always be changing, so if I use it to calculate Net Billing, I will lose month-to-month Net Billing history. I had thought of using the “Value of installments” column to make this calculation, but I saw that it wouldn’t work either, since that way the calculation will only count the value once, not counting the installments in effect every month.
What would be the best way for me to resolve this situation?
In the “Reports” table the rollup is currently set to get the “Installment value” from the Cost of Operation table, so notice that in the sum it is not taking into account the 2 November items that are installment purchases… solving this, I believe that everything works.
Thank you very much for your attention and patience, Kamille!
Thank you so much for your help, Kamille!
Your solution is very smart!!
But I noticed another problem, I’m already pretty confused, I don’t know if there’s a way to make the table work exactly as I need it, but if you have any ideas, I’ll be very grateful.
The problem is this: I hadn’t paid attention to the fact that Rollup will only add up what is in the Operating Costs column… so it still doesn’t consider the amounts of installments from previous months… below is a photo of how is it:
In this photo, I manually added the installments for the month of November in December, this way it shows the correct values:
This is a picture of my “Operating Costs” table:
As you can see, installment payments have the status “ :heavy_dollar_sign: Installment” and those that have already been paid have the status “ :heavy_check_mark: Paid”. I would need that whenever a new month line was created in the Monthly Billing table, everything that has the Status “ :heavy_dollar_sign: Installment” in the Operation Costs table is also added in this new month line.
