Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Subtracting multiple fields from a total

Topic Labels: Formulas
434 1
Showing results for 
Search instead for 
Did you mean: 

Here’s one for you.
We have a purchase order for a job, with a total amount in one table.

We have multiple work shifts each be a varied amount depending on hours and rates of pay in another table.

I’d like to be able to summarise all shifts and subtract them from the total purchase order to check we are under budget.

Is this possible?

1 Reply 1

Yes, this is possible, assuming that everything is joined with linked records.

I am picturing the following tables:

  • Job/Purchase Order (linked to Shifts)
  • Shifts (linked to Job/Purchase Order and Pay Rate)
  • Pay Rates
  1. In the Shift table, rollup the Pay Rate.
  2. Have the Shift calculate its amount based on its hours and pay rate.
  3. In the Job/Purchase Order, rollup the shift amounts. (SUM(values))
  4. In the Job/Purchase Order, subtract the rolledup shift amounts from the total.