Skip to main content

Send Finalized Record total for month to New Table

  • May 14, 2023
  • 2 replies
  • 20 views
K
Forum|alt.badge.img+2

Hi there! Hoping someone can shed some insight!

Context:

My primary table tracks billing for the month for multiple clients.  The total amount billed for each client is calculated from a reference table based on the current services each client is using. These totalare  updated each month and then sent to our billing department for invoicing. For simplicity sake, fields are:

  • Field 1: Company
  • Field 2: Total Current Monthly Charges

Goal: 

I would like to send the total current monthly charges for that month for each client to a separate table so I have a record of what each client was billed that month in order to track month over month revenue and have a historical record. 

I'm weary of a date specific automation as billing finalization date can vary.  

Ideal End Result:

1. Finalize totals for the month

2. Click button that says "Finalize" for a record

3. A new record is created in a separate table with the following info:

  • Field 1: Record Name ("Client" & "Sign Off Date")
  • Field 2: Client Name 
  • Field 3: Date Record was created
  • Field 4: Monthly Billing Total 

Thanks for any and all help! 

 

 

2 replies

TheTimeSavingCo
Forum|alt.badge.img+31

Hmm, you could do this with an automation that runs whenever a bill has a date and a client assigned to it instead if you'd like?

Try the following:
1. Each bill has a date I assume?  If so, create a formula field that will output the month-year of that bill via `DATETIME_FORMAT()`
2. Create another formula field that will combine the client's name with the month-year output from the previous field (You can combine these two formula fields if you'd like)
3. Create the new summary table you mentioned and create a link to your current table
4. Have an automation that triggers when a bill has a date and a client assigned, and its action will be to:
  - Update the triggering record by pasting the value from the field in step 2 into the linked field to the summary table

In your summary table, create a rollup field that will sum up the billing amount

This will give you a table where each record represents a single month for a single client, and will automatically update whenever new bills are added to your original table

Adam, Airtable Consultant - thetimesaving.company
K
K
Forum|alt.badge.img+2
  • KyleLaCour
  • Author
  • New Participant
  • May 17, 2023

Thank you Adam @TheTimeSavingCo ! 

You've got it with the end result I'm looking for but one caveat - the same bill is updated each month for the next month's sign-off, so there is no new record for the bill - it's a living ledger of services our clients are using and will need to be invoiced for.  Here is a snapshot and more on the process :

1. On the sign-off date listed, I audit each client to ensure we have accurate information for what they need to be invoiced for for the upcoming month. 

2. Once confirmed, these are sent to accounting to invoice each client 

3. Following the sign-off, Monthly Billing Totals may be updated for the following months invoices. 

4. So ideally, I would have a "Sign-off" or "Store Value" button or automation that generates a record on a separate table showing the fields

  • Record Name: "Client - Date"
  • Client Name: "Client 1" 
  • Invoice Month: "July 2023" - format's not important, just showing what the period we're referencing
  • Total Billed: "$xxxx" - the amount they were billed that month. 

These fields would not be impacted if I make updates to the table you see above, but just store the values at the time of sign-off.

Ah, just realized I typed that in my initial question - good to know I'm chasing the same goal! 

 

Terms & ConditionsAccessibility statement