User security filter with Read column

Hi

I have two tables

  1. Users
  2. Notes

I have a the following columns in the note table relevant to what I want to achieve and the notes are in a deck view.

  1. Note Owner:
    -Captures the email address of the user creating the note.

  2. CC Users Column:
    -Is a enumlist linked to the list of users in the user table where the note owner can select multiple user names to be used to lookup the relevant users email address to be used in a security filter.

  3. Read By:
    -indicates a populate list of users who open the note from deck view to detail view.

Questions

  1. I would like 2 slice deck views that I would like to setup security filter that only list either the note owner or relevant User in CC.
    1.1 Note Owner
    1.2 CC Users

  2. How do I get the Read By column to populate a list of users that open up the relevant deck view to detail view.

So the idea is that I will end up with the following filtered views.

All Notes By Owner
Unread Notes By Owner

All Notes By CC
Unread Notes By CC

Any ideas on how to achieve this?
Are there perhaps any sample app and or other threads available that replicate what I would like to achieve?

Thanks for the help in advanced.

(USEREMAIL() = [Note Owner])

NOT(IN(USEREMAIL(), [Read By]))

The “CC Users” aspect makes no sense to me. Please explain it in greater detail.

Why do you want to use a security filter, specifically?

Maybe not a clear name for the column.
I was “inspired” by the CC section you find in emails. :wink:

So the idea is that the note owner is able to select a list of Users from Enumlist where the valid if expression of the Enumlist lists users from the full name column (not email column) of the below User table example.

I believe I would then need some kind of lookup expression in either a security filter or slice that would lookup the full name of the useremail log in to the app and then only show a short list of notes records in the deck view.

I thought security filters are usually a better option vs a slice as a security filter is basically a slice that only loads the filtered data record to the relevant app user where as a slice loads all the data records to the app user but just does not show them.
I thought that would always be a better option.

The next question would be then is how do I populate the Read column with list of full names (not emails) when the relevant users open the deck?
So I think I need some kind of lock up express to populate the Read column with full name values of the relevant logged in user.

I then read somewhere back in other threads that mentioned that I could possible have an action that automatically triggers and populates the Read Column with full name values of the relevant log in users email address once the deck view is selected.

  1. I am just note sure how to set this up.
  2. What would probably be more complex would be how to add and not overwrite full name values.

That would also mean that the below expression would need to lookup user email as the Read By column is populated by full name column values of the user and not the user email itself.

User Table:

Email Full Name
bas###@##.com Basil S##
day@##.com Dayan W##
hei##@##.com Heinz P##

Is Full Name the key column of the User table?

“All Notes By Owner”: does this mean:

  1. All notes owned by a single given user?
  2. All notes owned by anyone, grouped by owner?

“Unread Notes By Owner”

  1. Notes by a single given user that the owner has not read?
  2. All unread notes, grouped by owner?

"All Notes By CC’

  1. All notes by any user listed in the CC list?
  2. All notes by one chosen user from the CC list?
  3. Something else?

“Unread Notes By CC”

  1. All notes by any user listed in the CC list that have not been read?
  2. All notes by one chosen user from the CC list that have not been read?
  3. Something else?

How would the note owner and CC users list be designated? Where would the slice and/or security filter(s) get this information?

Email is the key.
Full name is the label

I starting to realise that I need to be using slices as I need to sometimes filter for different things (I.e CC User) and that using a security filter would only be an option if I only had a view that would be filtering for Note Owner for that particular user email address.
I apologize for the confusion.
So I hope this sorts out this question of yours.

Then to answer the rest

  1. Option 1. So the note owner column has a Useremail in the initial value. I will then use the below expression you gave me for Deck View slice that will only show note records for that user.

Neither. This view will be deleted. I just realised that I am incorrect in requesting this filtered view as obviously the note owner has already read the note he has created himself :man_facepalming:t2:
Sorry about that.

  1. Option 1. So if I as the note owner create a note and select Basil and Heinz in the CC user column enumlist. Then both Basil or Heinz will see my note in this deck view, but not the other users using the app. The enumlist however will be showing and populating the Full Name labels and preferably not the emails as I do not want to disclose their emails addresses to other users.
    I therefore would need some kind of lookup expression in the slice of this specific deck view that looks up Basil and Heinz’s email address.

So using my example above. I create the note and I add both Basil and Heinz in the Enumlist of CC user. Both of them will now find the note in the “All Notes By CC” deck view but not other users.
Basil then decides to open the note by selecting the note in the deck view there by opening that detail view, but not Heinz.
This action will add Basil name in the in the Read column.
I suppose this is somehow captured by some kind of action that gets triggered when Basil selects the note in the deck view to and open up the detail view.
The expression would look at Basil’s email address and populates the empty Read column with his name which come from the value in the Full Name column next to his email address.
Therefor there would be 2 criteria for slice for Unread Notes By CC deck view where:

  1. Only notes where Basil is listed in CC user will be shown.
  2. Only notes where the Read column does not list his name. So in other words Heinz will see the note as he has not opened the note yet thereby adding his name to Read column but Basil has.
    However when Heinz has open the note the Read column with add his name to the list (i.e. Basil, Heinz) and hence the note will not be shown for either of them.

I hope I explained this correctly. Thank you for your patience.

What about the following, as slice row filter expressions?

All notes owned by the current user:

(USEREMAIL() = [Note Owner])

All notes for which the current user is in the CC Users list:

IN(LOOKUP(USEREMAIL(), "User", "Email", "Full Name"), [CC Users])

All notes for which the current user is in the CC Users list and has not read:

AND(
  IN(LOOKUP(USEREMAIL(), "User", "Email", "Full Name"), [CC Users]),
  NOT(IN(LOOKUP(USEREMAIL(), "User", "Email", "Full Name"), [Read By]))
)

Thanks Steve
I will try it and let you know.

Any idea on how I am going to populate the Read Column mentioned below?

I have an idea, but let’s solve one problem at a time.

1 Like