What is the OPTIMAL way to ingest JSON data into PowerApps

[jzia93]

Hi Power Automate Community,

I have a requirement to deploy a managed solution with a data dependency, so am exploring options for allowing users request a data import from a centralised Azure SQL instance into their PowerApps environment.

The flow I am using is as follows:

1. User imports the solution into their tenant/environment
2. User triggers data import using a button in Power Apps - this triggers a Power Automate Flow
3. Power Automate calls my Web Service with an authenticated GET request
4. Web service runs some logic to export SQL data as ODATA JSON payloads
5. The body of the response to point (3) is an array with the SQL Data.
6. Power Automate parses the array to JSON and writes the data into CDS in the current environment.

This works, but I've noticed that it takes several minutes to complete the flow when even a few hundred records are involved.

I also am not currently checking 'if record exists' - which would seem like a sensible caveat for any bulk data imports.

I understand there are other methods to import data into solutions, but am I missing something to optimise my flow or approach?

Thank you

[alrez]

Hi,

Looking through your logic I would think that what you have set up should be fine. I would have to see the run history and see exactly what is taking so much time. I would recommend create a case with Microsoft Support to help delve into this. I'll include a link below; on creating a case with them if you would like to go down that route. Otherwise I would recommend posting your Flow run and communities can help you out.

If you would like to create a ticket with Microsoft Customer Support here is a link on how to do so: https://docs.microsoft.com/en-us/power-platform/admin/get-help-support

Regards,

Alex

-------

Community Support Team _ Alex Rezac
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jzia93]

Thanks Alex, I'll create the ticket.

RE: Flow run

The flow is below. 

• Upon triggering the flow (this will be called via a custom ribbon command in a Model Driven App), an HTTP GET request is sent to a web service
• The web service extracts and concatenates a number of SQL queries into a JSON array, with the body containing:
  • Modules (<10 records)
  • Categories (21 Records)
    • (Lookup field to Module)
  • SubCategories (82 Records)
    • (Lookup field to Category)
  • Statements (500 Records)
  • SubCategory - Statement Mappings (2500 records)
    • (Lookup fields to Statement AND SubCategory)

Flow then does the following:

• Parse the main JSON object into 4 sub-arrays, this takes no time at all but helps with duplicate column names in SQL ('id' for example).
• Create records in the entities (see below)

Main flow:

[Kikoger · Accepted solution]

Hi jzia93,

the solution with Flow you describe is the only method at this time to get JSON data "as-is" into PowerApps.

Just like you, that was too slow for us and we did not want to eat into our Flow quota each time someone wants to get some data into PowerApps. So, call me crazy, but we actually 'developed' a proprietary data format that is saved in the DB as a string and that PowerApps can convert to collection on its end. For example:

JSON:

[
{
   "property1": "value1.1",
   "property2": "value1.2",
   "property3": "value1.3"
},
{
   "property1": "value2.1",
   "property2": "value2.2",
   "property3": "value2.3"
}
]

Proprietary Format:

value1.1/<value1.2/<value1.3/#value2.1/<value2.2/<value2.3/#

Formula in Power Automate:

SortByColumns(AddColumns(FirstN(Split(jsonString , "/#"), CountRows(Split(jsonString, "/#"))-1), "property1", First(Split(Result, "/<")).Result, "property2", Last(FirstN(Split(Result, "/<"), 2)).Result, "property3", Last(Split(Result, "/<")).Result, "property1", Ascending)

As you can see, the property names are not included, and this therefore only works with data in a pre-defined format. It's really annoying to make changes and I really hope for PA to soon include a way to read JSON from a passed string. Also, that way, my co-workers won't think I'm a nut anymore 😅

I by no means recommend doing this, but, if like for us it becomes it the only option, well.... good luck!!

Max 

[hemrhk]

Using the Respond action this can be achieved and with help of formulas we can construct back to the collection again in PowerApps.

Send JSON to Canvas App from Flow

Subscribe to 365 Stack for instant updates