Show values based on user list

AppSheet AppSheet Q&A
1

I am stuck on this for a while. Any guidance to write this expression would be helpful.

I am trying to restrict the users to view a list of projects from the PROJECTS table if these projects are in the comma-separated list in the STAFF table.

  1. Users should see related projects
  2. Reference should work.

With the formula below, I get yellow exclamation marks. If I chose a key and a label both for PROJECT_NAME exclamation marks are disappear but then I would have duplicates ID’s in my DATA table.

DATA[PROJECT] column is ref type to PROJECT and Valid_if expression is.

SPLIT(
      SELECT(
             STAFF[PROJECTS],
             USEREMAIL()=EMAIL,),
             ","
           )
2

Close, the default separator for a SELECT() formula is:

" , " - that’s “space comma space”

You’re using “comma” only - try adding the spaces on both side, that should work.

A potentially easier solution, one that would allow for possible further advancements, would be to include a “Current_User” slice.

  • Create a slice of the staff table.
  • Use a formula like this:
    USEREMAIL() = [Email]

Where [Email] is a column that holds the email a user will use to log into the app.

Then, instead of re-querying the Staff table you can just call the slice - which is holding your users record.

split(
Current_User[Projects], 
" , "
)
1 Like
3

MultiTech_Visions:

You’re using “comma” only - try adding the spaces on both side, that should work.

Thank you for your attention, I tried this but it brings all project in a row, it doesn’t separate the list.

The problem is that I can not make a solid ref list to PROJECTS table.

4

Try:

SPLIT(
  SELECT(
    STAFF[PROJECTS],
    (USEREMAIL() = [EMAIL])
  ),
  " , "
)
1 Like
5

Ahhh… I know what’s going on.

I’ve noticed that SPLIT() seems to “remove” the ref nature of a piece of data, in order to get it working you’ll have to use a dedicated SELECT() to create the list with the appropriate type.

UGH. I know.

You’ll end up with something like this:

Select(Projects[ProjectID], 
	in([Project_D], 
		SPLIT(
		  SELECT(STAFF[PROJECTS],
			USEREMAIL() = [EMAIL]
		  ),
		  " , "
		)
	)
)

You should never have a SELECT() inside a SELECT() - just fyi.

This is where having a Current_User slice comes in handy, then you could use something like this:

Select(Projects[ProjectID], 
	in([Project_D], 
		SPLIT(
		  Current_User[PROJECTS],
		  " , "
		)
	)
)
2 Likes
6

MultiTech_Visions:

I’ve noticed that SPLIT() seems to “remove” the ref nature of a piece of data

Yep, the output of SPLIT() is a list of Text.

Good call!

3 Likes
7

We should really see about getting that to maintain the type - especially for ref lists.

8

@MultiTech_Visions, Thank you for your time, and suggestions. I have created a user slice and played around with the expression to see if it works. But it seems that the problem is insoluble for now.

MultiTech_Visions:

auto> Select(Projects[ProjectID], > in([Project_D], > SPLIT(> Current_User[PROJECTS],> " , "> )> )> )> >

9

It’s solvable - just a matter of getting the right formula down.
Earlier you mentioned

Atlas:

“,” - brings the correct list

Try that for your split formula, instead of what I proposed.

If that doesn’t work, could you show us the list of projects that’s inside the STAFF table? It’s from this list that we need to compare things, so it might just be some simple technical thing.

10

So then, the expression below returns the corresponding project list with the broken reference
(yellow exclamation marks nightmares!)

SELECT(PROJECTS[PROJECT], 
    	IN([PROJECT], 
    		SPLIT(
    		  Current_User[PROJECTS],
    		  ","
    		)
    	)
    )

I add here pictures of the tables for getting better help.

11

These should be project IDs:

3X_9_f_9ff2cd4ee06d60fad0ac89f07d48cd9912e1d7b2.png

12

I thought the same but here is the problem that I don’t know how to solve.

I have constructed this list to have a dependent drop-down list based on project and block selection. I have 19 projects and each project has 20 - 40 blocks. So I have recurring project names in total 728 rows.

Would it be work if I construct another project list just contains the 19 project names?
And can I get working inline views by projects also by doing this?

1 Like
13

I’m afraid I’m not clear on what you’re trying to accomplish:

Atlas:

Would it be work if I construct another project list just contains the 19 project names?

Atlas:

And can I get working inline views by projects also by doing this?

1 Like
14

In the projects spreadsheet, I had reiterant project names. I thought I could not make ref to all rows in that spreadsheet.

Please look at the picture below.

For that reason, I added another sheet called PROJECT_LIST that includes just the project names.
Now, I made this and it is working.
@Steve & @MultiTech_Visions thank your help.

> SELECT(PROJECT_LIST[PROJECT_ID], > IN([PROJECT_NAME], > SPLIT(> Current_User[PROJECTS],> " , "> )> )> ) > >

Now in the spreadsheet in the DATA sheet, I have references of the project names which I don’t want as a result. I use app to collect data, for me the most important thing is spreadsheets. On the spreadsheet, I want project names.

If it is possible, I want to show users, the projects in which they are related, and project names in the backend data.

15

Atlas:

for me the most important thing is spreadsheets. On the spreadsheet, I want project names.

If this is the case, then I would suggest you add an additional column into the mix - one to hold the keys, then another to hold the names.

This way you can get the best of both worlds.

Your name lookup formula is pretty simple:

Select(PROJECT_LIST[PROJECT_NAME], in([PROJECT_ID], [_thisrow].[COLUMN_WITH_IDs]))

2 Likes
16

MultiTech_Visions:

f this is the case, then I would suggest you add an additional column into the mix - one to hold the keys, then another to hold the names.

Thank you so much, Matt, your suggestion was brilliant.
This idea helped me a lot, but I made a little bit different. As you suggested I added in DATA table a project_id column, so the user just selects this and it writes backend the references, and for the project name I used a lookup formula and hide it in the forms. User limiting, references, and name in backend data all working.

Thanks a lot.

2 Likes
17

Your welcome