May 14, 2023 02:28 PM
Hi there! Hoping someone can shed some insight!
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:
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:
Thanks for any and all help!
May 15, 2023 12:49 AM
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
May 17, 2023 09:54 AM - edited May 17, 2023 09:56 AM
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
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!