Filter rows in Table 2 based on result of filter in Table 1

Showcase
1

Hello,

I’m creating individual reports. Each participant will only get access to their report. I get data from the users through 3 different surveys. I have to create several charts per user. I want to have 1 filter field (or look up value) that all other charts are linked to. Right now I’m creating the following:

  1. Table with emails pulled from survey 1 (table name is restory user). This is manually filtered to only show 1 email. By this I mean I set up a filter as in selectuser contains xxx@gmail.com
  2. Table with results from survey 2 for the selected users (table name is dcheckpoint). I can’t seem to get it done. Ideally it should be Table2[email in Table 2 = selected user in Table1)

When I manually create the filter, Table 2 is correctly filtered.

I’ve tried to filter Table by adding this formula:

Filter(dcheckpoint,Email = [Restory User].selectuser.First())

but that didn’t work.

Any ideas where I’m approaching this task wrong?

Thanks.

2

Hi there @Katerina_Bohle_Carbonell :blush: .

I’m completely unsure of what I’m going to say here (having no visual sample to lead me in a direction or another) but : is this what you’re trying to do ? :innocent:

I use an interactive filter to filter all the different tables in this quick sample :blush: .

1 Like
3

Thanks for replying. It was way too late when I posted it. I tried a bit further and found out that the problem wasn’t the formula, but the data. The first rows in the data are useless. The first entry in the linked typeform response sheet with useful information is row 4. This formula works
thisRow.Email.Contains([Restory User].selectuser.Nth(4))

Ideally I have something that returns the first visible row from [Restory User].selectuser.

But your set-up gives me ideas to circumvent the issue with dirty data from typeform. As it’s private data, users shouldn’t see other people’s emails. Maybe there is a way for users to enter their email address (ideal situation), or only part of the doc to be shared.

Thanks for your help.

4

I agree :blush: . And I did try things to get around that problem :blush: .

Normally, you should be able to lock the pages you share with those users in `Read Only’, so they shouldn’t be able to modify anything :blush: .

In this screenshot of the same setup I shared earlier, the dropdown to select an another email in “Email matches” disappears :blush:, so they shouldn’t be able to change the email selected and therefore see the others :blush: (this might require some further testing though :wink:)

Capture d’écran 2021-01-20 à 18.20.21
Capture d’écran 2021-01-20 à 18.20.212286×1276 252 KB

Following the same principle I shared earlier, it also works with a Formula on the canvas where you should be able to apply a kind of similar filter than the one you used with success :blush: .
But it also means that for each user you’ll need to modify that formula where the interactive filter or just a Select control on the canvas would probably simplify things a little bit (as once your page is setup, you should just need to select a different user there :blush: )

Here are 2 samples of using a formula on the canvas :blush:

Now, if you want people to enter themselves their email, it’s doable using a very small table which I called here [Enter your email] (see my sample below) and hide everything around (here I just left the Column headers) but it also means that in terms of permission, they will need to be able to Change table values and there is no way to lock specific tables :confused: . Meaning, they could be able to modify what you’ve spend time to build too …
In this case, the filter formula for each tables might also needs some adjustments (People might had their email using only lowercases where in the forms, they used uppercases here and there, which might false the results of the filter formula and possibly lead to errors - I didn’t test this, so I can’t be sure :innocent:, I just thought about it :blush: )

This is as far as I could go with the time I had today :blush:.

2 Likes
5

Thanks for all the information. That helps a lot. You mention

Does that mean that within the same doc I could have an intro page with a Table with a search box, and then another page with the actual report? The first page (intro page) would not be looked and the second page would be read-only?

6

You’re welcome :blush: !

And yes, you can apply different locking settings to different pages in your doc :blush: .

You can find more info about Doc Locking here : :point_down:

1 Like
7

@Katerina_Bohle_Carbonell : There’s a feature you might want to try too or at least take a look at :

I just forgot to mention it yesterday, sorry :sweat_smile: .

This might help you with sharing the results of the surveys with the concerned users :blush:, as stated in the help page :

Secure Data Sharing

If you have people who only need to see a certain part of a table, you can set this up with Cross-Doc.

  1. You’ll start with your core table. Filter it for your criteria (e.g. Filtering a table to be just one person’s performance review results)
  2. Pull the filtered view into the individual’s doc
  3. Share the new doc with them. They will not have any access to the other data.

Note - this will also work for any hidden columns.

1 Like