cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User
Super User

SQL (OnPrem) - Refresh Data Bug

Something not quite right with data gateway service.

 

Load SQL data into a collection

change data in collection

patch data back to sql and clear collection

refresh datasource

load data into collection again

 

the changes are not populated even though they have been patched and the data source refreshed.

In order to load the changes i am having to do

refresh datasource

collect

clear collection

refresh

collect

 

there is definitely a bug here can somebody please advise. its not right that the datasource is not refreshing until the 2nd collect. and ive tried multiple refreshes and it makes no difference

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: SQL (OnPrem) - Refresh Data Bug

Hi @Delid4ve 

Could you check if you've enabled the 'use longer data cache' option?

If so, could you try disabling it to see if it makes a difference?

 

image.png

View solution in original post

11 REPLIES 11
Dual Super User
Dual Super User

Re: SQL (OnPrem) - Refresh Data Bug

Collect() adds new records to the collection, even if the records already exist.  You should be doing just a Refresh and then a ClearCollect to get a new collection of records from the data source. I'm not aware of any easy way to update an existing collection with changes made to a data source other than re-building it.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Super User
Super User

Re: SQL (OnPrem) - Refresh Data Bug

it doesn't matter if i use clear/collect or clearcollect, unless i do it twice the updated sql records do not refresh.

Super User
Super User

Re: SQL (OnPrem) - Refresh Data Bug

Hi @Delid4ve 

Could you check if you've enabled the 'use longer data cache' option?

If so, could you try disabling it to see if it makes a difference?

 

image.png

View solution in original post

Super User
Super User

Re: SQL (OnPrem) - Refresh Data Bug

@timl  It is on at present, I’ll test shortly.

 

@Pstork1 i have to use clear and then collect as I’m using in forall as ‘in’ is not delegated.

this is the formula I have to run twice for it to work after patching/updating records:

 

Clear(Temporary_ClassificationLink_Equipment_General);
Clear(Temporary_ClassificationLink_Equipment_PAT);

Refresh('[dbo].[ClassificationLink]');

ForAll(Distinct(Equipment,ID),Collect(Temporary_ClassificationLink_Equipment_General,Filter('[dbo].[ClassificationLink]',ForeignID = Result And ForeignTable = "Equipment" And Archived = false And Deleted = false And Or(ClassificationID =500,ClassificationID = 148,ClassificationID = 149,ClassificationID = 150,ClassificationID = 554,ClassificationID = 556,ClassificationID = 557,ClassificationID = 558,ClassificationID = 547,ClassificationID = 549,ClassificationID = 550,ClassificationID = 552,ClassificationID = 884,ClassificationID = 886))));
ForAll(Distinct(Equipment,ID),Collect(Temporary_ClassificationLink_Equipment_PAT,Filter('[dbo].[ClassificationLink]',ForeignID = Result  And ForeignTable = "Equipment" And Archived = false And Deleted = false And ClassificationID > 807 And ClassificationID < 871)));

 

goobernoodles
Level: Powered On

Re: SQL (OnPrem) - Refresh Data Bug

Although I'm using a Flow to execute a SQL stored procedure to merge records into my destination database, I am also running into needing to refresh the SQL data source multiple (more like 3-4 times for me) times in some instances before new data that was successfully saved to the database to be returned to PowerApps.

Super User
Super User

Re: SQL (OnPrem) - Refresh Data Bug

@goobernoodles yep, even if you wait for the return to say it successfully executed. Somethings not right.

Im adding to a temp table (trigger on table so cant patch directly), running a stored procedure (flow new record) to update the correct table

 

at first I thought it was due to the stored procedure not yet being executed, however I tested by waiting a full minute and it still doesn’t update.  The only way it updates is to collect clear and then collect again, doesn’t matter how many times I refresh the source I have to collect twice

Super User
Super User

Re: SQL (OnPrem) - Refresh Data Bug

@Pstork1 @timl @goobernoodles 


So to show you exactly how it happens:

 

so I start with the field as Testing mk5 (top table is live sql and also bottom box)

D7A127F1-DE3E-4C75-A7F9-4832EE84FC4E.jpeg

I collect the data (table 2/3 and top 2 boxes) 2 is a temp collection for comparing data and 3 is what I am going to update from

 

D0CED135-2F04-4CA4-9C81-9F76ED026B0F.jpeg

I then edit the field in the collection (table 3) to mk6

 

47E451B7-AFDD-49DB-B472-4D06B6BB8EBC.jpeg

I then update the sql with the new data (upload) which clears the collections and as you can see the live data table has changed to the new value.
FD906AEE-5B47-4E9B-AFBA-C3183CE4FCC8.jpeg

I then collect the field again from sql however it gets the old value (unless I run collect/clear/collect)

 

463DD349-5352-4851-8B17-859352D836DA.jpeg


I don’t know where it is even getting the old value from as you can see that it has updated in sql from the live data table. Confused as &#!$

 

Super User
Super User

Re: SQL (OnPrem) - Refresh Data Bug

@timl Tested with it disabled and it solves it so there’s the bug 👍

Super User
Super User

Re: SQL (OnPrem) - Refresh Data Bug

@Delid4ve - glad that solves the problem for you.

@goobernoodles - maybe you could check the same setting to see if that helps you?

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,996)