Refresh on SharePoint list causing items to be added back to my collection

[Danbo_Fett]

Hello,

Odd issue here.

I have an 'undo' button on a screen with the following code for 'OnSelect' property:

Clear(colKPIUpdates);
Refresh('Scorecard-Priority Item KPI')

The expected behavior is that the collection items are removed and the gallery is reset to original data before the user started making changes.

The actual behavior I'm getting is that the collection is being cleared, but the Refresh command is causing the unchanged items from my SharePoint list to be added back to the collection.

Major high-five to whomever can help me with this issue!

Much appreciated,

Dan

[RandyHayes]

@Danbo_Fett 

First Refresh refreshes a datasource, it does not revert anything back to anything.  

Can you provide more details (perhaps a screenshot too) as to what you have and what is happening?

You mention the Gallery has unchanged items...so, a Gallery is a list of items.  What is unchanged vs changed?

Sorry for the questions, but you are obviously much closer to the workings of your app, so coming in cold is a challenge.

[Danbo_Fett]

Hi Randy,

Thank you so much for replying, it's very much appreciated.

I'll try to explain:

RE: attachment 'Correct result without refresh.jpg'

• collection starts out empty
• only code on the 'Undo' button is Clear(colKPIUpdates)
• after making a change in the gallery item, Undo button becomes enabled
• pressing the undo button executes 'OnSelect' code, which is to clear the collection
• result is that the collection is cleared, but the user's changes remain on the gallery item
• this is expected as there was no Refresh executed on the sharepoint list

RE: attachment 'Incorrect result with refresh.jpg'

• collection starts out empty
• code on the 'Undo' button is now Clear(colKPIUpdates); Refresh('Scorecard-Priority Item KPI')
• after making a change in the gallery item, Undo button becomes enabled
• pressing the undo button executes 'OnSelect' code, which is to clear the collection and Refresh the SP list and update the gallery
• result is that:
  • the collection may have been initially cleared
  • the user's changes are removed from the gallery item
  • somehow all the items in the gallery have been added back to the collection
• this is unexpected

Hope that makes sense

[RandyHayes]

@Danbo_Fett 

Okay thanks for clarifying.  I am not entirely sure of the use of a collection in this case, but, a collection, if cleared, will be cleared.  Refreshing a datasource does not repopulate that collection.  You must have some other place where you are pulling in that information.

Where is that collection defined and populated and how many places in your app?

And, is there a particular need for a collection on this?

[Danbo_Fett]

Hey Randy,

I think I need to use a collection (as opposed to a form) as I'm dealing with multiple record changes.

As per Reza Dorrani's video (https://youtu.be/8I0Pt_8I6k8), here are the other places where I use this collection:

1 - App OnStart:

ClearCollect(colKPIUpdates, ShowColumns (Table (Defaults ('Scorecard-Priority Item KPI')), "ID",

                                                                                        "Title",

                                                                        "KPI_x0020_Description",

                                                                     "KPI_x0020_On_x0020_Track"));

Clear(colKPIUpdates)

2 – On the 3 controls in my gallery item’s OnChange property:

If(ThisItem.ID in colKPIUpdates.ID,

Update(colKPIUpdates,

LookUp(colKPIUpdates, ID = ThisItem.ID),

{

    ID:ThisItem.ID,

    Title:txt_KPI.Text,

    KPI_x0020_Description:txt_Comments.Text,

    KPI_x0020_On_x0020_Track:tog_OnTrack.Value

}),

Collect(colKPIUpdates,{ID:ThisItem.ID,

                    Title:txt_KPI.Text,

                    KPI_x0020_Description:txt_Comments.Text,

                    KPI_x0020_On_x0020_Track:tog_OnTrack.Value}))

[RandyHayes · Accepted solution]

@Danbo_Fett 

How are you ultimately using that collection?  I see you are assigning them, but what are you ultimately using it for?  Collections are overused and not needed for most situations and, since they are variables that are snapshots in time, you have to constantly update and maintain them in your app...more work than you need and subject to problems.

I've briefly seen that video and again, the collection is overused as the Gallery already is your collection in that case.

BUT, be that as it may, if you are not familiar enough with the platform yet to work around that and the video is the guide for you, then I would suggest you stick with it and work it through as it will be a good learning experience. 

Now...here is where your problem is coming from...you have that redundant formula on the three controls in the OnChange action.

When you issue a Refresh on the datasource, your Gallery is then needing to update all of the values in it.  My guess is that you have the controls tied (via their Default properties) to the underlying data column.  So, when you Refresh, it is causing the Controls to "change" and thus your OnChange action is occurring, thus refilling your collection.

So there is where to look at the problem.

Personally, I would skip all of that and focus more on the submit action you are doing.  Your Gallery already contains all the data you need, putting it in a collection as well is just duplication.

This would involve skipping the collection as the datasource and going to the gallery to get the data you want.  But, I'd need to know more about your formula for submitting the collection you have now.

[Danbo_Fett]

@RandyHayes 

The gallery contains multiple rows with 3 fields (controls) each. From what I've researched, it's not ideal to submit each individual change to sharepoint every time, not sure why, payload, API call limitations...). I can't remember the article name, but it seemed legit, it was recommended to use collections. I'll defer to your knowledge and attempt to use a gallery or gallery control property to submit the user's changes. Wish me luck!

[RandyHayes]

@Danbo_Fett 

Yeah, not so legit in every case.  It's actually better to submit row-by-row.  Why...because ultimately you will have some formula that will submit everything in your collection.  

For example.  SharePoint has a 600 calls limit and that throttle gets renewed every 60 seconds.

If your user goes through the list and makes changes/new records, etc (Let's just say there are 700 rows), they will most likely NOT complete changing all 700 items in 1 minute.  As they change and updates are written back, every 60 seconds that throttle is reset.  Now...if instead we collect all 700 changes in a collection and then submit them all at once, that will hit the 600 limit and your app will go into a death-spiral for over a minute.

NOW...that is the "plus 1" for doing it as you go.  What I was suggesting in regard to having your final submit do it all at once based on the gallery, still would have that limit issue and ultimate death-spiral.   So, that limit being hit needs to be evaluated based on the amount of rows and actions you anticipate.

There are a variety of ways to go about what you are doing.  Some would be to simply update as you go (better to have a save button on each line for that rather than to update as any column changes - even though I know the SharePoint interface tends to prefer that method).  The other is to differ to the end with a save/submit button.  In that case, it would be beneficial to have something like a non-visible toggle in your gallery that would do a comparison to the original record vs the current changes, to indicate there is a change.  Then the submit action can simply filter the gallery for any changes and update those back based on the Gallery control values.

I've also done this with timers (client wanted to have this happen on a timer basis)...the timer would finish about once every 30 seconds and use the last mentioned action (find changes and submit).  Then, when the screen was left, again any changes were submitted.  

So...there are lots of ways to go about it and it is somewhat more of a design over function and some cases a function over design.

I hope that is relatively clear and helpful.

[Danbo_Fett]

@RandyHayes 

I was able to resolve the situation by ditching the collection and navigating through my gallery instead, thank you very much for your assistance, definitely learned a valuable lesson!

Dan