cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate V
Advocate V

Filter issue when creating collection

Not sure what the problem is, but this works. col_UnitDetailsOriginal has data in all columns:

         1.PNG

But this does not. the collection only has data in one column.

         2.PNG

        col_UnitDetailsOriginalFiltered only has data in one column, where the rest of the data go?

         error.PNG

Any idea what I did wrong? Thanks.

6 REPLIES 6
Super User
Super User

I don't see anything wrong with your code. Any chance you have rows without data in your data source?

---
If this answered your question, please click "Accept Solution". If this helped, please Thumbs Up.

Yes, without the filter, all data returns. data source is Azure SQL table. If this is a bug, how do I get help?

 

3.PNG

Super User
Super User

Try this and see what it returns:

ClearCollect(col_UnitDetailsOriginalFiltered,
Filter('[dbo].[com_UnitDetails]',
ProductionUnitID = 195309,
!IsBlank(Start)
)

Returns nothing...

       5.PNG

Result:

       7.PNG

 

 

Hi @NewBee117 ,

Could you tell me the data type of the fields in your sql table?

I've made a similar test and I found that filter sql data will indeed cause the problem of displaying empty field.

But this problem is not for all data type.

I found that the char type field will not lose data, but others will.

Here's my test for your reference:

ClearCollect(a,'[dbo].[food_info]')
ClearCollect(b,Filter('[dbo].[food_info]',price=1))

 collection a:

11611.PNG

 

collection b:

11612.PNG

 

Only type in my test is varchar(50) type, its data retains.

 

As an alternative way, I suggest you filter based on the collection, then it will not occur this kind of problem.

ClearCollect(collectionname1,tablename);

ClearCollect(collectionnam2,Filter(collectionnam1,....))

 

 

 

Best regards,

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

This is the table structure in Azure SQL, it has three fields using nvarchar(50).

      8.PNG

My initial try was exactly what you suggested, to create collection1 and then filter collection1, however, I don't get any result after filtering. 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (8,809)