cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Problems with ClearCollect() and UpdateIf()

For some days we have massive problems with our apps

For data collected through Clear Collect , almost all values ​​in the columns are missing.

That's the query that suddenly stops working:

ClearCollect.png


I have already tried the following measures:

- Updated all data sources
- Code deleted and reinserted
- Explicit column selection checked (this was already disabled)

 


In addition, it was suddenly impossible to write records to the SQL database using the Update If  function. The system did not give any error message, but the data did not appear in the SQL table. Then I tried the PATCH () function instead and that worked. Nevertheless, I would like to know why suddenly Update If does not work anymore.

9 REPLIES 9
Highlighted
Community Support
Community Support

Re: Problems with ClearCollect() and UpdateIf()

Hi @PowerApp-AX2012 ,

Could you please share a bit more about the "ACTIVITYNUMBER" column? Where do you put your ClearCollect function?

Do you mean that the ClearCollect function could not collect records from your SQL Table?

Further, do you mean that the UpdateIf function could not update records in your SQL Table? Have you specified a PRIMARY KEY within your SQL Table?

 

Based on the needs that you mentioned, I have made a test on my side, and don't have the issue that you mentioned. Please make sure that the ThisItem.ACTIVITYNUMBER formula could return a proper value. Also please make sure you have specified proper SQL Table within your ClearCollect function as first argument.

Also please consider remove the SQL Table connection from your app, then re-add a new connection to your SQL Table, try your formula again, check if the issue is fixed.

In addition, you could also consider take a try with the following formula:

Clear(Agenda);;
Collect(
         Agenda;
         Filter('[DBO].[View_App_Activities]'; ACTIVITYNUMBER = ThisItem.ACTIVITYNUMBER)
)

then check if the issue is solved.

 

When you use UpdateIf function to update records in your SQL Table, please make sure you have defined a PRIMARY KEY within your SQL Table already. Also please make sure the first argument of the UpdateIf function is provided with a SQL Table data source rather than a SQL View. The standard formula format of the UpdateIf function as below:

UpdateIf(
         '[dbo].[YourSQLTable]',
         'Primary Key Column' = "Specific Primary Key Column value",
         {
             Column1: "xxxx",
             Column2: "xxxx",
             ...
         }
)

 

If the issue still exists, please consider re-generate a new app based on your SQL Tables data source, then try above formula again, check if the issue is fixed.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I
Helper I

Re: Problems with ClearCollect() and UpdateIf()

Hi @PowerApp-AX2012 

If this is a fairly new app and you are populating new tables I would suggest that you consider whether Delegation rules are playing a part in your issues. UpdateIf is not a delegable function therefore it could appear to be not working because it is not getting to the records that are greater than your app setting for "Data row limit for non-delegable queries". I know that this can have the appearance of working fine until your data grows, and then not working correctly.

Highlighted
New Member

Re: Problems with ClearCollect() and UpdateIf()

Hi v-xida-msft
thank you for your answer. I copied your questions and added my answers:


Could you please share a bit more about the "ACTIVITYNUMBER" column? Where do you put your ClearCollect function?
ANSWER:
The function lies on an arrow icon of a gallery (from the standard). Clicking on it opens the next screen, where I display the collection in another gallery. (I know that I do not have to cache the data as a collection and can display it directly, but I still use the collection elsewhere)


Do you mean that the ClearCollect function could not collect records from your SQL Table?
ANSWER:
No, the number of records is correct. But the values ​​in the columns are missing. Only the values ​​of the ACTIVITYNUMBER column are transferred to the collection. Of course I tried to call the table / view (View_App_Activities) directly in a gallery, which works. So it's not as if PowerApps could not retrieve the data.
It's just strange that the code has worked flawlessly until last week. I have not changed anything since.

 

Further, do you mean that the UpdateIf function could not update records in your SQL Table? Have you specified a PRIMARY KEY within your SQL Table?
 ANSWER:
Yes, the table has a PRIMARY KEY. At this point, the RECID is checked, which is automatically used as the primary key in AX2012.

UpdateIf('[dbo].[Table_ACTIVITIES]';
record.RECID = RECID;
{
DOCUMENTS: txtDocuments.Text
}
);;

Based on the needs that you mentioned, I have made a test on my side, and don't have the issue that you mentioned. Please make sure that the ThisItem.ACTIVITYNUMBER formula could return a proper value. Also please make sure you have specified proper SQL Table within your ClearCollect function as first argument.

Also please consider remove the SQL Table connection from your app, then re-add a new connection to your SQL Table, try your formula again, check if the issue is fixed.
ANSWER:
I tried that, but that did not change anything

In addition, you could also consider take a try with the following formula:

Clear(Agenda);; Collect( Agenda; Filter('[DBO].[View_App_Activities]'; ACTIVITYNUMBER = ThisItem.ACTIVITYNUMBER) )


then check if the issue is solved.
ANSWER:
I tried that, but that did not change anything
 
When you use UpdateIf function to update records in your SQL Table, please make sure you have defined a PRIMARY KEY within your SQL Table already. Also please make sure the first argument of the UpdateIf function is provided with a SQL Table data source rather than a SQL View. The standard formula format of the UpdateIf function as below:

UpdateIf( '[dbo].[YourSQLTable]', 'Primary Key Column' = "Specific Primary Key Column value", { Column1: "xxxx", Column2: "xxxx", ... } )

ANSWER:
I have compared my code with yours, but I do not see that I have something missing or wrong.

UpdateIf('[dbo].[Table_ACTIVITIES]';
record.RECID = RECID;
{
DOCUMENTS: txtDocuments.Text
}
);;


I am grateful for the detailed answer. So at least I rule out basic mistakes. Unfortunately, I have not solved the problem yet. Do you have any other ideas?

Best regards.

 

 

Highlighted
New Member

Re: Problems with ClearCollect() and UpdateIf()

Thank you very much for your hint. In fact, the amount of records has now increased significantly.

Highlighted
Community Support
Community Support

Re: Problems with ClearCollect() and UpdateIf()

Hi @PowerApp-AX2012 ,

Currently, I could not reproduce your issue on my side. Please consider re-store your app to previous version, then try your app again, check if the issue is fixed.

More details about restoring app to previous version, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/restore-an-app

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I
Helper I

Re: Problems with ClearCollect() and UpdateIf()

@PowerApp-AX2012 If you find the suggestion helpful, a Kudos would be much appreciated.

Highlighted
Advocate II
Advocate II

Re: Problems with ClearCollect() and UpdateIf()

Any updates on this issue?  Im running into the same problem.

Highlighted
Frequent Visitor

Re: Problems with ClearCollect() and UpdateIf()

@PowerApp-AX2012 , @v-xida-msft  Having the same issue. I think this is a bug.

 

When including a filter statement in the ClearCollect, most of the columns are empty.

If i remove the filter, all the columns have values.

 

found a workaround using showColumns and listing all columns i need explicitly.

 

Highlighted
Helper I
Helper I

Re: Problems with ClearCollect() and UpdateIf()

I am also having this issue - seems to be a bug. 

 

I will try explicitly naming columns with ShowColumns

Helpful resources

Announcements
Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,576)