Unexpected clearcollect behavior for SQL server

[FlowMohit]

Hi All,

I am not sure whether it's an expected behavior.

I have 2 table in ms sql server. One is called Protocol( Primary Key = Protocol_ID) and other is called LoE(Foreign Key = Protocol_ID). When I am trying to filter LoE items for a specific Protocol_ID using this formula - 

ClearCollect(
colLoEToBeCopied,
Filter('[dbo].[LoE]',Protocol_ID = ctxPLProtocolToBeCopied.Protocol_ID
))

My collection looks like this(Columns are returned as empty) -

What is wrong here?

[timl · Accepted solution]

Hi @FlowMohit 

The first thing I would check is the 'Explicit Column Selection' setting under File > App Settings > Advanced Settings.

If this is enabled, could you try turning to off see if it makes a difference?

[v-yutliu-msft · Accepted solution]

Hi @FlowMohit ,

Please try this formula:

ClearCollect(
colLoEToBeCopied,
Filter('[dbo].[LoE]',Protocol_ID in ctxPLProtocolToBeCopied.Protocol_ID
))

// ctxPLProtocolToBeCopied.Protocol_ID is a table, you need to use "in" to compare a value and a table.

Best regards,