I have a table named ‘Milestones’. I have a field ‘fk_users’ which looks up the user id from the ‘Users’ table
This field fk_users is VARCHAR and set to allow assigning mutiple users to a single record.I’m looking for the best way to fiter list view so that by default it will show the records filtered to the current user, but will still allow the user to view all records if they want.
I have tried setting a filter expression using "fk_users LIKE ’ “.CurrentUserID().” ’ "
but since the id field in the users table contains ids starting at 1 and so far up to around 180 with no prefix zeros, for user id 1 for example, the expression returns also records for other users whose id also contains “1”. it also won’t allow the user to see other records too (though i could create a separate view for that).
I have also added Ext search to the fk_users field, and that seems to filter the records correctly.
The URL search query URL ends like this: &z_fk_users=LIKE&x_fk_users%5B%5D=1
If I can figure a way to parse CurrentUserID() into the search query, that would be be enough.Is this possible?I’m probably going to switch the system to use people’s state assigned id number as the primary key instead. This would avoid this issue as all ids would be 11 digits long and all unique. But the domino effect of that change is pretty huge, as other tables will be affected.