Show values based on user list

Atlas · 07-13-2020 04:30 PM

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.

Users should see related projects
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,),
             ","
           )

MultiTech

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], 
" , "
)

Atlas

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

"," - brings the correct list

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

Steve

Try:

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

MultiTech

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],
		  " , "
		)
	)
)

Steve

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

Good call!

MultiTech

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

Atlas

@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

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

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.

Atlas

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.

STAFF TABLE SPREADSHEET

STAFF COLUMNS APPSHEET

PROJECTS TABLE SPREADSHEET

PROJECTS COLUMNS APPSHEET

Steve

These should be project IDs:

Atlas

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?

Steve

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

Atlas

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.

Projects

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.

MultiTech

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]))

Atlas

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.

MultiTech

Your welcome