cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
escapethisscree
Helper I
Helper I

Collecting SQL data with a filter

Hello PowerApps commmunity,

I am fairly new to PowerApps and am having difficulties understanding where I go wrong in my logic here.

Case:

I have a table of ~100 rows and I would like to collect some of these rows and place them in a collection OnStart.

 

In SQL terms what I am trying to achieve is as simple as:

 

 

SELECT *
FROM DB.Table
WHERE [ID] LIKE 'Default%'

 

 

 

But I am having an embarrassing amount of difficulties in achieving this.

I tried this simple code:

 

 

ClearCollect(col_DataDefaults,
             Filter('DB.TableName', "Default" in ID) 
);

 

 


This return only the rows I need - so far so good.
I also get all of the columns in the table - great.
BUT, all columns except for the ID columns that I am filtering on contains blank values.

From what I understand about delegation, using something like AddColumns() is not sustainable because of delegation, is this correct?

I need to pull these specific rows to use as default values in various locations in my app.

Thank you in advance for any help

1 ACCEPTED SOLUTION

Accepted Solutions
timl
Super User
Super User

@escapethisscree 

'Explicit column selection' is the reason why the columns other than ID contain blank values. This is a performance optimisation by Power Apps, which is a good thing. You can retrieve the other columns that you require by calling ShowColumns:

ClearCollect(col_DataDefaults,
            ShowColumns(Filter('DB.TableName', "Default" in ID),
                        "ID",
                        "ColumnYouWant1",
                        "ColumnYouWant2",
                        "ColumnYouWant3"            )
);

View solution in original post

6 REPLIES 6
escapethisscree
Helper I
Helper I

I tried to step the query like this:

 

ClearCollect(col_DataDefaults, 'PowerAppDev.Konfig_tags');


Filter(col_DataDefaults, "Default" in ID)

 

But I still end up with the same result where I the correct data in the ID column but the rest of the columns remain blank.

I must be missing something simple and obvious here.

Drrickryp
Super User
Super User

@escapethisscree 

You could do the filtering in Sql and create a view to use in PowerApps.

Hello Drrickryp,

Thank you for your message.

Writing a query that can be delegated would also result in data being filtered in SQL.

I realize I can create views but am afraid of the amount of views I will end up with ultimately.

There must be a way to pass this simple query along to the DB? I can do it without any issues in PBI or Automate. It's a super simple filter it makes no sense if I cannot delegate this to backend. 😞

timl
Super User
Super User

@escapethisscree 

'Explicit column selection' is the reason why the columns other than ID contain blank values. This is a performance optimisation by Power Apps, which is a good thing. You can retrieve the other columns that you require by calling ShowColumns:

ClearCollect(col_DataDefaults,
            ShowColumns(Filter('DB.TableName', "Default" in ID),
                        "ID",
                        "ColumnYouWant1",
                        "ColumnYouWant2",
                        "ColumnYouWant3"            )
);

escapethisscree
Helper I
Helper I

Thank you @timl for both the example and taking the time to explain this to me.
It is now working and the behaviour is both logical and preferred now that I understand it.

timl
Super User
Super User

You're welcome @escapethisscree 

Just in case it might be of help to you, the Power Apps StartsWith function enables you to do the equivilent of LIKE 'Default%' in SQL.

The in operator that you're currently using will be doing a LIKE '%Default%' 

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (1,728)