Limit the number of child entries

Will_Henry
Participant I

Is there a way to limit the number of entries in a child table? Is there a way to remove the ‘New’ button from the parent table when this limit is reached?

Solved Solved
2 35 5,023
1 ACCEPTED SOLUTION

LeventK
Participant V

@Will_Henry
You can use below expression in your child table’s Are updates allowed property. Let’s assume that you want to limit the records by 50:

IF(
COUNT(SELECT(TableName[KeyColumn],TRUE))<=50,"ADDS_AND_UPDATES","UPDATES_ONLY"
)

View solution in original post

35 REPLIES 35

LeventK
Participant V

@Will_Henry
You can use below expression in your child table’s Are updates allowed property. Let’s assume that you want to limit the records by 50:

IF(
COUNT(SELECT(TableName[KeyColumn],TRUE))<=50,"ADDS_AND_UPDATES","UPDATES_ONLY"
)

thanks

The fields become disabled,  its not allowing me to update the existing entry.

Will_Henry
Participant I

Thank you!

You’re welcome

Hey @LeventK

If you don’t mind, is there a way to limit one child entry per parent entry?

@Jeremy_F
Sure. Your question is same than @Will_Henry 's actually. Just set it to <=1 basically if that’s what you’re asking.

@LeventK I believe @Jeremy_F is meaning one child record per parent record.

One way simple way is if you use the Parent record’s key column as a key column for your child record (with initial value). Then it’s not possible to save with the same key value.

It works but, it's not hiding the New button

Jeremy_F
Participant V

This won’t remove the “add” button, though it prevents more than one child record per parent record

I want to allow only one child record per parent record, which means removing the “add” button once I add one child record

Then you would need to add a slice (read_only) and create the second Ref_Rows virtual list for that slice. Hide the original virtual list column when COUNT([Related child records]=1 and show this additional virtual list column. It will hide that “New” button.

@Jeremy_F there are three ways you can think of solving your problem:

  1. Dynamic control of the table update mode. This is the solution @LeventK proposed. When you do this, the app will remove the New button automatically. This also applies to changes made via workflow rules or REST API. So it is a very logical change.
  2. Control of the display of the New action. This is an option in the definition of the Add action and just controls whether the action is shown or not. You can use the same expression there. This is more fragile — it does not apply to changes in the workflow rules or API.
  3. Validation constraints on the data. @Aleksi originally proposed one solution in this category. The user doesn’t see any difference, they can make changes, but when there is an attempt to save or sync the data, it will fail. This is generally sensible for exception cases rather than for mainstream cases. As you pointed out, this is still confusing for the user.

Of these three, option #1 is the best for you. However, there is a limitation in AppSheet today. The condition specified in the table update mode cannot be different per row. So you should probably go with option #2. In the Add action definition for the child table, the Show_If condition would be something like this: COUNT(SELECT(ParentTable[KeyColumn], [KeyColumnd] = [_THISROW].[ParentRefColumn])) = 0

@Aleksi also proposed a different UI-based way of achieving the same outcome — more directly controlling the UI shown by using a different virtual column. This is a somewhat indirect way to achieve the outcome though, so I’d suggest try my approach first to see if it will work for you.

Thanks for the suggestions. I already implemented the suggestion by @Aleksi,and it works perfectly, as the actions apply on per row basis

I am triying to implement your number 2 solution, using the add action definition but is not working, can you elaborate more detail

This does not seem to remove Add from the inline view of the child entries, in the parent detail view

If you don’t need the action to show anywhere else, you can simply set it to “Do not display” and it drops off.

That is not conditional on the number of child rows

Im taking different approach when i want to limit the number of child row to be added to one parent.

First, to make the inline child view set to parent table out of slice which is update, delete only (not able to add.)

Make a virtual column to parent table to dynamically calculate the number of child rows.

Make action attahced to parent table, which will open the form to add to it child table.

The action button is made visible only when this Virtula column value is less than and equal N.

For this quick demo, i set app that the user can add max 3 rows to child per parent record.

hi Koichi_Tsuji .. can u share your sample app?

Looking for the following help

The below issues are all in the Form View.

Im having this exact issue right now. Has anyone figured out how to limit the amount of entries of a child based on a column in the parent? All the while still allowing edits of the child… just not adds. So the Uses would still be able to click on a child entry they added in the form and delete it/edit it, but if they have reached the max limit not be able to add… ?

Anyone have any ideas/implementation on the above?

I think you’d have to hide the system-generated Add action and use your own (of type App: go to another view within this app), giving your own the desired intelligence. The downside of this approach is your own wouldn’t display an Add button in the inline display, but could show an overlay in the View display. You’d probably want to add an overlay or prominent button for your Add action on the detail view.

Another option could be create two slices with filter condition as TRUE on the child table. Slices called say ChildUpdate ( with Update and Delete permissions in slice settings) and ChildAdd ( with Add, update and delete permissions in slice settings)

Create two VCs in the parent such as

  1. VC 1 called say [ChildAdd] and with expression

REF_ROWS(“ChildAdd”, “Parent Key”)

and Show_if constraint as COUNT([_this])<=([Child Table1 Max Records]-1)

  1. VC 2 called say [ChildUpdate] and with expression

REF_ROWS(“ChildUpdate”, “Parent Key”)

and Show_if constraint as COUNT([_this])>=[Child Table1 Max Records]

3)[Child Table1 Max Records] is the column in parent table that denotes the number of max child records

