Re: Create Archive Button

StephenGW · ‎07-06-2021

Hello,

I'm wondering if there is a way to have my app take data from one table in an excel sheet and move it to another table in the same workbook? Basically I want to have a button that patches the first 900 records from one table to another then deletes those 900 records? Would this be possible? If so how would I write something like this? I can add more info too but I thought I would ask if the mass collecting, then mass patching, then mass deleting would even be possible first?

Thanks.

WarrenBelz · ‎07-06-2021

Hi @StephenGW ,

If you connect both tables, then they are no different from any other two data sources.

For adding, you can do a ForAll Patch and removing a RemoveIf. If your Delegation limit is set above 900, it should work. I will say in closing however if you have SharePoint available, consider moving to it - as a Database, Excel makes a good Spreadsheet . . .

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

BCLS776 · ‎07-06-2021

Yes, this is possible and you've got the basic structure figured out:

Read the records into a collection (Collect or ClearCollect)
Write them out to the archive data source (Patch or Collect, or ClearCollect)
If everything runs OK, delete the old records (Remove)

My big recommendation is that if you are dealing with that many records, Excel is a slow data source. A Sharepoint List is a noticeable jump better in performance.

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.

StephenGW · ‎07-06-2021

@WarrenBelz

I know a SharePoint list would be better in many ways but I struggle with images in the SP list. I don't want to use a Flow but I need to add images from the AddMedia button and then be able to view them in the gallery as well as edit them in the editform. I struggled with images in SP lists otherwise I think I would try and make the transition.

My problem is how to collect and patch only rows 2 through 902 and leave the rest, same with deleting. My delegation is set to 2000 and I want to Archive 900 of the rows when the count reaches a set number. It would be handy if I could always just add the oldest rows when the row count reaches a point. Maybe I'll look into that too?

Thanks.

StephenGW · ‎07-06-2021

@BCLS776

As I told Warren, I struggled in dealing with images an SP lists. Good to know I'm on the right track. I'll post back with more details as I play around a bit.

Thanks.

WarrenBelz · ‎07-06-2021

Hi @StephenGW ,

I have a blog on SharePoint images with a number of options and processes - I would urge to to consider SharePoint as Excel will come back and bite you in a great number of ways.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

WarrenBelz · ‎07-10-2021

Hi @StephenGW ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

StephenGW · ‎07-12-2021

@WarrenBelz

You are always very helpful and I appreciate that very much! I did get this to work using a Patch but performing this with thousands of lines of data was way to slow! I decided manually moving them over would be the best solution for the time being.

Thanks