Delete sharepoint list record and add it on another list

[Elitezone]

Hello,

In my app I have one sharepoint list that stores all records.

I recently added a status field, because my organisation started to work around some records and need to update them.

I received a requirement to "Hide" some records with status Done/Rejected and allow them to show only when user click a button.

I already have a tons of filters in my gallery. I thought that this will not be a good idea to make it even bigger (also regarding performance because without filters we already have 600 records).

So my idea is to create a archive sharepoint list that have all records that statuses were changed to Done/Rejected.

I currently allow admins to change status in edit form, and when they finish they click button:

If(varAdmin && varAdmin1, SubmitForm(Form4_1);UpdateContext({showDialog:false});Notify("Propozycja została zmieniona!",NotificationType.Success),Notify("Brak uprawnień!",NotificationType.Error))

varAdmin && varAdmin1 (first is admin rights onStart, second is admin toggle on), showDialog is a confirmation Box with Yes/No.

Is there a way to verify if Status field in Form4_1 has values Done/Rejected and if yes, then make a deletion from one list and add to another list. Seems like Remove function and Patch would work.

I know that I am asking a lot, but please guide me, as this is no trivial task for me - I tried to use flow, but failed.

I found this function in Internet but cannot implement:

Patch(
    '[PowerAppsTest].[deleteme20190726b]',
    Defaults('[PowerAppsTest].[deleteme20190726b]'),
    {
        Name: DataTable1.Selected.Name,
        Age: DataTable1.Selected.Age,
        IsHuman: DataTable1.Selected.IsHuman
    });
Remove('[PowerAppsTest].[deleteme20190726a]', DataTable1.Selected)

My main Sharepoint List is IdeaBox_1

My archive Sharepoint List is ArchiveIdeaBox_1

They both have same fields structure (Archive is a copy).

Status is a field in both of them and it is a combobox with 6 values New->Analysis>In progress->Done->Rejected->Suspended

Thank you.

[BrianS]

You can check here:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-clear-collect-clearc...

Normally you use Collect to create a local collection with the app, but it can be used with Sharepoint and other sources. 

I forgot one thing when I gave you that code - sorry!

I have an app that is used to reconcile credit card charges. Once the charges are reconciled, they are moved to another SP list - similar to what yo are doing. I creaed a colelction to hold the reconciled charge and then moved all those at one time. You could do the same, even if you only do one at a time. Here is the code I used:

ClearCollect(col_Rec,Filter(col_Entries,Reconciled));

Collect(CC_Reconciled_Expenses,

ShowColumns(col_Rec,"Title", "Card_Issuer_Acc", "CreditCard","Vendor","Payee","Description","Transaction","Inv_Date","Job","Phase","Status","Approver","Reconciled","ReceiptImage","ApproverEmail","Total","Category"));

I added entries to the collection (col_Rec) based on the Boolean Reconciled. Then I sent all those records to the SP list CC_Reconciled_Expenses at one time with the Collect command.

You could do something similar and let your admins mark several records to be moved at one time, or you could just add one.

This will keep you from needing to "disambugate" the fields. Otherwise you can use Patch, but you will need to figure out how to correctly designate the fields. You could temporarily rename the fields in one list using RenameColumns - it will only effect the names during the session. That command is listed in the documentation as well. 

[Elitezone]

@BrianS 

With my level of knowledge it is impossible to reproduce the results.

I barely started with PowerApps.

If("Odrzucona" || "Zrealizowana" in SelectedRecord.Status.Value,ClearCollect(IdeaBox_1,Filter(Col_1.SelectedRecord));
Collect(ArchiwumIdeaBox,ShowColumnn("Nazwa","Opis","Kategoria","Podstawa_x0020_prawna","Zmiana_dotyczy","WhoLiked","Author","Created","Modified")))

I have no idea how to create collection with this data.

We can close the topic, as I cannot finish the work. 

Thanks for help.

[BrianS]

Don't give up! If yo are new to all this I would suggest starting here:

