ClearCollect not working properly? Missing fields.

(0) Share

Report

Posted on by farhandsome

175

Hi all,

I've been having a continuous error with ClearCollect. For some odd reason, not all of the fields will populate. See below images:

PowerApps Collection Error.PNG

PowerApps Collection Error 2.PNG

As you can see, there is a discrepancy between what my SQL data is versus what the collection populates. The ClearCollect statement in my code does not filter or aggregate the data whatsoever, it just pulls in the table. Not sure why this is happening?

Thanks,

Farhan

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

Verified answer

PowerAddict 7,316 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

Can you share your ClearCollect formula? Also, in your App settings, what's the value for Explicit Column? Is it enabled?

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution.

Thanks!
Hardit Bhatia
The Power Addict
https://thepoweraddict.com

Was this reply helpful? Yes No
v-yutliu-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @farhandsome ,

Do you mean that your collection data and your sql data are different, for example: Material field doesn't have any value in your collection, but has value in sql table?

Could you show me your Collect formula?

I assume that the formula is like this:

ClearCollect(collectionname,sql table name)

//just to save all the data in sql table to your collection

If so, the reason why you met this problem is because of sql server connector's limit.

If a SQL Server data type is not listed in the table above, it is not supported.

Examples of unsupported SQL Server data types include: binary(), varbinary(), image, cursor, rowversion, hierarchyid, sql_variant xml, Spatial Geometry Types, Spatial Geography Types, and table.

The following data types cannot be used as query option predicates:

date

datetime

datetime2

smalldatetime

So please check the fields' data type whether is supported in PowerApps.

Here's a doc about SQL Server connector in details, please check sql's limit:

https://docs.microsoft.com/en-us/connectors/sql/

Best regards,

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @farhandsome ,
I have the same problem. Did you find a solution ?

Thanks
Best regards

Was this reply helpful? Yes No
TB-13091842-0 140 on at

Like
a
(0)

Report
Copy link

Link copied!

I have this same problem. It is not the Explicit Column Selection setting -- that is turned off.

My data source is SQL Server, and the procedure there returns the correct number of rows (47).

I use Power Automate to retrieve it (to get around implicit sharing), and its run history shows all the fields being returned.

Power Apps Monitor also shows all the fields being returned from Flow. The collection is created, and has the expected data, but four fields are missing -- only 43 are being collected.

Here's something that worked, though I can't say with certainty that it's the solution. I thought I had deleted and re-added the data source, but I'm not sure I did that before trying this.

I created a new app, and set up the data source and collection there. And it worked: it pulled all 47 columns.

I went back to the original app and refreshed the collection, but it still missed four fields. So I deleted the flow and then re-added it, and lo and behold, all 47 fields were there. Whether it was the new app or just the delete/re-add procedure, I can't say. Hope it works for you.

Was this reply helpful? Yes No