Help

Re: Creating a “Monthly Billing” table

3668 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Blending_Produc
6 - Interface Innovator
6 - Interface Innovator

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.
Airtable Custos de Operação
Airtable Trabalhos

8 Replies 8

I covered how to do time-based reporting on last season’s Built On Air:

The base being demo’d is this one:

Since you appear to only need monthly reporting, you may not need the full solution described in the video but the general process is the same:

  • Use a DATETIME_FORMAT() formula to convert your dates into the name of the month (and presumably the year) to get 12/9/2021 to look like “December 2021”, or whatever your prefered format is
  • Use an Automation to copy the value of that formula into a LinkToAnotherRecord field that connects to your Reports table (this will work for future records, copy the formulas into the link fields for your existing records manually). Now you should have one row per month/year, each linked to one or more “Cost of Operation” records
  • Use Rollups to sum the value of your Gross and Net fields

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?

Airtable Cost of Operation
Airtable Reports

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!

Kamille, I thought of a solution.
I thought of naming the column “Operating Cost” as “REF Operating Cost”, then creating an automation to copy the values ​​from this column “REF Operating Cost” every month on the last day at 12am to a new column called “REF2 Cost of Operating Operation”, so I would create one more column with the formula:

IF({REF2 Operating Cost}=BLANK(),{REF Operating Cost},{REF2 Operating Cost})

That is, if the automation has already made a copy of the value that the column “REF Operation Cost” had on the last day of the month, use the copy value, if not, use the value of {REF Operation Cost}

This column with the formula would be called “Operating Cost” and would be visible, and I would use it to calculate the column “Monthly Net Billing”, the columns with the prefix REF would be hidden. What do you think?

If you think it works, I need help creating the automation, I tried to create an automation with the “At a scheduled time” trigger but I didn’t understand how to do it.

Thanks in advance!

Your solution is on the right track. What you probably need to do is use the “when record matches conditions trigger”, however. “At scheduled time” doesn’t associate with particular records, making it somewhat difficult to update one or more records.

If you have a formula field that returns “yes” when “today” (or a specific date field from the record) is the last day of the month, you can use that as the condition by which your Automation will trigger. That way the Automation know which record to do the copying in.

I can’t see the fields you’re using so the following is a guess, using your MMMM-YYYY format (i.e. “November-2021”)

IF(
   {Name of field showing month}, 
   IF(
      TODAY() = DATEADD(DATEADD(DATETIME_PARSE({Name of field showing month}, "MMMM-YYYY"), 1, "month"), -1, "day"), 
      "yes"
   )
)

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:
Reports

In this photo, I manually added the installments for the month of November in December, this way it shows the correct values:
Reports Manual Add

This is a picture of my “Operating Costs” table:
Airtable Costs of Operation

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.

I thought of a solution!
I thought of creating a column called “First day of the month” with a formula in the “Operating Costs” table that does the following: If today is the first day of the month and the Status is “ :heavy_dollar_sign: Installment” then “Yes”, if not, be “No”. Then I would create an automation in the table “Operating Costs” with the trigger “When record matchs conditions” with the condition that if the column “First day of the month” is “Yes”. If it is “Yes” the automation pastes MMMM-YY in the column “Month”.

I think that solves the problem, right? If so, could you please help me create the formula for the “First day of the month” column? I’m new to formulas, I understand a little bit, but I have difficulty when it involves more than one condition.

Thanks in advance!

PS: I found another problem, but I already thought of a solution too, I think I’m getting good at it! hahahaha

I realized that when the automation was going to copy the “MMMM-YY” field, it would delete the others, leaving only the current month, so I created a new column using the formula:

CONCATENATE({Month},", ",{(automation) Month})

This formula joins the months that are already in the “Month” column with the current month.

I believe that now all that is needed is the formula that I asked you to help create.

DATETIME_PARSE(DATETIME_FORMAT(TODAY(), "MM/YYYY"), "MM/YYYY")

The above gets you first of the month.

Hi Kamille!
Right! I would like help to build a formula with 2 conditionals, the idea is that it shows me “Yes” if it is the first day of the month and if the Status column is written “ :heavy_dollar_sign: Installment”. The idea is that the answer “Yes” is conditioned to these 2 things.

I think this formula you created does in parts what I need:

IF(
{Name of field showing month},
IF(
TODAY() = DATEADD(DATEADD(DATETIME_PARSE({Name of field showing month}, “MMMM-YYYY”), 1, “month”), -1, “day”),
“yes”
)
)

But I’m not sure, I have difficulty understanding formulas with 2 “IF”.

Can you help me with this please?

Thanks in advance!