Sum fields from different table


#1

Hey,

I’m having a task table with a worker ID and a calculated task run time (with datetime dif) on one table and want to sum all entries from a worker summed up.

I know how to use Lookup but how do I calculate a sum from a field from all rows?

Thanks,
Frank


#2

Hi Helmi, this is a great question! It sounds like you have a few options:

A) Use a rollup field to calculate the sum of a field for all linked records
B) Group records by worker, and use the group’s summary bar to calculate the sum of a field
C) Create a filtered view of only those records associated with a given worker and use the summary bar

Hope this helps! If I’m misunderstanding your setup and what you’re looking to accomplish just let me know :slight_smile:


#3

Hey @Victoria_Hay,

thanks for replying.

I’m unsure about all your solutions. I try to be a bit more precise with another example. Here’s my situation with two tables:

  1. Tools - contains tools
  2. Job Log - contains jobs while for each job one tool is used. Also contains the time the job takes

What I now want is to sum up the time that each tool was used in jobs and display it right within the record on the Tools table.

As I understand the rollup field doesn’t have that filter where I can only select the entries where the tool is used but can rollup everything from the Job log table. Is that right? Also the Rollup doesn’t seem to able to add up values that were created by DATETIME_DIFF.

Grouping is nice but isn’t exactly what I want as well as a filtered view.


#5

It can be done with a rollup (and a sum) as it will only grab the linked records. Not sure about the date format though, but you can always convert the time to minutes or something using: DATETIME_DIFF([date1], [date2], ‘units’) - where units is ‘m’ in this case.