How do I print the number of consecutive days in the child table to the main table

[Pointer]

Hi,

I have two tables as I have shown below, one is a habit table and the other is a child table,

I want to print the latest serial number of days to the virtual column in the habit table.  (sample 19.03, 20.03, 21.03     3 days )

What should be the VC formula?

Habit Table     

ID    Habit Name     Virtual C.(  number of consecutive days)

1         Habit 1               3

2         .

3         .

Record Table

ID  Habit ID    Habit Name           Date                 Goal

1           1                 Habit 1            21.03.2023              Yes

2          1                 Habit 1            20.03.2023            Yes

3          1                 Habit 1            19.03.2023              Yes

4          1                Habit 1            18.03.2023             No

5          1                 Habit 1            16.03.2023            Yes

6          1                 Habit 1            15.03.2023            Yes

7           1                 Habit 1            14.03.2023           No

[Marc_Dillon · Accepted solution]

That's a rough ask for Appsheet. Here's one way, not promising that it's the best way though.

I would start by adding a new column to the record table that is a Ref to the previous record.

ANY(FILTER( record , AND( [habit]=[_THISROW].[habit] , [date] < [_THISROW].[date] ) ) )

Then another new column to count of the number of consecutive days.

IF( [newcol1].[date] = [date]-1 , [newcol1].[newcol2] + 1 , 1 )

Then back in your parent table, add a new column to Ref to the latest child record

ANY( ORDERBY( [related records][key col] , [date] , descending ) )

And yet another column to pull the consecutive days value from the latest record

[latest_record].[newcol2]

[Pointer]

After newcol2 I get the error "Unable to fetch app definition. Ran out of resources evaluating: IF"

[Marc_Dillon]

Wow, do you have a lot of data? The first 2 columns can be real.

[Pointer]

No, there is not much data, the main table has 15 rows, the record table has 45 rows.

[Pointer]

No problem when using real column, also

ANY(FILTER( record , AND( [habit]=[_THISROW].[habit] , [date] < [_THISROW].[date] ) ) ),

ANY( ORDERBY( [related records][key col] , [date] , descending ) )

I used maxrow instead, thank you for showing the way.

[Marc_Dillon]

MAXROW is fine for the first of those 2, but I would stick to my expression for the 2nd. MAXROW must search the entire table, my expression only searches the related records.

[Pointer]

I did as you said. "first expression maxrow" second expression is much faster sync as you suggested. Thanks

[Joseph_Seddik]

Have a look here, it might help..

Target Workday Calculator - Google Cloud Community

[JPAlpano]

Search for @MultiTech 's post on looping.  He also has a video in his Youtube channel specific to this.

[Pointer]

Can you share the link? @JPAlpano  Thanks.