Business Requirements
The Quality Department had created a form on the company’s Sharepoint to record quality issues received from various channels - customers, sales reps, customer services etc.
This form required a customer lookup to assign the quality issue to.
The customer data resided within an on-premise SQL Server database.
A Sharepoint List was needed that could be used as the source list for the custoemr field on the form.
The List needed to be initialised with the existing active list of customers from the database.
Subsequent updates would be necessary to update the customer list with new customers that were added to the on-premise database.
These updates should be automated according to a schedule or an event.
Technical Requirements
-
Sharepoint - Microsoft’s collaboration and document management platform.
-
On-Premises Data Gateway - to enable data transfer between on-premises data and several Microsoft cloud services, including Power Automate.
-
Power Automate - Microsoft’s workflow and business process automation platform.
-
Licencing - The SQL Server Connector is required to connect to a SQL Server database. This is a premium level connector and therefore requires a Power Automate Premium licence. From Microsoft’s own pricing documentation, other licencing scenarios are an option (Copilot Studio, certain Dynamics 365 licences).
On-Premises Data Gateway
Microsoft’s Reference documentation.
Microsoft’s Architecture documentation.
This requires .Net Framework 4.8 to be installed on the server the gateway will be installed on - a server restart will be required.
The List
Before you can populate the list, you will need to create it.
Within the site on Sharepoint that you will be using the List, click:
New => List => Blank List => Name it => Create.
As the list I am creating is for a lookup list, I will be requiring two pieces of data from the Customers table => account code and name. Just like an HTML select element, these will represent the value and display text respectively.
So I click the dropdown link in the column header called Title, Column Settings => Rename to rename it AccCode. I then click “+ Add Column” in the next column and name it Name.
The Title column is a “special” column in Sharepoint lists.
Using it the way I have described above is totally acceptable, but it can only be done for text type values.
See some discussion aboit it here.
Creating the new Flow
Harking back to Power Automate’s original name, Flow, we create a new scheduled cloud flow.
For draft 1 of this flow, here are all of the actions, formatted as:
Connector Name - Action Name - My custom name for the action (where applicable):
- Schedule - Recurrence
- Sharepoint - Get Items - Get existing customers from list
- Control - Apply to Each - For Each Customer
- Sharepoint - Delete Item - Delete Customer From List
- SQL Server - ExecuteProcedure_V2 - Execute Get Customers Stored Procedure
- Data Operations - Parse JSON
- Control - Apply to Each - For each Customer From Database *
- Sharepoint - Create Item - Create Customer In List
*A gotcha to watch out for in the second Apply to Each action (renamed as “For each Customer From Database”), is the need to modify the action’s default previous step output when you select the Body Table 1 output from the Parse Json action.
As it is, the output will generate a null value for the action:
You will need to manually paste in the altered reference to the table within the JSON body:
Save and Test the flow.
Browse to your List in the Sharepoint site you created it in and you should see the data from your on-prem database listed.
This List is now available as a new field in forms that are created in Sharepoint:
Duplicate List Items due to Get Items Gotcha
Not long after you release this List to your “power” users, you will get an email (or a walk-in) to tell you that the list is showing duplicate items.
This is due to the default configuration/limitation of the Get items action that means it will only retrieve 100 items.
The solution to the issue, detailed here, is to up the limit to a value greater than the number of items in the list (with a current maximum of 5,000 items).
