cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
NewBee117
Level: Powered On

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
EricLott
Level 10

Re: Filter issue when creating collection

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.

NewBee117
Level: Powered On

Re: Filter issue when creating collection

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

EricLott
Level 10

Re: Filter issue when creating collection

Try this and see what it returns:

ClearCollect(col_UnitDetailsOriginalFiltered,
Filter('[dbo].[com_UnitDetails]',
ProductionUnitID = 195309,
!IsBlank(Start)
)
NewBee117
Level: Powered On

Re: Filter issue when creating collection

Returns nothing...

       5.PNG

Result:

       7.PNG

 

 

Community Support Team
Community Support Team

Re: Filter issue when creating collection

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.
NewBee117
Level: Powered On

Re: Filter issue when creating collection

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
thirdimage

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

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