https://www.youtube.com/c/ShaneYoungCloud/videos

Shane has a large number of videos on a range of issues in Powerapps. They are easy to follow. You may want to go to the bottom of his list and watch a few of the early ones to get a good background, then you can look at some of the specific topics to get a good background.

[Elitezone]

I am watching a lot of his tutorials.

I also read Microsoft tutorial and will join fundamentals course soon.

But I am not from IT, I was forced to transfer to the team that works in PowerApps and I have no IT background knowledge - so I need to catch up in a lot of matters (Excel, SQL, Sharepoint, 365 overally, Azure, Flow etecera).

This matter is very hard for me so If anyone would be willing to help me - by making a full example with my data I would be very grateful. So far I was not succesful in creating any function that would delete a record from one list and create similar record on another.

[BrianS]

I don't know if anyone would be able to write the app for you, but I can give you a few more ideas. You may want to create a separate screen for the Admins to move these items to the archive list. On that screen use a Gallery to select the item you want to move. Create a form that uses the Archive list and have each data card default to the data from the original list. In each Default property just reference the selected item in the gallery instead of Parent.Default:

Gallery1.Selected.Field.Value

Then you can just do a SubmitForm and it will write the data to the Archive list for you. Then on the OnSuccess of that form you can do the Remove from the original list by doing a Lookup with Gallery.Selected to get your Record value

[Elitezone]

@BrianS 

Seems like a great idea - and might be doable for me, but requirement was that this would be automatic process when someone submit a form that has a value in Status form "Done","Rejected" when submitting the form.

So when admin pick it and submit it automatically move it to different sharepoint list, and this list is the basis for archive.

This solution would force people to move through one more screen and it would be illogical from business side, because you have one form that allow you to change status, and you would need another that would work when you change a status to certain value. My business would be really confused.

The community here is great. They helped me to fix much harder problems and develop apps (believe me everyone is so patient with me that's unbelivable), and imho this problem is about formatting two functions - not writing app for me. Probably a few minute of work for someone who has a knowledge.

Thanks to this community I still have a job! 😄

[BrianS]

If you are always moving those items to the Archive list it should then be straight forward, but I was missing an easy way to get the correct data to your Archive list. In the OnSuccess of the form, do the check for Done or Rejected. If it is true use the values in your variable SelectedRecord in your Patch

Patch(ArchiveIdeaBox_1, Defaults(ArchiveIdeaBox_1),

  { YourField1:SelectedRecord.YourField1.Value, YourField2:SelectedRecord.YourField2.Value, ..}

);

If( SelectedRecord.Status.Value="Done" Or SelectedRecord.Status.Value="Rejected", Remove(IdeaBox_1,SelectedRecord))

Again - I would leave out the Remove until the copy to the archive list is working

[Elitezone]

@BrianS 

Ok in my case it worked when I deleted Value:

Name:SelectedRecord.Name

This syntax was OK.

If( SelectedRecord.Status.Value="Rejected" Or SelectedRecord.Status.Value="Done",
Patch(ArchiwumIdeaBox, Defaults(ArchiwumIdeaBox),

  {Name:SelectedRecord.Name,Desc:SelectedRecord.Desc,Category:SelectedRecord.Category,'Legal Change':SelectedRecord.'Legal Change',Concerns:SelectedRecord.Concerns,WhoLikedPeople:SelectedRecord.WhoLikedPeople,Status:SelectedRecord.Status}

));

 There are however few problems.

I cannot copy created,created (these are server generated fields) by and I cannot copy attachments.

Is there any way?

Seems like I will have to let those done/rejected propositions stay on the list, and only filter default view so they won't be visible.

This Author/Attachments issue is a huge turn off.

[BrianS · Accepted solution]

Author and Attachments are not simple fields, they are records, so the syntax will be a little different. I do not know, off the top of my head, what that is. You could hunt through Shane Young's Youtube videos - I believe he addressed that at one time. You may also be able to find it in the documentation.

[BrianS]

You can also try adding a "." after what you have know and see if the program provides any suggestions