Dynamic SQL Server query in Powerapps

0
votes

I have created a simple form in Powerapps which has a text input field called name and a data table which shows a list of all customers from a table called customer in a SQL Server database and I have also added a button labelled "Go" on the form.

What I want to do is:

  1. See a blank data table when I first open the form
  2. I would enter a customer name in the name text input field
  3. Click the "Go" button and then the value from the name field will be passed to the SQL Server database in a query which only returns the records which have the same name
  4. Display the results of the query in the data table.

How can I do this?

Thanks

1
sqlsql-serverpowerapps
Not familiar with PowerApps, but this blog seems to have everything you'd need to figure out a solution for your scenario: radacad.com/… - JohnLBevan
Thanks for the reply. Its mainly how to trigger a dynamic SQL query part that I am struggling with but the blog doesn't seem to cover that - user3165854
Does it have to use dynamic SQL given it's just a query against a user table? Presumably you just need to pass the username as a parameter? It looks like you may need Flow for that: powerusers.microsoft.com/t5/Using-Flows/… (sorry, not looked deeply as I've not touched PowerApps so aren't familiar enough to figure out what's useful) - JohnLBevan
Thanks. I'll take a look. I was hoping for it to be dynamic as the table could have millions of records on it and didn't want to pull them all into the table and then search - user3165854
That's cool - you shouldn't need to use dynamic SQL if your SQL statement is the same but you're just changing the username that you're filtering on; you only need a username parameter for that. - JohnLBevan

1 Answers

0
votes

Assuming you've been able to correctly add your on-premise SQL server as a data source:

You'll want to use a combination of Collect() and Filter()

Assign your user input to a variable using (this isn't strictly necessary)

GetContext({UserVariable: TextInput.Text})

Use a combination of Collect() to store the data you pull from MSSQL, and Filter() to, well, filter the data.

Collect(AppStorageTable1, Filter('[dbo].SqlTable]', ColumnName1 = UserVariable))

If you assign AppStorageTable1 as your data source for your data table, it should now appear. (Note, you'll have to declare/create it before it will appear as an option, but once you've used the name in Collect() it will appear as a data source).

EDIT: The term you likely were looking for is "delegable", a quick search will yield a few articles about it. The "Filter" function will pass the work off to your SQL server, so your app won't be responsible for processing/filtering the data.