Forum Discussion

KrissyMayes's avatar
Qrew Member
5 months ago

Formula to get the roles of everyone in people table

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?

Krissy Mayes

4 Replies

  • 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. 

    Chayce Duncan
    • JimHarrison's avatar
      Qrew Champion

      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


      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.

      Jim Harrison
      transparency = knowledge + understanding : The Scrum Dudes
      • ChayceDuncan's avatar
        Qrew Captain

        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. 

        # Realm Manager > JSON Handler | Import User Roles
        # Account slugs:
        #  - quickbase[]: ADMIN <None>
        - META:
            name: Realm Manager > JSON Handler | Import User Roles
            enabled: true
        - TRIGGER json-handler json_source on_incoming_json_blob -> a: {}
        - a<>QUERY json-handler json_object search -> b:
              json_records_path: /qdbapi/users/user
              json_schema_sample: "{\n                    \"name\": \"\",\n              \
                \      \"firstName\": \"\",\n                    \"@id\": \"\",\n        \
                \            \"lastName\": \"\",\n                    \"lastAccess\": \"\"\
                ,\n                    \"@type\": \"\",\n                    \"lastAccessAppLocal\"\
                : \"\",\n                    \"roles\": {\n                         \"role\"\
                : []\n                    }\n               }"
        - ACTION quickbase[] bulk_record_set define -> c:
              export_fields: '"Related User, Related Application, Role ID, Role Name" <9,
                12, 13, 14>'
              merge_field: '"Record ID#" <3>'
              table: '"Realm Manager: User Roles" <DBID>'
        - b<>LOOP:
          - DO:
            - IF:
              - AND:
                - expression<> TRUE {{b.roles["role"]["name"] is defined}}
              - THEN:
                - c<>ACTION quickbase bulk_record_set create -> d:
                      related_application: '{{a.headers[17].value}}'
                      related_user: '{{}}'
                      role_id: '{{b.roles["role"]["@id"]}}'
                      role_name: '{{b.roles["role"]["name"]}}'
              - ELSE:
                - ACTION quickbase[] quickbase_api make -> e:
                      body: "{\n     \"to\": \"DBID\",\n     \"data\": [\n          {%\
                        \ for role in b.roles[\"role\"] %}\n            {\n              \
                        \  \"13\": {\"value\": \"{{role[\"@id\"]}}\"},\n                \"\
                        14\": {\"value\": \"{{role[\"name\"]}}\"},\n                \"12\"\
                        : {\"value\": \"{{a.headers[17].value}}\"},\n                \"9\"\
                        : {\"value\": \"{{}}\"}\n            }\n            {% if loop.last\
                        \ == false %},{% endif %}\n          {% endfor %}\n     ]\n}\n   \
                        \ \n"
                      content_type: application/json
                      method: POST
        - c<>ACTION quickbase bulk_record_set commit_bulk_upsert -> f: {}

        Chayce Duncan

  • For jinja & JSON novices like me, here's what I finally figured out in order to deal with the error message that is thrown when a user has no role in the app and you're trying to populate a field with the role name property. I added a condition that checks to see if a role is returned, and then if so, populated with the role name property, and if not, populated with None. It took me forever to figure out the right syntax to get the expression to actually evaluate properly (I had to use the tojson filter to actually get a value to compare against), so hopefully this helps someone else as well.

    Expression evaluates to TRUE:

    If TRUE, then populates the Role field with {{quickbase_make_request_1.json.qdbapi.user.roles.role['name']}}

    If FALSE then populates the Role field with 'None'