PREMISE: i've setup a login/registration system that populate a slice of [users] named [current_users] that contain only 1 record, the id_users and email of the current logged in users. so accessing [current_users].[id_users] INDEX 1 result in the current logged in id_users.
I would create a [table].[assigned_to] column to store the [users].[id_user] that are allowed to retrieve the data. This column must contain a list of allowed user that can be updated or changed to assign some [table] record to different user.
I've created a good system to track "created_by, updated_by, data_creation and data_update" combining initial value and app formula to store the timestamp and id_user of who create and update the record.
Now what i would do (and what i'm able to create in any programming language) will be some similar to this:
[assigned_to] is a list type
when a record is created current [id_user] is stored in the assigned_to
a privileged user can add more [id_user] to the list to assign the record to other users.
In appsheet i've found this option:
[assigned_to] is an EnumList type
valid value is [users].[id_user]
suggested value is [user].[id_user]
initial value is [current_users].[id_users] (who create is the first assigned_to
now what i would try in the app formula (so computed on edit) is to check if [current_user].[id_user] is in the list itself and if not add it.
Here "current_user" is INDEX(current_user[id_user],1), for semplicity i will refer it as "current_user"
IF(NOT(IN(current_user,[_THIS])),LIST([_THIS],current_user),[_THIS])
So check if current user is in this field, if not this field become a list of this field and the new value of current_user. If the current_user is just in the list, this field become itself.
SPOILER > this does not work.
Ok after a lot of throuble:
IF(IN(INDEX(current_user[id_users],1),LIST([_THIS])),[_THIS],LIST(TEXT([_THIS]),TEXT(INDEX(current_user[id_users],1))),)
This is crazy. enumlist type must be converted in LIST type (!! is just a list comma separated !!) , after that it must be converted in TEXT type to be passed with LIST() function. BTW i CANT manually edit this field because it has an app formula, so i think i need to use a different approach:
use and assign_to field to select the users (enum type), and an assigned_to where use the formula above to store the users assigned list...
User | Count |
---|---|
37 | |
31 | |
29 | |
22 | |
18 |