How to delete items in Sharepoint list by checking condition

[Hemasai1999]

Hi,

I have an app where I have list with below columns

ReqID    Training_Program Start_Date     End_Date  

1            java                       01/07/2021    14/07/2021

Here I have two Datepickers for start date and end date, on app start I need to write formula to delete the row where end date is less than today,

i.e -  I should have only specific records where my End_Date is greater than today().

Any suggestion would be helpful

Thanking in Advance

[RusselThomas]

Hi @Hemasai1999 ,

It sounds like you need RemoveIf()

Something like;

RemoveIf(Source, EndDate < Today())

Hope this helps,

RT 

[Hemasai1999]

Hi,

Thanks for response

I also want to delete items which don't satisfy condition from sharepoint1 and add same same items in another Sharepoint2

[RusselThomas]

Hi @Hemasai1999 ,

If we assume the above formula is applied to sharepoint1, then Items that don't satisfy the condition would be everything that is equal to or later than Today().  If you remove everything that is earlier than today, and everything that is later than today, then you are left with just today's items.  Is that what you're aiming for?

To add items, you would need to collect them first from the first list, then collect or patch into another list, then remove them.

Kind regards,

RT

[Hemasai1999]

Yes.

That is exactly what I want

1 -  I need to filter items which end date is below today.

2 - I need to patch to another sharepoint list(expired items)

3 - Then I need to delete from original Sharepoint list

[RusselThomas · Accepted solution]

Hi @Hemasai1999,

You'll have to adjust for your columns, but you can either collect it like this;

Collect(ArchiveList, 
   ShowColumns(
             Filter(OriginalList, EndDate < Today()), "Column1", "Column2", "Column3")
);

RemoveIf(OriginalList,EndDate < Today())

or you can patch it like this;

With({baseTable: ShowColumns(Filter(OriginalList, EndDate < Today()), "Column1", "Column2", "Column3")},
Patch(ArchiveList, baseTable, 
  ForAll(baseTable, {
    Column1: Column1,
    Column2: Column2,
    Column3: Column3
   })
  )
)

RemoveIf(OriginalList,EndDate < Today())

Because you're copying data from one list to another, (as opposed to editing data or creating data in the same list), you have to work with just the columns containing the data you want to copy.  You have to drop all the other SharePoint columns that tend to come along with it - hence the use of ShowColumns().

The patch function is constructed as a batch - there are other ways to do it, but if you have lots of data it would hopefully be more efficient in a batch.

Hope this helps,

RT

[Hemasai1999]

Hi,

It worked 😊

[Hemasai1999]

Hi, 

Collect(ExpiredTrainingList,
ShowColumns(
Filter('Training Request List', EndDate < Today()), "ReqID","Category","TrainingPrograms","AccountName","PracticeName","StartDate","EndDate","Title","Trainer_Name","Requester_Email","LP_Email","LPName","Mode","No_x002e_ofParticipants","FullDay_x002f_HalfDay","Weekend_x002f_weekday")
);

RemoveIf('Training Request List',EndDate < Today());

I wrote this formula it worked for me but thing is I also want to delete same record from another sharepoint at a time which have multiples dates with reqID.