We need to base our record pickers in our People table on their roles in the Users table. How do we make a field in the People table that shows their role?
Unfortunately there isn't a way to apply a formula against a field/record in a table. You can do UserRoles() but that will only return the role of the current user.
If you need actual role information - you will have to invoke API_UserRoles via a pipeline or some other method of integration and match up the user role(s) with the records in your table.
Good question and also response from Chayce.
I will add my experience to this problem in case someone finds this post.
I looked at the API_GetUserRole with a Workato loop and found the nested array returned more than one Role for each User and there was no indication which Role was dominant. So in order to determine the active Role it's a guess. Add to that the returned results were not in any order so if a User has multiple Roles, they are returned as is without indication of precedence.
After about a day of trying to come up with a solution, including going back and forth with QB Support who were not helpful in any way to answering how it is supposed to work, I gave up.
Here is this general process developed to get the desired data:
Use API_GetUserInfo for each User to build a list of User IDs.
Apply that list to a loop for each user id and request API_GetUserRole.
take the returned array of Roles parse out the nested list
filter out blanks and nonsense
pick a Role and write it to a field in a table associated with the user id
repeat
This process got us into a bit of trouble with Quickbase and Workato because we had to use a nested loop to go through each user for each app to get their role for each app. We decided it was not needed and abandoned the project.
Using the Admin Console Connected table we can get the status of a User and Last Access, which is good enough.
Last thing to say is there was a Admin Meetup yesterday where Ventsis explained the Admin connected tables. Check out the Events page to get connected with these groups.
If that is the route you end up going - you can also do it with Pipelines. I do this in concert with the realm sync similar to Jim, but instead of Workato I query a list of apps that I want to run, make the API call and run it through a JSON handler pipeline for each app.
I've copied a sample of that Pipeline if the event that you want to explore it a bit more - this pipeline. In this case - I have it push single roles into a single bulk import and then if a user has more than 1 role then it does a loop and imports those separately as one off instances.
