Re: editable if

NCD · 11-15-2022 07:21 PM

What would be an expression for,

If all related grand child row's column Status is "Complete", Parent Table's column Status is editable.

SkrOYC

You have multiple ways to do the same thing inside AppSheet and this is obviously not the exception.

A "one expression" solution would be:

COUNT(
 SELECT(
  [Related child][Child_id],
  COUNT(
   SELECT(
    [Related grandchild][Grandchild_id],
    [Status]="Complete",
   )
  )=
  COUNT(
   [Related grandchild]
  )
 )
)=
COUNT(
 [Related child]
)

Basically meaning
The amount of existing related child records should be the same as the amount of related child record whose existing related child records is the same as the amount of child records whose status is "Complete"

Suvrutt_Gurjar

Since I was also working on this post with interesting requirement and as @SkrOYC very rightly mentioned there could be multiple ways to achieve the solution, just posting an alternative approach that makes use of system generated reverse reference columns.

1. Include a virtual column called say [Grandchild Status] in the child table with an expression something like

[Related Grandchildren][Status]

Where [Related Grandchildren] is the rev_ref column in the Child table and [Status] column is the status column in the grandchild table.

2. In the Editable_if of the Grandparent table's status column you could have an expression something like

ISBLANK(SPLIT([Related Children][Grandchild Status], ",")-LIST("Complete"))

[Related Children] is the rev_ref column in the grandparent table and [Grandchild Status] is the newly created VC in the child table in the step 1 above.

The expression could need some more tweaks to take care of initial editable if requirements depending on your app requirements of how to handle editing of initial status in the grandparent.

SkrOYC

I also though about this solution and I have a doubt (not to meant that your solution is worse in any case), the editable if is called while using the app instead of on sync so eventhough my expression is unefficient it shouldn't generate problems, right? I'm eager to read your take on it!

I hope we get chained list dereference in the near future, it would make things easier

Suvrutt_Gurjar

Hi @SkrOYC

As mentioned, I simply posted because I was also working on the requirement just about when you posted your response and my approach was different from yours. More the options better for anyone to choose from.

@SkrOYC wrote:

I hope we get chained list dereference in the near future, it would make things easier

I fully agree with you. The way we can pull a value of a column from a grandparent into a grandchild with a chained dereference , it will be great if we can do the reverse way, meaning getting the list of related grandchildren into the grandparent through a simple chained expression , it will be great.

@SkrOYC wrote:

I'm eager to read your take on it!

I think the expression I posted is just about using the available system generated columns, thereby making the expressions more compact and not necessarily making the sync time efficient. In this case as the expressions are used in constraints and not in VC etc. as you mentioned.

As you rightly mentioned, if reverse chained expressions were available , even in my suggested approach , there was no need to create an intermediate VC in the child table just to pull values from the grandchild.

Thank you very much for opening up the topic further as I believe such exchange of ideas help us all.

SkrOYC

@Suvrutt_Gurjar wrote:

More the options better for anyone to choose from.

Absolutely!

@Suvrutt_Gurjar wrote:

using the available system generated columns

I did the same actually so I expect those SELECT() to be efficient considering the lower amount of rows. I really like how SELECT() allows us to do that.

@Suvrutt_Gurjar wrote:

Thank you very much for opening up the topic further as I believe such exchange of ideas help us all.

More so when the ideas comes form an experience user like you 😃