Home      FAQ      Forum      Idea Exchange      Ask a Question      My Stuff      Help   
Configurable user properties
I would like to have user properties on user accounts in applications. I'd like the ability to add new properties, and to set those properties for each user - and then to access those properties for the logged-in user in formulas. ie, with a UserProperty("PropertyName") function.

I would find this useful in defaulting links, such as in time entry applications. I would have a table with records for each person associated with the org, with contact details etc for each person. Then on time entries I would create a link to that Employee table - and default it to UserProperty("EmployeeID") . Where EmployeeID is a property I configure as an admin on the user account, which stores the ID of the person's record in the Employee table.

I'd also find this useful in deciding which language to show people, for example in forms. I could collapse unwanted sections, and show the section that explains the form in the person's preferred language - using UserProperty("PreferredLanguage") . Where PreferredLanguge is a property I configure as an admin on the user account, which stores the name or possibly ID of the person's preferred language.
User Experience

Nathan Phillips (CW)
Date Created
7/5/2010 8:53:21 PM
Date Updated
6/12/2012 7:51:25 AM
Promoted By
Nicklas HelgessonMartin Odendaalgerardo garcia
FCI ADMINDesmond BeattyGii Systems
Philipp MatuschkaSam ParishRick Cogley
Nathan Phillips (CW)Brian PetersJohn Elcik
Philippe FaureDamar ChristopherAnthony Carter
Scott Miller
Scott Miller 4/28/2007 2:41:06 AM
At present the User table is locked down which means I need to create another User table holding Users details (Telephone Number, Dept Code, etc). It would be preferable to use the exisiting Table with the the System components locked down.


Rick Cogley 7/5/2010 8:59:47 PM
That does sound very useful.

Sam Parish 7/6/2010 8:49:30 AM
I like this idea too. How exactly will this work differently than making user specific permissions?
Nathan Phillips (CW) 7/6/2010 2:12:13 PM
I see a few different uses for it that user permissions can't do right now. The two I described above (defaulting a link based on a user property, and showing/hiding language-specific sections of forms based on a user property) - plus configuring Add Records access for tables based on a user property. ie, I'd like these particular managers to be able to create this type of record, but no one else.

You could do all of this directly in formulas, but it would be unwieldy. You'd have to maintain the link IDs directly in the formula, specify what to show and hide for every single user on forms and specify Add Records access for every single user in the rule formula for that. With properties, you could just say "behave this way if the user has this property, or that way if the user has that property" - and you wouldn't need to list out every user in the formula.

You could also do some of this with roles - IF everyone in the role uses the same access / language / etc. Supposing I have 30 sales reps, and 20 of them prefer Spanish in the interface, but 10 of them prefer English in the interface - I'd have to create separate Sales Rep English and Sales Rep Spanish roles to make it work. If I have more languages, then I need more roles. And I have to configure and maintain access permissions for all those separate roles. Properties would allow configuring this sort of thing while keeping everyone in a single role, eliminating the overhead of maintaining all those separate roles.

I see something like a User Properties table, where I can add columns, with one record per application user. Then I can simply open up that table and set the properties for any user, create new properties, delete properties if I want to. All properties become available to formulas as soon as they're created.
Gii Systems 1/19/2011 4:10:36 AM
This will be useful to create a "Multi-Reference Column" as well. I need to assign a record to multiple users. This is proving quite difficult.

Would have been quite simple if I had a table called 'Users' and had all active users in there.

Gii Systems 1/19/2011 4:16:10 AM
I agree with the User Table. This idea can be mereged with #104.
Desmond Beatty 1/19/2011 7:17:15 AM
I have had to create a "user profiles" table and manually maintain the relationship with my users. This is weak and prone to having additional non-system information about users being incorrectly represented in the system. I support the idea of a extensible user table for each application.
Nathan Phillips (CW) 1/27/2011 12:30:07 PM
I've kind of jury-rigged this in my applications. It's become a foundational piece of several different areas of functionality that I commonly build in applications.

It takes some relation configuration on each table where I want to access user properties. It's not simple to set it up.

It would be easier for me if there were a direct way in formulas to access user properties. It would be nice if I could specify a table as the "user properties" table in an application, maybe using a User column as the key column, then access the fields in that table for the viewing user in a formula. ie, UserProperty("Can See All Hospitals?")

I would use it to do things like this:

In the View access rule for a table, I might show some records to all users, and all records only to users that have the Can See All Records flag set: `[Record Visible to All?] or UserProperty("Can See All Records?")`

In workflow rules for notifications, I might filter on a user property so that notifications only send to people for whom I've enabled notifications: `UserProperty("Send Me Notifications?")` - for this case, I'd like to allow the user to edit this property themselves.

This would also lead into further functionality, like formula-control over view or dashboard visibility: http://teamdesk.crmdesk.com/answer.aspx?aid=12706&back=ideaexchange.aspx

