MLA '24

Portland, OR

Building an ILL Automation Workflow in a Small Hospital Library Using Microsoft Forms and Power Automate

Jonathan Ratliff
[email protected]

Library Svcs. Program Mgr. Library Services Program Manager
Samaritan Health Services

Flow Setup

Flow type
Automated cloud flow
Flow trigger
Microsoft Forms: When a new response is submitted
Connections
Microsoft Forms, Office 365 Outlook, Office 365 Users, SharePoint
[Optional] Variables:
ParsedIdentifier (String), IdentifierLink (String)

Basic Process Flow

  1. Flow triggered: When a new form response is submitted
  2. Get form response
  3. [Optional] Initialize Variables: ParsedIdentifier, IdentifierLink
  4. [Optional] Branch on whether identifier (PMID or DOI) was provided
    1. If not provided: Skip
    2. If provided:
      1. Use formula to extract identifier value from input
      2. Set ParsedIdentifier to identifier value
      3. Construct IdentifierLink from ParsedIdentifier
    • Note: Steps 3 and 4 are only necessary if you want to programmatically extract a DOI from the provided identifier. To simplify your flow setup, you can skip creating the variables and condition and update your items manually when needed.
  5. Lookup responder in Office 365 Users
  6. Create SharePoint List item with details from combined Office 365 user data and form response and send notification email

Step-by-Step Flow Details

Step 1: Flow triggered

The flow will be triggered to run when new responses to the selected Microsoft Form are submitted

Note: All MS Forms will appear when connected to an account

  • Flow Trigger: When a new form response is submitted
  • Form ID: Select correct Form ID from list

Step 2: Get form response

This action will pull the form response data from the form that triggered the flow

  • Flow action: Get response details
  • Form ID: Select the same Form ID as flow trigger
  • Response ID: Dynamic Content → When a new response is submitted → "Response ID"

[Optional] Step 3: Initialize variables

Two variables are used to store the identifier and a PubMed link that searches for the identifier

  • Flow action: Initialize variable
  • Names: ParsedIdentifier, IdentifierLink
  • Types: String
  • Values: [blank]

[Optional] Step 4: Branch on identifier

Update variables only if user supplied an identifier

  • Flow action: Condition
  • Condition expression:
    • Value 1: Dynamic Content → Get response details → "What is the identifier?"
    • Condition: Is equal to
    • Value 2: [blank]
    • Note: The logic is inverted because it is easier to check for blank than non-blank responses
  • If True (identifier is blank): No action
  • If False (Identifier is not blank):
    • Flow action: Set variable
    • Variable Name: ParsedIdentifier
    • Value: Formula
      Note: This formula extracts DOIs from URLs that may be submitted in the identifier field. The <Identifier> placeholder should be replaced with the Dynamic Content item for your form's Identifier field. 
      
if(
    equals(length(split(<Identifier>, '/')),),
    slice(<Identifier>,
        add(nthIndexOf(<Identifier>, '/', 3 ), 1)
    ),
    if(not(isInt(substring(<Identifier>, 0, 1))),
        trim(
            split(<Identifier>, ':')
            [sub(length(split(<Identifier>, ':')), 1)]),
                <Identifier>
    )
)
    • Variable Name: IdentifierLink
    • Value: (<a href="https://pubmed.ncbi.nlm.nih.gov/?term=@{variables('ParsedIdentifier')}">Open in PubMed</a>)

Step 5: Lookup responder

Lookup the user by their email

  • Form action: Search for Users (v2)
  • Top: 1
    • Note: This ensures only the first result is returned
  • Search Term: Value 1: Dynamic Content → Get response details → "Responders' Email"

Step 6: Create list item and send notification

Use the form response and user data to create a new list item and notify staff

  • Form Action: Apply to each
    • Note: The "Search for Users" action returns an array even though only one result was selected, so it is necessary to loop through the array with "Apply to each"
  • Select An Output From Previous steps: Dynamic Content → Search for users (V2) → "body/value"
  • Form action: Create item
    • Select appropriate SharePoint Site Address and List Name
    • Field names will populate from the list; fill with Dynamic Content from form response and/or user details
  • Form action: Send an email (V2)
    • To: [library inbox]
    • Subject: New Article Request from @{items('Apply_to_each')?['DisplayName']}
    • Body: 
      <p>
    @{outputs('Create_item')?['body/Title']} submitted a new request for an article.<br><br>
    Identifier: @{outputs('Create_item')?['body/Identifier']} @{variables('IdentifierLink')}<br>
    Citation: @{outputs('Create_item')?['body/ArticleTitle']}
</p>
    • Advanced options:
      • Reply To: @{outputs('Get_response_details')?['body/responder']}
      • Note: Setting this option means that when library staff open notification emails and click reply, the email reply will be addressed to the requester and not to the library mailbox