Skip to main content

Sum up durations (rollup) from looked up data (from multiple records) into one cell

  • December 4, 2023
  • 14 replies
  • 79 views
O
Forum|alt.badge.img+5

Hello, i'my trying to compare efficiency of real-time project durations to theoric template durations. 

Projects are linked to tasks and tasks templates ;

-> total duration of a project = sum of the duration of each task

-> duration objective (efficiency) of a project = sum of the task duration objectives

In each project, Im trying to lookup the tasks theoric duration and sum it up to compare it to the actual project duration. 

I'm doing this this way : 
- I link projects to tasks

- Each task is linked to a ''task template''

- I look up the ''theoric duration'' of each task and Sum it up in rollup

The problem is that the sumup calculation is incorrect : see screenshot ->

- the first row, lookup values for theoric durations per project are as follow : 10, 10, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8

=> And the calculated Sum is 20...

Does anyone encounters problem with this kind of sumup ?

Thanks for your help

 

    14 replies

    Databaser
    Forum|alt.badge.img+25
    • Databaser
    • Brainy
    • 868 replies
    • December 4, 2023

    What field type are you using for your durations? Duration? Number? Text? 

    Jan - Databaser
    S
    Forum|alt.badge.img+21
    • Sho
    • Inspiring
    • 560 replies
    • December 5, 2023

    Maybe some filter is enabled? 
    Scroll down here.

    O
    Forum|alt.badge.img+5
    • oVo_Team
    • Author
    • Known Participant
    • 13 replies
    • December 6, 2023

    What field type are you using for your durations? Duration? Number? Text? 


    Hello, thanks very much for your time and help. It's a lookup field that looks up a number field. 

    O
    Forum|alt.badge.img+5
    • oVo_Team
    • Author
    • Known Participant
    • 13 replies
    • December 6, 2023

    Maybe some filter is enabled? 
    Scroll down here.


    Hello !

    No its not the case, the result is the same with a formula SUM field. any other idea ?

    Databaser
    Forum|alt.badge.img+25
    • Databaser
    • Brainy
    • 868 replies
    • December 6, 2023

    Hello, thanks very much for your time and help. It's a lookup field that looks up a number field. 


    Did you try using a rollup field on your project records to look at the numbers in your rollup field on your tasks records? That should work. 

    Jan - Databaser
    O
    Forum|alt.badge.img+5
    • oVo_Team
    • Author
    • Known Participant
    • 13 replies
    • December 7, 2023

    Did you try using a rollup field on your project records to look at the numbers in your rollup field on your tasks records? That should work. 


    Hello, not sure if i understood correctly, see screenshot of the rollup formatting : 

    - Table projects (Routes) : rollup of the lookup fields of field ''objectifs temps' in table tasks (visits)

    - Table tasks (visits) : lookup from a linked field (order_ID) to get the ''objectif temps''

     

     

    S
    Forum|alt.badge.img+21
    • Sho
    • Inspiring
    • 560 replies
    • December 7, 2023

    "Objectif temps(min)/adresse" is a string, which may be the reason why the total fails.
    If it is a number, it will be displayed on the right side of the cell.

    How about changing the Lookup field to a rollup field and using the formula Value(values) to convert the values to a number field?

    Databaser
    Forum|alt.badge.img+25
    • Databaser
    • Brainy
    • 868 replies
    • December 7, 2023

    Try using a rollup instead of a lookup in your table tasks.

    Jan - Databaser
    O
    Forum|alt.badge.img+5
    • oVo_Team
    • Author
    • Known Participant
    • 13 replies
    • December 7, 2023

    "Objectif temps(min)/adresse" is a string, which may be the reason why the total fails.
    If it is a number, it will be displayed on the right side of the cell.

    How about changing the Lookup field to a rollup field and using the formula Value(values) to convert the values to a number field?


    Hello !

    Thanks for the suggestion, just tried it but it returns ERROR message, see screenshot

    O
    Forum|alt.badge.img+5
    • oVo_Team
    • Author
    • Known Participant
    • 13 replies
    • December 7, 2023

    Try using a rollup instead of a lookup in your table tasks.


     Tried so but still not working 😕😕 It seems the issues comes from the fact that the original field is a lookup (rollup from a lookup). Is it possible that a number data stored in a lookup field changes to a string ? How to change it back to a number ?

    S
    Forum|alt.badge.img+21
    • Sho
    • Inspiring
    • 560 replies
    • December 7, 2023

    Oops, value(values) is an error.
    Try sum(values)

    Databaser
    Forum|alt.badge.img+25
    • Databaser
    • Brainy
    • 868 replies
    • December 7, 2023

     Tried so but still not working 😕😕 It seems the issues comes from the fact that the original field is a lookup (rollup from a lookup). Is it possible that a number data stored in a lookup field changes to a string ? How to change it back to a number ?


    By using a rollup field instead of a lookup field for that original field. 

    Jan - Databaser
    O
    Forum|alt.badge.img+5
    • oVo_Team
    • Author
    • Known Participant
    • 13 replies
    • December 7, 2023

    Oops, value(values) is an error.
    Try sum(values)


    Oh OK guys i think i almost got it ! it seems airtable rollup field switched to 0 the data when the lookup field has multiple values, see screenshot attached 

    Lookup = 8, 8 ===> Rollup = 0
    Lookup = 10 ====> Rollup = 10
    Lookup = empty ====> Rollup = 0
    Considering this, how do you thin i should solve this ?
    S
    Forum|alt.badge.img+21
    • Sho
    • Inspiring
    • 560 replies
    • December 7, 2023

    @Databaser is right, the original Lookup field also needs to be a Rollup field.


    Terms & ConditionsAccessibility statement