Or formula-control over view subscriptions. ie, I might filter a view subscription based on a user property that indicates whether or not to send that view subscription to that user: `UserProperty("Send Me Weekly Subscription?")` - or: `UserProperty("Send Me Monthly Subscription?")
Kirill Bondar  Staff  1/27/2011 12:45:07 PM
Merged with:
104 - User Table
Nathan Phillips (CW) 1/27/2011 2:46:48 PM
Places where this capability would be useful:

A visibility-formula for tabs: http://teamdesk.crmdesk.com/answer.aspx?aid=12548&back=ideaexchange.aspx

A visibility-formula for views and/or details views: http://teamdesk.crmdesk.com/answer.aspx?aid=12706&back=ideaexchange.aspx

A visibility-formula for dashboards. Especially now with the great multiple dashboards feature.

A visibility-formula for the New button on details views. http://teamdesk.crmdesk.com/answer.aspx?aid=11306

The custom rule formula for the Add access permission on tables. Currently this custom rule formula has no access to data. User properties would allow some fine-grained control over the Add permission and thus the New button.
Desmond Beatty 2/2/2011 11:53:24 AM
I also seek the ability to apply record picker functionality to lists of users. I do not want users to see ALL other users in a picklist. I seek to control, through record picker, which users show up for a given user. Opening up the user table in the way proposed here allows for data against which the criteria checker in a user record picker view edit would work with.
Nathan Phillips (CW) 2/21/2011 12:51:04 AM
Here's a description of how I create configurable user properties, in case anyone's interested:

Desmond - I think this approach would enable you to what you want with the record picker. If you create a drop-down text column on your user table (Maybe "User Group"), you can pull the User Group of the viewing user onto the user table. Then in the record picker filter, you can compare the Viewing User Group with the User Group column. That will achieve filtering user lists to only people in a user's group.

If you need some people to see everyone in the dropdown, you can create an override "Can See Everyone" flag on the user table. Pull the VU Can See Everyone flag onto the user table, with the same relation, and check for that first in the record picker filter formula.

The formula would end something like this:
[VU Can See All?] or [VU User Group] = [User Group]
Kirill Bondar  Staff  7/4/2011 10:07:21 AM
In the Labs now!

Now you can specify one "User Properties" table per application. The table must have the column of User type as its key column. Once the table is specified, you can access the value from any column in this table via [Column] syntax - this will extract the value from the record with the key matching <the current user>.

To get the value of property for the user stored in a column, regular lookup logic is used. Yet, we simplified the management by adding the list of lookups directly to user column. If there is no existing relationship to user property table and you are adding new lookup, new relationship will be created and used for lookups.
Desmond Beatty 7/4/2011 10:51:23 AM
In this User Profile Table, please consider making (certain) fields in the record for the logged-in user accessible and updatable from the Custom Buttons, Workflow Rules and Actions of ALL OTHER TABLES.

The effect of this would allow the user to change certain aspects of (only) their environment such as which location (Case 415), and other such "Session Variables" raised in Cases 414, 415 and 416.

That these are stored in the User Profile table (rather than in session memory) has the added advantage that they are saved for the next session... the new session resumes in the same state as the previous session exited.

My first specific application for this would enable me to program a capability for my users to select the one account of many that they wish to work on at a given time and to change that easily - without having to explicitly enter and edit the user profiles table. My application (views, record Pickers, etc) could then filter on these dynamic values.

(Note. An additional powerful tool would be to allow the SysAdmin to declare which user profile fields appear either in the Sidebar or Above the Tabs so that the user can pick or enter (depending of field type) the preferred value).

Gii Systems 7/4/2011 10:52:32 AM
Glad that this is in Dev finally. Thanks Kirill.
Some Requests:
1. This table should automatically have a record for all active users, with their role.
2. If a new user is invited, they should automatically be added to this table.
3. If a user is deleted, they should be removed (or flagged inactive) from this table.
4. If a user's role is changed, this should be updated in this table.

It would almost be easier to simply make this the area where users are invited/managed. Access to this table should be restricted initially to only the app owner or users with setup permission.
Kirill Bondar  Staff  7/4/2011 11:00:48 AM
@Jacques - we think of integration with setup's user management area, yet we'd like to keep this table available for regular users as well so they can update some of (their, or whatever is configured via roles) properties - that's what Desmond is looking for.
Nathan Phillips (CW) 7/4/2011 3:56:04 PM
AWE-some! Thanks, guys! I'm really looking forward to trying this out.
Gii Systems 7/12/2011 6:23:50 AM
@Kirill - any update on this? Esp point #1 - 3?
Kirill Bondar  Staff  7/13/2011 5:49:13 AM

You can evaluate user's role via Role([User]) function - this will result to user's role name, or NULL when the user is not a member of the app or has no role assigned.

Making new users into the table won't be an easy task - we won't be able to create new record in the table if, for example, you have some required fields with no defaults in the table. Since the list of users does not change often, it is not a high priority task at the moment.

Desmond Beatty 9/21/2011 7:14:49 AM
Kirill, to your comment of 7/13, I would prefer that a record be created and that the admin be prevented from having required fields without defaults. Regards,
Martin Odendaal 9/26/2011 7:07:52 AM
I agree with Desmond - It is important to have the record created automatically.
Rick Cogley 10/4/2011 8:42:39 PM
Kirill, if we have a makeshift user table already, in our apps, as Nathan describes, do you have any advice for how to convert to this new approach? It seems like it will be a fairly manual and time-consuming process, but I wanted to get your take...
Kirill Bondar  Staff  10/5/2011 7:25:14 AM
Every column in user table becomes what Nathan addressed as UserProperty("Property Name").

For current user's value you can simply use [Property Name] syntax.

For the "user stored in the column" you need to "turn on" the property, otherwise the name space will be cluttered quickly. You can do it right from the user column of interest: it fact it's a front-end to create one-to-many relation and lookups for it. They are not any special, any existing setup will be understood.

Kirill Bondar  Staff  10/5/2011 7:27:32 AM
You can either create new or use any existing table with user column as a key.
Rick Cogley 10/5/2011 7:33:10 AM
Thanks Kirill.
Gii Systems 10/13/2011 4:48:11 AM
Can we look at an easy way to identify weather all active users are in this table or not. When we have many users that come and go on a regular basis it is becoming increasingly difficult to ensure that both the 'Setup user Table' and this table is in "sync".
Desmond Beatty 11/2/2011 7:16:33 AM
I have no migrated my "user profile" table into the new "user properties" table and that's fine so far. Thank you Kirill and the team.

What is missing is the option (per application) to autocreate a new "user property" when when a new user is created.

Separately, to Jacques proposal above, why not simply have a look-up into the User properties tables in each application of all the fields in the "setup use table"
Kirill Bondar  Staff  11/2/2011 11:40:02 AM
Auto-creation problems are described above. Yet, we've made a process bit simpler but adding "Add User Property" and "User Property" buttons to the setup part. First button invokes New Form in User Property table, pre-filling the user column. Second button invokes Edit form for selected user - this also partially covers the request to identify what users have or do not have corresponding user property record.

Currently, when the user is deleted, the record remains in user props - removing record would be a next step.
Martin Odendaal 11/10/2011 2:39:41 AM
Hi Kirill, is it possible to call "last accessed date" into this table?
I would like to create a weekly report that will reflect this data.
Kirill Bondar  Staff  11/10/2011 9:19:35 AM
Working on adding setup-related columns (Role, Name etc) to user properties.

Also, I was wrond about auto-deleting from User Properties when the user is deleted - it would be wrong.

When user is deleted the records in the application are not updated - user info remains in the record. Since you typically use user props for additional user-related info, dropping a user prop record will drop the info that can lead to undesired effects. We'd rather leave the information intact - you can determine whether the user is in app or not by querying user's role.

Kirill Bondar  Staff  11/13/2011 2:11:41 PM
Todays release automatically adds the following read-only columns to user property table:

E-Mail, First Name, Last Name, Screen Name, Role, Default Set, External Customer, Last Access.

These columns are live snapshots from the information found in setup section.

User Property table is extended on assignment. If you already set up User Property table and want to take advantage of new columns, change User Property table to --None--, save, and set it back to previous value.

Also, equality and inequality operators now handle two special cases: you can compare text with role reference, for example:

[Role] = [Default Role]

where first is a reference to role column in User Properties and second is a reference to default role. This is identical to:

[Role] = "Default Role"

with one exception: if you rename the role the reference will be auto-updated with a new name while text will remain intact.

You can also use role reference in ToText() calls convert the reference to its name for use in text-based comparison scenarios beyond equality/inequality.
gerardo garcia 12/13/2011 6:54:45 PM

I am creating a table in which each new record can have 3 access levels selected by the record creator:

by user
by role

"by user" I have been able to solve with user table and multiference tables, but I believe I need a new type of column for [role] from which I could pull existing role values and create a multireference table, instead of creating "roles" table with the role names that would not update if the role is renamed for some reason.

Or is there another way to do this? Thanks.
Philipp Matuschka 12/14/2011 1:28:00 PM
So I am trying this out for the first time. Not entirely strangely, I have a field name in the User Property table which clashes with a field name in another table, from which I want to reference the field from the User Property Table.

[@STAFF Table Einheiten has a field called Objekt and Table "My Preferences" (= my version of User Properties) also has field called Objekt]

I want to create a filter whereby a user only sees Record where [Objekt] = [Objekt], where one of those is the [Objekt] from the UserProperty table. Other than more careful naming, is there a way to differentiate between the two?
Kirill Bondar  Staff  12/14/2011 2:44:52 PM
@Philipp: Conflicting names are resolved in the following order: Column, User Property, Variable, Role. This is legacy logic; before introduction of user properties and roles, column names were taking precedence over variable names.

So far there is no way to specify what object you are referring to. Perhaps, we can easily add mandatory prefixes, but they make formulas clumsy. Smart prefixes - to appear whenever necessary - require fair amount of work we are planning to do.

Kirill Bondar  Staff  6/12/2012 7:51:25 AM
@Philipp: please check latest formula language enhancements

Back to Search Results