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

Filtering on a text field from a collection rather than a data source works differently?

I have a SQL Server data table which I am using as the source for a collection.

When I try and filter the collection by one of the fields it appears to need trailing spaces added to find the record. When I use the original data source it doesn't.

 

In my full program when I patch a status record into the collection it doesn't appear to add the trailing spaces into the record, it only appears to be when the collection is created from the source database?

 

I've tested this using the following code on a button to create and filter the collection

ClearCollect(TestVehicle, Filter('[dbo].[Vehicle]', FleetID =51063));
ClearCollect(TestDepotHistory, Filter('[dbo].[DepotHistory]', FleetID=51063));
ClearCollect(TestDepotHistoryDepotName,AddColumns(TestDepotHistory,"DepotName",LookUp('[dbo].[Depot2]',DepotID=TestDepotHistory[@DepotID],Depot)));

 

When I put a data field up and try to show the data filtered on "Assignment Status" from the collection requires me to put "Current   " into the filter to show it.

 

When I change the datasource of the filter to the SQL table it will filter using "Current"

 

I have another text field in the table which is a user name, this field appears to filter correctly for both the collection and the source data table.

 

Anyone have an explanation for me?

 

I've set both of the text fields to Nvarchar in the SQL DB, I don't think it's a coincidence it's Nvarchar(10) for the Status field and "Current" plus 3 spaces = 10.

I've run a Rtrim command over the field in the db to ensure there are no trailing spaces there.

 

I've attached some screenshots if that helps...

2 REPLIES 2
v-jefferni
Community Support
Community Support

Hi @Sincilbanks ,

 

After doing a small test I can confirm it's not related to the field type.

 

I can see your sql server table name still with the prefix "dbo" while since a few months ago the new naming convention going forwards is to not include the dbo prefix. Please try remove the connections and add those tables again into Power Apps.

 

If this issue persists, please check the versions of SSMS and other components, mine are as follows:

 

vjefferni_0-1629367196198.png

 

Hope this helsp.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

 

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

Sincilbanks
Frequent Visitor

Hi,

 

Thanks for the info.

 

My SQLSMSS is an older version it would appear (why would that make a difference though?) and I have 30-odd tables to remove and replace which will take quite some time and take the app down for the duration as well. I'll have to try and find a slot to get that done when no-one is using it.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Carousel_PP_768x460_Wave2 (1).png

2022 Release Wave 2 Plan

Power Platform release plan for the 2022 release wave 2 describes all new features releasing from October 2022 through March 2023.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Top Solution Authors
Users online (3,032)