How to Iterate Over Referenced Rows in a Column and Perform Calculations in AppSheet

[Laye1]

I’m working on an AppSheet application with the following setup:

• Tables:

  1. Steps Table: Contains a column [Milestone] which references rows from the To Do table.
  2. To Do Table: Contains the columns [Status], [Due], and [Date/Time].

• Goal:
  For each row in the To Do table referenced by the [Milestone] column in the Steps table, I want to:

  • Check if [Status] = "Done".
  • If Done, calculate [Due] - [Date/Time].
  • Otherwise, calculate [Due] - NOW().
  • And Return the value in a in virtual column [completed_to-do_task_duration] which will serve as Chart column
• Challenge:
  My current attempts return same value and fail to iterate over the rows in the referenced To Do table. I need to iterate over all the rows referenced by [Milestone] and perform the calculation for each row.
  Current Expression:  if ([Milestone].[Status]="Done",[Milestone].[Due]-[Milestone].[Date/Time],[Milestone].[Due]-NOW())
•  
• Re