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
  

[RBTAndrade]

Hi @Laye1 , I undestand that Step Table is the parent and To Do Table is the child, where Step Table key is [Milestone] and also exists in To Do Table as a Ref type field, correct? 

If so, the expression IF([Milestone].[Status]="Done",[Due]-[Date/Time],[Due]-NOW()) used in a "virtual" To Do Table field will work.

Once [Due] and [Date/Time] are To Do Table fields, I didn't understand why you referenced them with [Milestone].

[Laye1]

Hi @RBTAndrade , Thank you for your reply. The virtual column is from Step table, hence [Due] and [Date/Time] are unknown columns, unless they're referenced. 
The expression is used in Step table not "To do " table

[RBTAndrade]

Hi @Laye1 , if I understood well, Step is the parent table and To_Do is the child table. So, To_Do table must have a Ref column that references Step key column.

So, you need a virtual column in Step table that references all child records with the expression to calculate durations (it will be a LIST column). In this case, first you create a virtual column with the expression in To_Do table. Second, you create a virtual column in Step table that references this virtual column from To_Do table, like this:

a) Virtual column "Duration" in To_Do table: IF([Status]="Done",[Due]-[Date/Time],[Due]-NOW())

b) Virtual column "Durations" in Step table: To_Do[Duration]

For me, It's not clear when you say that you need to iterate over child records. If you need more help, please describe all fields from Step and To_Do tables.