Filter rows based on a referenced table

[GSuite_Omar_Poc]

Hi Team,
I have a table “Contratos” where I can show filtered rows using the sentence [Usuario]=USEREMAIL() into a slice. It is OK !!!

The problem came when I must enable two or more users to see any row.

The first that I think is to add columns [Usuario 1], [Usuario 2] , [Usuario 3], etc into the table, and make and OR function over they.
This works but it is not an “elegant” solution, because I must add additional columns to avoid need change the table structure into Table definition and change the or sentence later.

I think that it can be made with a related table with the users enable to see each row, both related by ContratoID

Contratos Table
CTID - - - - Desc
1 - - - - - - - Contrato Auto
2 - - - - - - Contrato casa
3 - - - - - - - Contrato Yate

Usuarios Table
CTID - USERID - USEREMAIL
1 - - - - - - 1 - - - - - carlos@gmail.com
1 - - - - - - 2 - - - - - pedro@gmail.com
1- - - - - - -3 - - - - - juan@gmail.com
2 - - - - - - 1- - - - - carlos@gmail.com
3 - - - - - - 1- - - - - pedro@gmail.com
3 - - - - - - 2 - - - - - omar@gmail.com
3 - - - - - -3 - - - - --yates@gmail.com

But I can not to find how create a filter ussing related “Usuarios” table to see the rows into “Contratos” table.

Is there a way to do it ???

Thanks in advance

Omar

[Aurelien]

Hi @GSuite.Omar_Poch

not sure if that does the job because I don’t know your table structure and type, but what about that ?

Assuming that:

• in Contratos table, the column CTID is type Ref or Enum (base Ref)
• in Usuarios, CTID is type Enum (base Ref) or EnumList (base Ref) type

IN([CTID], 
  LOOKUP(
    USEREMAIL(),
    "Usuarios",
    "USEREMAIL",
    "CTID"
  )
)

If that doesn’t do the job, can you share more informations on your table structure ?
Screenshot are best !

[GSuite_Omar_Poc]

Hi Aurelien:
Thanks for your answer.
My english is not very good, and then I think that I did not describe the relation table well.

The tables are these

and

The data for example state is this ( I show both data at same sheet to clearity)

The users must see on Contratos table:

carlos@gmailcom only the row CTID=1,
pedro@gmail.com the rows CTID=1 and CTID=3
omar@gmail.com the rows CTID=1 and CTOD=3,
autos@gmail.com only the row CTID=3

Note: The USERID Column is a number inside each CTID, it is used only to shows how many users can see each row into “contratos” table.

I think some as this:
The app go to “Usuarios” to filter it with [USER MAIL]=USEREMAIL()
For example if the user is omar@gmail, the “Usuarios” table will shows two rows

Then the “Contratos” will be filtred to show CTID=1 and CTID=3-

I want that this description will be clear

Thanks again

Omar

[Aurelien]

Hi @GSuite.Omar_Poch

Thank you for sharing further information.

I suggest you change your data structure this way:

Table usuarios:

• CTID : type EnumList, base type Ref, source table name Contratos
• User mail : set it to be key column
• remove the _computedkey
• then, remove some rows so that you have unique row per email.
• Also, not necessary but I suggest you remove the column USERID, as the column USER MAIL will be the key_column. It all depend on your needs with that.

==> Now, you should be able to select multiple CTID per user.
So, after doing so, you will probably have something like:

Contratos table:
this one is perfect, don’t change anything

NOW:
take the expression described in my previous post, and set it as row filter condition for your slice.

[GSuite_Omar_Poc]

Thanks a lot Aurelien. Especially for the time that you’ve taken for answer my question

I will test your solution in a while, but I think the method is not going to be very practical in my case .

I forgot say you that the table “Contratos” has near of 1.000 records, and the “Users” near of 600. Sorry.

I “dream” with and interfase similar to this:

I select Pedro user from a list table showed into a previus View. I can see the Pedro’s contracts

Then I can go to next user and see his contracts

With this will be easier edit the “Usuarios” table.

Is possible to create a rutine to update data in the format that you say into “Aurelien Usuarios” (your table).

Thanks a lot for your help !!!

Omar

[Aurelien · Accepted solution]

Hi @GSuite.Omar_Poch

Oh great, then you will need to change slightly the structure of the app.

• You may want to rename “Usuarios” into “contratosUsuarios” to make more sense.
  Then:
• add a new column, which you will set as the new key_column
• fill this column with unique values. I suggest you set k_00 in the first cell, and pull it to the last cell in order to get k_01, k_02, and so on.
• create a new table “UsuariosList”, with unique list of user emails that comes from contratosUsuarios. I suggest you use the expression =UNIQUE(SheetsContratosUsuarios!C:C) in your new sheet in order to quickly grab them ( I assume here it is the 3rd column on your sheets, i.e. column C). Then you can add a new column such as name and tick its Label properties (just a suggestion)
• in table contratosUsuarios, set the UserMail column with type Ref, source table name UsuariosList

…I think I didn’t forget anything, that should do the job.
Le us know !

[GSuite_Omar_Poc]

Yes Aurelien !!!
I modified the APP with your instructions, and it works !

I’ve created a slice to filter the “Usuarios” table…
For test, I set the Row filter condition to any existing email (in this case pedro@gmail.com)
To deployed APP, I will replace it with [USER NAME] = USEREMAIL()

Then I create a detail view based in this slice

Now I need to study which is the better way to show the data, but that will be another capitule in my AppSheet learning ( which is in the first stage)

Many…MANY…VERY MANY THANKS for your help Aurelien !!. I hope I can to help you in the future

Omar

[Aurelien]

Hi @GSuite.Omar_Poch

Very glad to hear you succeeded !

One more tip, about testing: you can simulate any user email at entering it under the preview panel, on the right side of the editor.
“Preview app as”

Thank to that, you can directly set your expression with USEREMAIL()

Cheers !

[GSuite_Omar_Poc]

The user simulation is great for testing the APP.

Thanks Aurelien !

Omar