Are you using the ToUser Function?? You Need to Read This!
Are you using the ToUser Function?? You Need to Read This!
We here at Quickbase are always working behind the scenes to improve application performance. We scrutinize reports, fields, logs, basically anything we can to try to help you get the most out of the platform.
Like many Builders, you may be utilizing the ToUser function to convert an email address field to a 'user' value. You may be using it in formulas or permissions. After optimizations we’ve made, and working with many of you, we are now able to clearly describe a more efficient way of building these patterns.
TL;DR: Anywhere you're using the pattern of ToUser([Email]), you can exchange with UserToEmail(User())!!
Let's say you have a numeric formula field where you want to ascertain if the current user matches up to an email field.
Initially you might have built it like this:
I don't want to go down the rabbit hole of what this means on the back end because let's face it, at the end of the day, you just want your app to work. And you want it to work wicked fast.
To that end, we've identified that the UserToEmail function is a much more efficient way to go. In short, this formula only needs to make one call to convert User() to email, while the ToUser pattern has to parse through the entire user list to convert ToUser([Email]) for each record.
Your new and more efficient formula would now look like this:
One of the most popular patterns leverages the User() function and the User field type to control permissions. It is also very powerful because the platform is using the operational data to help drive what is viewable and editable for each user. For example, say you have a project management app and you only want the assigned project manager to be able to edit project information for the projects she is assigned to. An app builder can create a ‘Assigned Project Manager’ field using the user field type. Then in the permissions for project managers she would update the Modify permission to ‘when user is Assigned Project Manager’ as illustrated below.
In the case above we only used the User field to determine which Projects are editable for a given Project Manager.
Iterating on the above example, imagine you had a team of people who should be able to view projects, but only if they are on the team assigned to a project.
(The general pattern for this example is in the relationship diagram at the end of this post.) It consists or 3 tables (Projects, Teams and Team Members). Now, this is where the power of Quickbase really shows itself. We use a formula field to determine if the email address of the current user viewing the data is equal to the email address on any of the Team Member records. If the email addresses are equal, the formula returns 1 (true) otherwise 0 (false).
Take a look at the below screen shot of the Team Members table, and you’ll see sgalloway@quickbase.com under the ‘Is Current User’ field display as 1 -- and all the other Team Members as 0, because he is the current logged-in user.
- Now the ‘Is Current User’ field from the Team Members table can be summarized up to the Teams table to the field ‘Current User on Team’.
- Notice that sgalloway@quickbase.com, in the above Team Member table screenshot is on the Banking team.
- In the below screen shot of the Teams table, the Banking team has a value of 1 under ‘Current User on Team’ because the current logged-in user: sgalloway@quickbase.com, is assigned to the Banking team.
Now the ‘Current User on Team’ field can be a lookup field down on the Projects table. In the below screenshot you’ll see all Projects with the Team of Banking have a 1 under ‘Current User on Team’. This is because the logged-in user is sgalloway@quickbase.com, who is on the Banking team.
The ‘Current User on Team’ field is calculated every time a report or form is displayed with the field on it, which is definitely the value of our engine, because now the builder can leverage the value to:
- Filter a report to only show Projects where ‘Current User on Team’ is equal to 1. Thus, creating one report which will dynamically display Projects based on the team(s) the viewing user is assigned to, instead of multiple reports filtered by team.
- Use the value in a custom permission so Team Members can only see Projects where their team is assigned. See the below screenshot of the permission configuration.
Full illustration of this pattern and the relationships, summary fields, and lookup fields needed is diagrammed below:
VOILÀ!! Anywhere you're using the pattern of ToUser([Email]), you can exchange with UserToEmail(User()=[Email])!!
