In this #PowerShot, I will show you how to set up a simple UI Flow for Desktop in Power Automate to sync data between excel online and MS Access DB. We will first create a schema for the Access DB and populate the database with data from an excel sheet stored in SharePoint.
Let's Get Started!
The first step to setup UI Flows is to check all the prerequisites as mentioned here.
We will be creating the UI Flow separately and then add it to a main flow in Power Automate to complete the automation scenario.
Flow 1 (UI Flow for Desktop)
Step 1: On the Flow, tab click on UI Flows (Preview) and click on New. Select the Desktop app and click Next. Provide a name for your flow and click Next.
Step 2: Set up input fields: Define all the input fields that you want to create in the Access DB and provide sample values to those. In our scenario, we are copying trade details data from excel to access and we have three fields: Customer Name, Commodity and Amount.
Step 3: Record steps: Now Click on the Record UI Actions and launch the recorder. Once the recorder is launched, click on the record button to start recording the screen actions.
Step 4: Once the recording has begun you can use the input parameters from the recorder pane and use them in the desktop applications (access DB in our case) and similarly copy output parameters to send to flow later on.
Step 5: Once the process is completed, save the recording by clicking on stop recording.
Step 6: Provide sample values and test the recording.
Flow 2 (Main Flow to get excel data and invoke the UI Flow)
Step 1: Trigger: Recurrence- I am using a recurrence trigger to trigger this flow every day.
Step 2: Action: List Rows Present in an Excel Table- To get all the rows from the excel table. Filter query to check the date of the transaction and get rows matching the filter criteria.
Step 3: Control: Apply to each loop: To iterate over each row from the excel table.
Step 4: Action: Invoke the UI Flow action, select the UI Flow created in the first section and provide the column wise parameters to copy data to Access DB.
In this post, we saw the UI Flows extension in Power Automate and how we can leverage those to sync data between an online source and an on-premise data source. This newly added capability of Power Automate enables easy LowCode, NoCode RPA implementations and eases creation of end-to-end automation scenarios.
I hope you found this interesting and it helped you!