The child table inline view will switch from add slice to update slice once the number of records are added as mentioned in [Child Table1 Max Records] are added.

So I cant get the child table Inline view with just update and delete permissions to show on the form, even if I have it always showing?

No Matter what this will not show.

I have it so it removed the inline table view when the max entries has been entered, but would still like the user to be able to edit/delete entries. If entries are deleted than re-add others…

I dont believe the above method works,
Have you tested this?

Hi @Jonathan_S,

Yes, I had tested the suggestion before suggesting. It is possible that at times there could be misunderstanding in requirement from my side. However, I humbly mention that I always test any major suggestion beforehand.

I believe your requirement is exactly similar to the post below. In that post , the child records slices are made to appear with add / update option by using [Order Status] column from parent record in show_if of the add or update slices. In your case it is show_if based on [Child Table1 Max Records] as suggested above.

Please refer the sample app in that post created by @ZhipengY

Yes what your saying is correct for everything except for child views in forms. In the form itself this does not seem to work.

You are correct about inline views everywhere else.
In the forms themselves this does not behave the way you and I are hoping.

Got it. Thank you. Could you please elaborate the above. May be we can explore , if there is any solution possible.

Does this means that the marked solution is not a solution any more?

Bahbus
Participant V

No, but what I meant was, that if you aren’t explicitly using that specific system Add action, you can hide it completely. For example, you could be adding the children from a different action that you created that is visible for the parent table. You can easily make that conditionally disappear after a set number of children. And then you completely hide the child’s Add action. I didn’t have any details of your app to go on, so I couldn’t be more specific earlier.

Sam_Sulkey
Participant I

I got this to work but I need to have a different limit per user. One user can have 10 entries but another might only be able to have 5. Is this possible?

Yes certainly possible.

Create your own user table where you hold a value in number type in a field how many row each user can add.

Then use expression to pull that value to see how many row each log in user can add . This expression will be used in show if constrain for action button to add child rows.

What would that expression look like?

(
  COUNT(
    FILTER(
      "Child Table",
      ([Email] = USEREMAIL())
    )
  )
  < LOOKUP(
    USEREMAIL(),
    "Users",
    "Email",
    "Child Limit"
  )
)

Substitute your table and column names as appropriate.

This is what I have going on right now:

[Allowance] Column in Parent Table with the number of entries I want to allow
[Request] Virtual Column in Parent Table with COUNT([Related Request])
[Remaining Request] Virtual Column in Parent Table with ([Allowance] - [Request])

Action in Parent Table to open form and add request in Child Table with condition [Remaining Request] > 0

The Action works, I just can’t figure out how to apply it to the Child Table system Add action.

I dont understand this part.
You dont need to set any action in child table, for this caes.

Sam_Sulkey
Participant I

I don’t think that will work. This is what I have and am trying to do:

  • App Users Table

    • Has Security Filter: [Email]=USEREMAIL()
    • Each user has a column [Parent Name]
    • Multiple Users can have the same [Parent Name]
  • Parent Table

    • [Parent Name] is the column that is referenced by User Table and 10 Child Tables
    • Parent Table and all Child Tables have a Security Filter: IN( [Parent] , App Users[Parent Name] )
    • Parent Table has a number column [Child Table1 Max Records]
    • Each [Parent Name] will have a different number in [Child Table1 Max Records]
    • Parent Table has a virtual column [Child Table1 Records]
      • Formula: COUNT([Related Child Table1 Records])
    • Parent Table has a virtual column [Child Table1 Records Remaining]
      • Formula: ([Child Table1 Max Records - [Child Table1 Records])
    • Parent Table Detail View has a prominently displayed Action Button I created for opening a form view and adding a new record to Child Table1
      • Condition: [Child Table1 Records Remaining] > 0

The Action I created works and does not display when the condition is met. I can’t figure out how to make the same thing happen in Child Table1 or the Related Child Table1 views. I want to disable Add everywhere when the [Child Table1 Records Remaining] hits zero.

I hope this makes sense and someone can help me out. I don’t fully understand formulas yet and my brain is fried from trying to figure this out.

Top Labels in this Space