ClearCollect on a fresh collection receives a record, but thinks it is a non-record.

[Brazilian_Joe]

Hi, 

I am using Power Automate (formerly Flow) to query Cosmos DB and format the response for PowerApps. 

(I have used the same method in other  flows and it works)

In this one instance however, ClearCollect is refusing the response, thinking it is not a record. 

If I Set() the response to a variable however, it is recognized as a record. 

If I try: 

ClearCollect(asd_col,Upsert.Run("{""pkey"":""test""}"))

The code is underlined red, with the message
The function ClearCollect has some invalid arguments.

Cannot use a non-record value in this context.

I cannot execute the code, it stays with a red underline.

If I try Set, however:

Set(asd_var,Upsert.Run("{""pkey"":""test""}"))

It works just fine - asd_var is defined as a record just fine. 

My Power Automate response to PowerApps uses a schema auto-generated from a sample response, it is well-formed and the data returned conforms to it.

 

[v-yutliu-msft]

Hi @Brazilian_Joe ,

Maybe you could try this:

If(!IsBlank(Upsert.Run("{""pkey"":""test""}"))||
   !IsEmpty(Upsert.Run("{""pkey"":""test""}")),
   ClearCollect(asd_col,Upsert.Run("{""pkey"":""test""}"))
  )

In set function, if the variable is empty, it will not have error message.

While in clearcollect function, if the record is empty, it will have error message.

So I suggest you add an if statement to justify whether the result is empty record.

Best regards,

[Brazilian_Joe]

A few problems with the suggestion.

First, the Flow always sends a response. A well-formed response can be saved with Set() just fine, but ClearCollect refuses the same response. 

Second is that running Upsert three times for the same data (!!!) is not good, since there is a quota on number of Flow executions.  There are other server side automation that would happen whenever a document is upserted, and it would cascade triple triggers to other actions as well.  The flow should be run only once, and PowerApps should accept the response.

[DarkLiteOne]

I was wondering if it would be possible without doing multiple calls to the database/flow?

Something like this:

Set(SQLSiteVisitsDATA, 'PA>usp_view_SiteVisitsPlanned'.Run());
If (IsBlank(SQLSiteVisitsDATA), ClearCollect(Data, {}) , ClearCollect(Data, SQLSiteVisitsDATA));

Or even better something like this:

ClearCollect(SQLSiteVisitsDATA, Coalesce('PA>usp_view_SiteVisitsPlanned'.Run(),{}))

But this fails too... Is there a way to have just one call to flow and have a collection empty or populated? See my similar question here.

[Gadsweb]

Hello, I know it's been a long time but I had the same issue and after many hours I finally found the reason.

Depending on the result of the query (Resultsets, Table,...), when you use it as a sample to generate the model in the response, you can get a type of array or object.

 or 

You have to choose the right query result to generate an array, not an objet.

Hope this helps.