I will try to explain what I want to accomplish, but first I will explain what I have so far:
First, the user has a Card view that points to a google sheet called “DATOS GEN”. This google sheet contains a table with all the apartments of a residential condo with the following columns:
- [UNIDAD] Is the apartment number (column key and label)
- [NOMBRE] The name of the owner of each apartment.
- [EMAIL] The GOOGLE email address of the owner. This column is compared with USEREMAIL() in order to show only the apartments that this particular user owns.
- [MAX CANT INVITADOS] is the max quantity of guest allowed for each apartment.
- [Related INVITADOS] is a virtual column (list) that is referred from other google sheet called “INVITADOS” in order to show a list of the names of guests corresponding to that apartment (the source is the table INVITADOS).
the table INVITADOS contains a list of all the guests of all the apartments with the following columns:
[UNIDAD] The apartment number of the guest (this is NOT a key column, because its values can be repeated in the table)
[EMAIL PROPIETARIO] this is the GOOGLE email of the owner.
[NOMBRE INVITADO] the name of the guest
and there’s more irrelevant data columns…
The Key column is the “_RowNumber” column in this table. (Not recommended by appsheets, it warns it)
Lets say, for instance, a user owns 2 apartments. He will see 2 cards, each one with the corresponding apartment.
When the user clicks a card. The app shows a child view with the details of that unit with all the data from the selected row coming from “DATOS GEN”, plus an embedded list of allowed guests from column [Related INVITADOS] that shows the filtered data of allowed guest for that apartment.
This list has an “add” link in orde to add guests to that apartment, and when the list reaches de max quantity of guest allowed for that apartment ([MAX CANT INVITADOS]), the add link disabled (or no longer swown) as expected.
I did it by adding the following formula to the “Are updates allowed?” setting of table INVITADOS in appsheets:
IF(COUNT(SELECT(INVITADOS[UNIDAD],[UNIDAD]=“103B”))<INDEX(SELECT(DATOS GEN[CANT MAX INVITADOS], [UNIDAD]=“103B”),1),“ALL_CHANGES”,“UPDATES_AND_DELETES”)
where “103B” is the number of an apartment for testing purposes.
The problem is that it works only for apartment “103B” because I don’t have access to the apartment the user selected in the parent view.
And that’s what I want to accomplish: To restrict the number of guests depending on the value [CANT MAX INVITADOS] of the selected apartment.
I hope this summary of my app allow you to hepl me find the solution. I’m new to appsheets…