cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Filtering a Table based on a Collection

Some help much appreciated!

 

I have a gallery with the contents of an SQL table (List of Vehicles with a primary key of FleetID).

In the gallery I have a checkbox which adds the FleetID to a collection called SelectedVehicles_Test when checked and removes it when unchecked.

 

Outside the gallery I have a check box which when selected is supposed to filter the gallery to only those vehicles selected with the checkbox ("Show Only Selected")

 

In the Items row of the gallery I set them with an If statement which works fine however I cannot seem to get the table to filter to show any of the items which are in the collection.

Filter('[dbo].[Vehicle]',FleetID = First(SelectedVehicles_Test).FleetID)

 

I have numerous delegation problems with the filter as written, which I'm sure I will have to deal with at some point, I'm just mystified why it can't filter the table based on *any* value in the collection (hence the First filter on the collection)

 

I can see that SelectedVehicles_Test.FleetID is returning a number type which is the same type as the FleetID in the Vehicle table.

If I replace the First(SelectedVehicles_Test).FleetID) with a hardcoded value it's fine and if I put the First(SelectedVehicles_Test).FleetID as a Text identifier in a label it returns the FleetID of the first record of the collection.

 

Is there a way to do this, and preferably if someone could suggest a method which removes the delegation issue I'd be really grateful!! 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Filtering a Table based on a Collection

Hi @Sincilbanks ,

Do you want to filter your Gallery Items based on the check box outside the Gallery?

 

Based on the formula that you mentioned, I think there is something wrong with it. I have made a test on my side, please consider take a try with the following workaround:

Set the OnCheck property of the Checkbox within the Gallery to following:

Collect(SelectedVehicles_Test, ThisItem.FleetID)

Set the OnUncheck property of the Checkbox within the Gallery to following:

RemoveIf(SelectedVehicles_Test, Value = ThisItem.FleetID)

Set the Default property of the Checkbox within the Gallery to following:

If(
   ThisItem.FleetID in SelectedVehicles_Test.Value,
   true,
   false
)

 

Set the Items property of the Gallery to following:

Filter(
       '[dbo].[Vehicle]',
       If(
          OutsideCheckBox.Value = true,
          FleetID in SelectedVehicles_Test.Value,
          true
       )
)

Note: If the FleetID is a Number column in your SQL Table, above formula may cause a Delegation warning issue. As an fixed solution, you could consider change the Data type the FleetID column from int to varchar, then above formula would be delegable.

 

In addition, if the amount of your SQL Tables records is not more than 2000, you could ignore the Delegation warning issue on your side. If the amount of your SQL Table records is more than 2000, you could consider bulk-load the table records into collection in your app, then use the collection as data source in your app.

Please check and see if the following solution would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M7...

 

Best regards,

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

View solution in original post

4 REPLIES 4
Highlighted
Frequent Visitor

Re: Filtering a Table based on a Collection

Ok so the issue is the delegation. Looking at the monitor and reading the JSON I can see it's only pulling back the 2000 limit from the Vehicle table, I suspect that's a misunderstanding on my part on how delegation works, I assumed because the delegation marker was against the Collection side it was limiting the Collection and not the table...my bad...

 

I guess the question now is, is it possible to perform this operation without delegation?

Highlighted
Community Support
Community Support

Re: Filtering a Table based on a Collection

Hi @Sincilbanks ,

Do you want to filter your Gallery Items based on the check box outside the Gallery?

 

Based on the formula that you mentioned, I think there is something wrong with it. I have made a test on my side, please consider take a try with the following workaround:

Set the OnCheck property of the Checkbox within the Gallery to following:

Collect(SelectedVehicles_Test, ThisItem.FleetID)

Set the OnUncheck property of the Checkbox within the Gallery to following:

RemoveIf(SelectedVehicles_Test, Value = ThisItem.FleetID)

Set the Default property of the Checkbox within the Gallery to following:

If(
   ThisItem.FleetID in SelectedVehicles_Test.Value,
   true,
   false
)

 

Set the Items property of the Gallery to following:

Filter(
       '[dbo].[Vehicle]',
       If(
          OutsideCheckBox.Value = true,
          FleetID in SelectedVehicles_Test.Value,
          true
       )
)

Note: If the FleetID is a Number column in your SQL Table, above formula may cause a Delegation warning issue. As an fixed solution, you could consider change the Data type the FleetID column from int to varchar, then above formula would be delegable.

 

In addition, if the amount of your SQL Tables records is not more than 2000, you could ignore the Delegation warning issue on your side. If the amount of your SQL Table records is more than 2000, you could consider bulk-load the table records into collection in your app, then use the collection as data source in your app.

Please check and see if the following solution would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M7...

 

Best regards,

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

View solution in original post

Highlighted
Frequent Visitor

Re: Filtering a Table based on a Collection

Thanks so much for looking at this..

 

I have the checkbox in the gallery set to add and remove from the collection already and that works fine as far as I can see,  unfortunately my vehicle table is well in excess of 5k records.

 

FleetID is my Primary key in my SQL table and is an autogenerated ID by the table, so I can't really change it (and wouldn't want to to be honest), I do have the customer fleet identifier which is an nvarchar so I'll look into filtering by that field instead, if that works then that's a reasonable workaround I suspect...

 

You are right, I want to filter the gallery using the checkbox which is outside the gallery. (I have a number of filters as headings at the top of the gallery, and the aim was to allow the user to filter by some criteria, select some records, filter by some other criteria and select other records, then present all the selected records to apply a common action against them.)

 

I'll crack on and see how I get on...

Highlighted
Frequent Visitor

Re: Filtering a Table based on a Collection

So filtering on FleetID and Fleet_Number (which is nvarchar(50) type in the db) both bring up a delegation issue. It looks like I can't attack this in the way I want without preloading the records into a collection (presumably at App open). This also means when I patch the records I have to patch the collection and their related table values at the same time to keep them in synch?

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (6,070)