Updating "Next" Child Record

[Ryan_Mortimer]

Hello Appsheet thinktank!

I have another “this may be another case of this is so simple I don’t realise it”, however:

I am hoping to make an automated data change/automation/ where:

1. A user “Completes” child record (by setting the “STATUS” to "COMPLETED)
2. On the same row - [ACTIVE LEG] to be changed to “FALSE”
3. On the “NEXT” child record (determined by [LEG NUMBER]), receives a data change (setting the “ACTIVE LEG” column to “TRUE”)

My difficulty with this is that there is not a pre-determined number of “LEGS” or “CHILD” records (there may be any from 1 to 5 rows)

So far, I have tried using SELECT(), ANY(SELECT)) and even DeReference’s in both the action and within virtual columns. I have encountered a range of issues, but still can’t seem to get this working.

To be clear, this is not a case of “When an update is made in the parent table, the child record is updated” it is purely between related child records that are in a specific order.

As always, thank you in advance and more than happy to provide some data structure examples.

[Aurelien]

Hi @Ryan_Mortimer

your case looks interesting.
Did you have a look to this post ? the section about PENULTIMATE may inspire you an idea ?

FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() Tips & Tricks 💡

Nope. Try: INDEX( ORDERBY( FILTER( "My Table", ([_THISROW].[Thing] = [Thing]) ), [_ROWNUMBER], TRUE ), 2 )

[Ryan_Mortimer]

I have just managed to find the time now to sit down and read through this.

Thank you for the link! I will try and make sense of this and see if I can get an answer before anybody else chimes in.

[Ryan_Mortimer]

So, based on the expressions that I was tinkering with and the information in the above link:

I have worked out how to reliably select the next “LEG” (by this, I mean returning as a ref instead of a LIST)

ANY(SELECT(LEGS[LEG ID], (AND([_THISROW].[JOB ID] = [JOB ID],[LEG NUMBER] = [_THISROW].[LEG NUMBER] + 1))))

Interestingly enough, when reversing the last half of the AND( expression, it actually worked in reverse by selecting the previous “LEG”

Expression that resulted in an error

ANY( SELECT(LEGS[LEG ID], ( AND( [_THISROW].[JOB ID] = [JOB ID], [_THISROW].[LEG NUMBER] = [LEG NUMBER] + 1))))

Interestingly enough, I worked this out on an iPhone so I will have to try to work towards the action portion of this conundrum in the morning.

[Steve]

Ryan_Mortimer:

1. A user “Completes” child record (by setting the “STATUS” to "COMPLETED)
2. On the same row - [ACTIVE LEG] to be changed to “FALSE”
3. On the “NEXT” child record (determined by [LEG NUMBER]), receives a data change (setting the “ACTIVE LEG” column to “TRUE”)

I imagine using a set of actions for this; I see no reason to use automation.

Steps (1) and (2) can be combined into a single action that the user triggers by pressing a button. Try getting that working, then we can consider step (3).

[Ryan_Mortimer]

Hey @Steve !

Thanks for your reply!

I did toy with the idea of actions, but I was struggling with the expression to return the value as a reference to then de-reference the action of setting the next “leg” to “active”.

I have set the action up for step 1 & 2 (that was the easy part), now I am a little stuck on the third step