A financial company uses multiple (50+) managed and cloud-based systems, from Office 365 and Salesforce to applications developed in-house and by third-party programs. For auditing and reconciliation purposes, they need to periodically collect the information about all the users in all the systems, together with permissions, and store it in the database. They use an internally developed web portal as a dashboard to reassign the roles and send notifications to the users.
- The solution must be able to collect users and permissions from all 50+ internal and external systems.
- The solution must be able to store the information in a database (full refresh).
- When the portal application updates the database, the solution must be able to send email notifications to all affected users.
In this tutorial, we will be collecting information from Office 365 and Salesforce only. The actual solution, developed by the team, collects it from all 50+ managed and cloud-based applications.
The Flow which collects users and permissions from Office 365
Step 1. Create a new Microsoft Graph API Connection pointed to the users endpoint.
Step 4. Create a Connection to the relational database with users.
Step 5. Create a Connection to the temporary database.
Step 6. Create a JSON Format with all default settings.
Step 7. Create a new Flow by selecting the
Web Service to database in the gallery. This Flow will be loading Office 365 users without roles into the database.
Step 8. Add a new source-to-destination transformation using Microsoft Graph API Connection for users endpoint (created in step 1) as a source and a database Connection as a destination. Configure per-field mapping if needed.
Step 9. Repeat step 7 and add a new source-to-destination transformation using Microsoft Graph API connection for roles endpoint (created in step 2) as a source and a temporary database Connection as a destination. Configure per-field mapping if needed.
Step 10. Repeat step 7 and add a new source-to-destination transformation using Microsoft Graph API Connection for members endpoint (created in step 3) as a source and a database Connection as a destination. Configure per-field Mapping if needed.
Step 11. Create a nested Flow with the following steps:
- The Flow that loads users without roles.
- The Flow that loads roles.
- The Flow that loads members of the roles (users with roles).
Step 12. Click the
pen button next to the Flow
Refresh Office 365 user for role and configure the database loop as shown below.
The Flow which collects users and permissions from Salesforce
Step 1. Create a new Salesforce Connection which uses the following URL to populate users and roles:
Step 2. Create a JSON Format with all default settings.
Step 3. Create a new Flow by selecting the
Extract data from the Web Service and load into the database in the gallery. This Flow will be loading Salesforce users into the database.
Now you can combine Office 365 and Salesforce Flow in a nested Flow.
The Flow which sends email notifications triggered by updates in the portal database
Typically, the portal database is getting updated when the user submits the data using a web form. It is easy to add an additional call in the submit action to the user-defined PUSH API.
Step 1. Create a user-defined PUSH API that will be receiving a payload from the submit action.
Read how to create a Flow that sends emails.