ClearCollect() returns partly empty column values from SQL

[HVAmend]

Hello,

I've tried a variety of workarounds and can not get this to work reliably.

I'm using small SQL tables to store values so they can be easily changed in one place and used across multiple apps.

(Talking around 3 columns with no more than 13 rows each).

In order to decrease load and improve performance, I cache these tables when starting an app in a collection.

For each of these tables, I run:

ClearCollect(
    TemporaryCollection;
    SQLTable
)

The tables essentially consist of these columns:

• ID char(3), primary key of the SQL table
• Readable description varchar(30)
• Value float

The readable description is used to show up in dropdowns, and selecting any of the options will set a corresponding variable to the ID (which is later used to look up the Value).

My issue is this:

All of these tables have blank values in the readable description column.

(Screenshot shows rows readable description, value, ID)

Comparison SQL Table:

(ID, readable description, value)

ver. 3.21071.26

Troubleshooting:

• Relinking the SQL database/refreshing the connector does not work
• Using Refresh() does not work

I'd be happy to hear any suggestions on how to work around this problem. 

[timl · Accepted solution]

Hi @HVAmend 

Calling ShowColumns should resolve this issue.

ClearCollect(
    TemporaryCollection;
    ShowColumns(SQLTable,"ID","ReaddableDescription","Value")
)

My recent post here provides a fuller explaination for this behaviour.

https://powerusers.microsoft.com/t5/Building-Power-Apps/ClearCollect-only-collects-data-to-one-colum...

[ismith0924]

I feel like maybe there's a type in this formula. I was getting the error: Invalid number of arguments: received 1, expected 2 or more. I fixed it by adding the SQLTable reference again:

ClearCollect(
    TemporaryCollection,SQLTable;
    ShowColumns(SQLTable,"ID","ReaddableDescription","Value")
)

[NdaLuz]

@ismith0924 You are right to think it is a typo, but it is not the typo you think, I think @timl is creating "TemporaryCollection", adding to it the result of the "ShowColumns" function.

The typo here is that, between arguments in a function, the ',' character should be used, and not ";", which is used to separate instructions. This is why the function "ClearCollect" thinks you only have one argument.

The corrected code should be:

ClearCollect(
    TemporaryCollection,
    ShowColumns(SQLTable,"ID","ReaddableDescription","Value")
)