Solved: Get sum of other items value in same table - Page 2

Aurelien · 02-19-2021 09:32 AM

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:

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

Steve

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.

View solution in original post