Filter issue when creating collection

(0) Share

Report

Posted on by NewBee117

247

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

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

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

Any idea what I did wrong? Thanks.

Categories:

Building Power Apps

All responses (6)

Answers (0)

Sort by

Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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 247 on at

Like
a
(0)

Report
Copy link

Link copied!

Yes, without the filter, all data returns. data source is Azure SQL table. If this is a bug, how do I get help?
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Try this and see what it returns:
ClearCollect(col_UnitDetailsOriginalFiltered, Filter('[dbo].[com_UnitDetails]', ProductionUnitID = 195309, !IsBlank(Start) )
NewBee117 247 on at

Like
a
(0)

Report
Copy link

Link copied!

Returns nothing...

Result:
v-yutliu-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
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:

collection b:

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,
NewBee117 247 on at

Like
a
(0)

Report
Copy link

Link copied!

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

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