Answered

SQL Query: Collection is empty

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi guys,
I am trying to display the SQL-Query results in Power Apps using Power Automate to transmit the dynamic parameters (From Date and To Date) from Power Apps Form in SQL-Query and Collections to store the SQL-Query results. I followed the instructions here.

After submitting this form, the collection is created, the flow executes without any errors, but the collection become no data.
Investigation of my flow didn't help me, because the response step of my flow receive the body with data form SQL database.

OnSelect for submit button:

ClearCollect(sqlresult;MyFlow.Run(FromDate.SelectedDate;ToDate.SelectedDate))

SQL query in Power Automate Flow:

SELECT p.name, p.mobile 
FROM Person p
WHERE p.id NOT IN (SELECT h.personID 
 FROM Holiday h
 where h.fromDate >= '@{triggerBody()['SQL-execute_query']}'
 and h.toDate <= '@{triggerBody()['SQL-execute_query_1']}')

Respond

{
 "Table1": [
 {
 "name": "Anna",
 "mobile": "01992345678"
 },
 {
 "nameDevice": "Benny",
 "manufacturer": "01882345678"
 }
 ]
}

Does anyone have an idea, how to solve this problem?

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

Verified answer

Pstork1 69,490 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
The issue with this is that people usually try to return Table 1 as an object when what they need to return is the set of records inside Table 1. Take a look in the instructions at how he gets the schema for what is returned. You'll see that he is careful to select the array inside of Table 1 rather than Table 1 itself. The output of your Response should look like this. Yours includes Table 1:

[ { "name": "Anna", "mobile": "01992345678" }, { "nameDevice": "Benny", "manufacturer": "01882345678" } ]

Was this reply helpful? Yes No
jblacharczyk1 9 on at

Like (0)

Report
Copy link

Link copied!

I have a similar problem and tried following the instructions; but things have changed and there's no longer a "button" on the bottom "use sample payload to generate schema" so I'm at a loss. When I paste the formula
body('Execute_a_SQL_query_(V2)'.ResultSet.Table1 as noted in the vid I end up with only a single output string in the "test" collection. It has the data in there but power apps doesn't know how to display it...it's a long string of json.

Was this reply helpful? Yes No
Pstork1 69,490 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

I assume you are using the new designer. If so then the button mentioned previously is still there. Its just a link now. See the screenshot below.

If you need additional help please post it as a new question. You'll get more replies that way and others will be able to find an eventual answer easier also.

Was this reply helpful? Yes No
jblacharczyk1 9 on at

Like (0)

Report
Copy link

Link copied!

Thanks, I didn't think of expanding the "advanced parameters" area wherein the link resides. Anyway, got it fixed and working. Thank you again.

Was this reply helpful? Yes No