Replicate in 8 steps

Posted by:

Jason

|

On:

|

Replicate-in-EightDownload

Power Automate to create data after query

  1. Trigger the flow
  2. Authenticate
  3. Extract Access token
  4. Assign Variables
  5. Do Until Loop
  6. Execute the Query
  7. Parse the results
  8. Create/Replicate the data item
  • Services and solutions are available and potential options:
  • Build flows to replicate your data
  • Trigger from a button
  • Trigger from a cloud event

How can you replicate data from a cloud system?

A new matter has just been created upstream, and you need to automate inserting the row/details locally for reporting purposes or building custom flows where the data needs to reside closer to home. Power Automate can accomplish this.

  • Concerned about processing hundreds, thousands, or even up to 100,000 rows?
  • Power Automate can handle this.
  • SharePoint can handle this.

Recently, I was engaged for a flow to accommodate such a scenario. To process 100,000 rows took about two hours to replicate, create the storage item in SharePoint, and perform additional tasks. But batches of 5000 rows happen pretty quickly.

Moreover, after you have your initial load, processing this many rows moving forward should not be a requirement. After all, you are only looking for your delta or net new rows.

Replicate data with a few steps:

  • Query with an HTTP step/operation
  • Iterate those rows until a condition is met
  • Execute the query until a ‘Do Until’ loop is complete

Why Replicating Data is a Great Option (with use cases):

  1. Custom Reporting: Replicating data into SharePoint or SQL allows the use of advanced reporting tools like SSRS, which may require a local dataset to function efficiently.
  2. Cross-System Integration: By replicating the data to a SQL table, third-party integrations can read this data without directly querying your cloud system, reducing system load.
  3. Data Archiving: Cloud data changes frequently, and sometimes you need to retain a snapshot of data. Replicating this data into a local system ensures you have a copy to reference later.

Step-by-Step Guide for the Power Automate Flow:

  1. Trigger the Flow: Start by triggering the flow from a button or Power App.
  2. Authenticate: Use an API connector or HTTP request to authenticate to the system. Store the authentication token securely.
  3. Save Values: Initialize and save the values needed, such as counters and the starting index of the data (e.g., the starting matter index).
  4. First Query: Make your first query using the HTTP action to retrieve the first batch of records based on the @odata.nextlink.
  5. Create a Do Until Loop:
  • Inside this loop, execute the query to retrieve batches of matters starting with the saved matter index.
  • Store the next link (OData.NextLink) for each iteration.
  1. Increment: Continue querying and incrementing the matter index or checking the next link value until the end is reached (OData.NextLink is null).
  2. Parse Data: Use a schema to extract the values needed from each record.
  • Let the flow fail once during setup, then copy and paste the schema generated from Power Automate for easy parsing.
  1. Save Data: Loop through each record and store the desired fields in your preferred data storage (e.g., SharePoint or SQL).

How to Create a SharePoint Hidden List:

  1. Navigate to your SharePoint site.
  2. Click on Site Contents > New > List.
  3. Name your list, then click on List Settings.
  4. Under Advanced Settings, set the list to Hidden. This ensures it doesn’t appear in the site navigation but remains accessible to your flows.

Begin the Workshop:

  1. From make.powerautomate.com, create a new flow.
  2. Select Manual trigger a flow if you want to run from the flow itself. Alternatively, if building a Power App, select the Power Apps option.
  1. Trigger the Flow:
    Create a step to run the flow. Click the Plus (+) on the canvas to add new steps.
  2. Authenticate:
    Best Practice: Save these items in a SharePoint hidden list or environment variable.
    Create a step with the HTTP action request:
  • Click the + and type “HTTP.” Select the operation from the list.
  • Choose the source to integrate. This is your list of Power Automate connectors that provide integration with many systems.
  1. Extract the Token:
    Add a Parse JSON step to parse the returned results and extract the token needed to execute the query. From the Body of the response, extract the token.
  2. Initialize Starting Point:
    Initialize and save values for counters and the starting matter index. Use the variable operation to store the starting index.

Steps for Do Until Conditions:

  • Using MattIndex Greater than a Variable:
  1. Inside the Do Until loop, set the condition: MattIndex <= {your upper limit variable}
  2. For each iteration, update the MattIndex by adding the batch size (e.g., increment by 5000).
  3. Continue processing until MattIndex exceeds the upper limit.
  • Using OData.NextLink is Null:
  1. Inside the Do Until loop, set the condition: OData.NextLink != null
  2. Query the data and store the OData.NextLink in a variable.
  3. For each iteration, execute the query using the saved next link and update the variable with the new OData.NextLink.
  4. Continue until the next link is null, indicating the end of the data.

Posted by

Jason

in