Limit Rows in a child BUT be able to modify the last row

[JpChapron]

Hi,

I have implemented a Check to limit the number of Child Rows in a parent to 12.

It's working ish... but If I go to modify the the 12th child after I save it, I can't save anymore. I used data validity to check the amount of rows with this code :

or(COUNT(SELECT(Route[RDV ID],[RDV ID]=[_THISROW].[RDV ID]))<12,[RDV ID].[RDV_Status]="Fermé")

I'm checking if the report is Closed AND if the route is less than 12.

If I change the COUNT expression to +1, now I CAN modify the 12th. But I CAN also Add a 13th row.

I need to be able to Limit to 12 rows, but when the user Adds to 13, he can't save the 13th row

Any pointers ?

thanks

[MultiTech]

Are these records reference connected to anything?

[JpChapron]

Yes, let me change the code to make it clearer :

or(COUNT(SELECT(Child[Parent ID],[Parent ID]=[_THISROW].[Parent ID]))<12,[Parent ID].[Parent_Status]="Closed")

[dbaum]

You're in good hands with @MultiTech.

Meanwhile, FWIW, here's where I would have started. If this doesn't accomplish what you need, and you need to troubleshoot ongoing quirks around the current row being either new vs. edited, experiment with a combination of revising 12 to 11 and subtracting [_THIS].[Child ID] from the list returned by SELECT.

COUNT(
  SELECT(
    Child[Child ID], 
    [Parent ID] = [_THISROW].[Parent ID]
  )
) <= 12

[MultiTech]

Ex-nay on the brute force.

On the parent you've got a list of all the child records already there for you; count that list instead.

[dbaum]

Is this the technique you're referencing?

COUNT([Parent ID].[Related Child IDs])

[MultiTech · Accepted solution]

---

@dbaum wrote:

Is this the technique you're referencing?

COUNT([Parent ID].[Related Child IDs])

---

Yup! (^_^)

• That list you're trying to get, already exists on the parent
• It's that VC  on the parent that was automatically created for you by AppSheet when you created that REF column in the child
• It's literally the result of something like a SELECT(Child[Key], [Child_Parent_Ref] = [_thisrow].[ParentID]), but without the brute force.  It's done using other mechanisms that are much more efficient.

The thing you'll find, is most of the time that list already exists; it's just a matter of finding the connection (sometimes a connection chain) that connects them.

• Once you find the chain, then it's just grabbing values that already exist somewhere and doing something with them. 🙂

In fact... in those instances where you feel you'd have to resort to a SELECT() - you could probly take two lists you already have, or lists you could make really easily with a list dereference, and running them through an INTERSECT() to find the common elements between them.

If you think about THAT... it's not looking anything up; it's taking two lists and simply finding the ones that are in both lists.  That's GOTTA be much easier on the system. 😋

• It's just a matter of finding the lists, connecting them in some way, and doing whatever you need with them to get your resultant lists.

----------------------------------------------------------------------------------------------------------------

Sounds simple right!?

Me at 2am....

[JpChapron]

Hi ! sorry about the week long wait, I've accepted your answer, thanks SO MUCH for all these info

Now, for how I solved my particular problem, which was : Can't modify the 12th child. here's how I proceeded :

I've created a Virtual Collumn that has this formula :

COUNT([Parent ID].[Child ID])+1

And in my PHYSICAL value that I want to trigger, I've added this to "Valid_If"

IF([Virtual_Num_Collumn]>12,false,true)

So what It does it it checks the related child items +1, and if it is equal to 13, you can't save the form. But if it's equal to 12, well now you can.

Thank you all !