Forum Discussion

DanielBewley's avatar
Qrew Trainee
2 years ago

(Pipelines) Get Users in Role / Process XML from HTTP API

Hello, any help/advice would be much appreciated.

I'm trying to send a reminder email to users in a specific Role, in a specific App, using Pipelines.

As far as I can tell, there is not a JSON RESTful API call that does this (Get Users only returns all users for an App, with no info on Roles).  However, API_UserRoles returns each user from an app with what Roles they have.  In theory, I could somehow loop over this and send the email to only those users with a specific role.

I can successfully use the Quickbase Channel -> 'Quickbase APIs' -> 'Make Request' step to call API_UserRoles and get this data.

Here's where I run into trouble:
  • How do I process this XML into a form that another step could use (e.g. loop over it and send emails)?  I found this question: "To capture an XML response from an API in Pipelines" but I can't seem to figure out how get {{a.json.qdbapi.value}}.  When I try to view its contents (emailed to myself) it is blank.
    • There isn't any "Result" field or something like that from this request step available in subsequent steps.  Only URL, Method, Headers, Content Type, & Body.
  • For instance, if I want to get the JSON out of the XML (using {{a.json.qdbapi.value}}) with the JSON Handler Channel -> 'Iterate over JSON Records', the 'JSON Source' field states 'No json sources in the pipeline'

Thank you for any help you can offer,


8 Replies

  • Wow!  Thank you so much!  I should have noticed that.  I feel kind of dumb for not noticing that.  Where can I go to learn more about how to write this type of code?  It's Jinja2, right?

    • DougHenning1's avatar
      Community Manager

      I should have mentioned you would need to change that line too, but happy to hear it's working now!

      There is some help on using Jinja in Quickbase:

      That page also has a link to the Jinja documentation:

  • Sorry to bother you again, but I really don't understand where the problem is.  Any help is truly appreciated.  Here is a screenshot of the pipeline and error:


    • DougHenning1's avatar
      Community Manager

      Hi Teresa, no worries happy to help! I see from the screenshot that your query is step A and not B, so you need to adjust line 2 to "a.json.qdbapi.users.user" for it to read the data properly.

  • Here is some other code from Doug Henning about 2 years ago that seemed like it was working for my issue, but I get an error when I paste it into the Text* field for the Find All Matches to a RegEx which says that it contains invalid Jinja syntax. 

    {% set users = a.json.qdbapi.users.user -%}

    {% set searchRoleId = '18' -%}

    {% for user in users if (user.roles.role | selectattr("@id", "eq", searchRoleId)|list|count) > 0 or user.roles.role['@id'] == searchRoleId -%} {{ }} ({{user["@id"]}}){{ "," if not loop.last }}

     {% endfor -%}

    I was able to run the pipeline once though, and it gave me the result I was looking for, but after that it won't allow me to run the pipeline.  And like I mentioned in my earlier post, the lengthier bit of code referenced in this post allows me to get past the syntax errors, but give me an error when I run the pipeline.

  • DougHenning1's avatar
    Community Manager

    This code will filter the API_UserRoles output on a specified role and return the names as a semicolon-separated list:

    {% set searchRoleId = '12' -%}
    {% set users = b.json.qdbapi.users.user -%}
    {% for user in users if user["@type"] == "user" -%}
    {# Handle single vs array of roles -#}
    {% if user.roles.role is iterable and user.roles.role is not mapping -%}
    {# Array of roles -#}
    {% set userRoles = user.roles.role | map(attribute='@id') | list -%}
    {% else -%}
    {# Single role, return as array of roles -#}
    {% set userRoles = [user.roles.role['@id']] -%}
    {% endif -%}
    {% if searchRoleId in userRoles -%}
    {{ user['name'] }}{{';' if not loop.last }}
    {%- endif -%}
    {% endfor -%}

    Change line 1 to search for a different role id. The data returned from API_UserRoles is different if a user is a member of a single role vs multiple roles, so this code has the logic to handle both cases.

    The output will look like "John Doe;Jane Smith;Bob Jones". The output of the regex step should assign a "group_0" to each of the names that you can iterate with a loop.

    Hope that helps!

  • I know this post is old, but it relates very well to what I'm trying to do.  I'm trying to get the user names of all people in a particular role.  There's just a couple things I don't understand in the RegEx channel.  First, how do I write the code to filter the logic in step B for a certain role (for example, Role ID = 12)?  Second, I am trying to retrieve the user's name.  It's stated that you should convert the string "group_0" to an object and can then reference the property.  How is this done?  I ultimately want to create a record with the user name or delete a record for that user name from a test table, depending on what is returned from the API_UserRoles call.

  • DougHenning1's avatar
    Community Manager
    You can use the Regex channel to create a list of objects to iterate:

    Then you can add a loop to check each one.  Since each "group_0" in the match is a string representation of an object, you can reference any of it's properties.  For example, to get the id, convert the string to an object then reference the property: {{ (b.group_0|from_json)['@id'] }}

    If you want to filter out certain roles just put the filtering logic in the jinja for step B to exclude adding to the list.

    Hope that's helpful! ​

    Doug Henning