Group Field formula

[Currently_Jason]

Hi,

Hope someone can help. My head is bursting.

I have created a childcare system in Airtable. Everything was based on hourly costs so all calculations from payments were easy, number of hrs * hourly rate.

I’m now told that there is a monthly deduction.

Other than using grouping to show monthly costs, I have no way of adding “Monthly” as everything is a rolling total of hrs

How can I do this ? is it possible to modify the SUM on the grouping to add field ?

Any help appreciated

Jason

[Currently_Jason]

Let me try and add some context to see if I can get some help. Maybe I’m being too vague

I have a table called “Bookings” each Row is a booking. The booking row has the following key columns:

Hrs - Calculated from a Start Time and End Time)
Rate Per Hr (Taken from a link table)
Total - Hrs * Rate

The billing is totaled Monthly with a sub total of weekly. This was easily done by using Month and Weeknum formulas to get the information into columns and then Grouped by Month and Week. The Group Sum did the rest.

Now I’m informed that there are Funded Hours, which means some pupils are funded for certain hours and that this is deducted from the Monthly total. being as I don’t have a monthly total other than what the grouping gives me, I’m not sure the best way of doing this.

I can thing of a way but it’s messy

Any idea’s ?

Cheers All

[Justin_Barrett]

There are a couple ways I can see this being done, both of which have some element of messiness involved.

The first keeps everything in a single table, but is slightly messy from a data structure standpoint. Long story short, add new dummy bookings to each week/month that have a specific name like “FUNDED HOURS”, setting the hours appropriately for how much is funded for that week/month. In your {Total} formula field, look for this name and multiply the normal calculation by -1, which will reduce the weekly and monthly totals appropriately. Something like this:

IF(Name="FUNDED HOURS", -1, 1) * Hours * Rate

A much more complex setup involves adding more tables for tracking the funded hours per month per pupil, more linking, summarizing in a table other than [Bookings], etc. A bit “cleaner” from a data structure standpoint, but more complex to use, though some aspects of its usage might be made easier using automation (Zapier/Integromat).

[Currently_Jason · Accepted solution]

Firstly, thanks for the detailed reply Justin. Much appreciated.

Both are good, workable solutions. I had already gone down the Integromat route of moving the data into a new table but the first solution sounds like a better option and gave me another idea.

I could have a status of each booking that can be changed to “Funded” and zero’s the cost. I’ll have to look at how I manage the depletion of the total funded hours but is sounds easy enough. Failing that I’ll automate a “Dummy” entry with the funded hrs value as you said

I might also continue with the Integromat option too as a kind of month end procedure where I flatten the data and make it static for reporting and archiving .

Thanks again