Get sum of other items value in same table

AppSheet AppSheet Q&A
1

Hi there

I’m working on a schedule tracker, and I need to calculate which duration can I set on a new entry. Basically, I’m allowed to work 8:12 per day.

I used parent/child tables, and in the child table “Detail_Day” I’m using this formula:

> List_OtherChilds=> SELECT(> Detail_Day[key_detail_day],> AND(> [Related_Key_Detail]=[_THISROW].[Related_Key_Detail],> [key_detail_day]<>[_THISROW].[key_detail_day]> )> )> > > > CALCULATION_otherDetails_Duration_VC=> SUM(> [List_OtherChilds][TotalTime_Detail_VC]> )> >

Where [TotalTime_Detail_VC] is a sum of various durations
And in initial value, I use the formula:

> workTime=> "08:12:00"-[CALCULATION_otherDetails_Duration_VC]> >

Each formula, individually, seems to work properly.

When I sync the app, I get this error:

Expression appears to be part of a cycle[…]

I tried something else, based on excellent article from @Steve:

[FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT()](https://community.appsheet.com/t/faq-filter-lookup-maxrow-minrow-ref-rows-and-select/24216/9) Tips & Tricks ?

How do I do SUMIF() or SUMIFS()? SUM( SELECT( Orders[Total Amount], AND( ([Customer] = [_THISROW].[Customer]), ([Order Date] >= [_THISROW].[Order Date]) ) ) ) Use SELECT() to gather values from a single column only. See also: AND(), SUM()

I think it summarizes what I tried earlier in two separate steps:

> SUM(> SELECT(> Detail_Day[TotalTime_Detail_VC],> AND(> [key_detail_Day] <> [_THISROW].[key_detail_Day],> [Related_Key_Detail] = [_THISROW].[Related_Key_Detail]> )> )> >

)

Anyway, I still have the cycle-error message.
I think I get the point, but I have no idea about how to get it working ? Any idea ?

(A second trouble I will probably have is matching duration vs time, but I will deal with it in a second time)

Many thanks in advance

2

This?

(
  "008:12:00"
  - SUM(
    SELECT(
      Detail_Day[duration-column],
      AND(
        ([Related_Key_Detail] = [_THISROW].[Related_Key_Detail]),
        NOT([_ROWNUMBER] = [_THISROW].[_ROWNUMBER])
      )
    )
  )
)

replacing duration-column with the name of the column in the Detail_Day table that contains the duration associated with each row.

3 Likes
3

This.
Is.
Extraordinary.

Thanks a lot !

3X_d_3_d39e62ae43cb261cfc2303d4e7c1cd0f7c29e6be.gif

1 Like
4

I just noticed some interesting point: if I try to separate your formula in pieces, as I did before, I get the cycle formula error, but not when everything is in the same calculation.
That’s curious…

5

If column 1 computes its value using the value of column 2, and column 2 computes its value using the value of column 1, you get a cycle, because each column requires the other to have a value before it can compute its own. That’s what you’re running into.

2 Likes
6

Ow, said this way it’s ridiculously simple

Thanks for this explanation !

2 Likes