Delete Missing Items not ALL Items

[Phineas]

I can't get the following to work as I desire.

Goal: If the item (based on the 'InOperator' of the Excel)
         is not in the SharePoint list (FullName column) delete
         the item(s) from the SharePoint list.

The following deletes ALL items in the SharePoint list. How
do I update the flow to ONLY delete the item(s) that are not
in the Excel workbook?

[ManishSolanki]

Hi @Phineas 

Inside 'No', you are again iterating on all items from SharePoint and deleting those. We can take the ID of the item to be deleted from the 'Apply to each 5' iteration, you can try modifying the flow as per below screenshot:

Expression used in the ID property:

items('Apply_to_each_5')?['ID']

If this helps & solves the problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[v-mingkz-msft · Accepted solution]

Hi @Phineas,

Do you want to delete items that do not exists in Excel table from SP list.

You can Initialize a flag variable to make judgements.

Following is my flow.

Best Regards,

Kyrie

[Phineas]

Sir,

Yes, I want to delete from SP List all items NOT in the Excel source data.

I came across a solution just like yours. It uses a variable with 'If_yes', as a string, and "a".

The delete ONLY function is now working properly; However, on my 602 item data set I am now getting a 'run time' error.

When I run a test on a 12 item data set I DO NOT get the 'run time' error.

I have already increased the pagination in both the Excel and 'Get item' action to 650.. 

I am still getting 'run time' error.

[Matthy79]

Hello @Phineas 

You shouldn’t use all those apply to each. It is better to use one select action for all SharePoint items and check if there is an entry in the excel table. After your select statement you can filter all entries not present in the excel and only use apply to each for those items. No condition, no variable, no apply to each inside another. Flow will be a lot less complex and will run vary fast.

[Phineas]

Can you provide an example, please?

[Matthy79]

Hello @Phineas 

Here is the select action you need (Columnames maybe different):

Here is a sample you can copy into a dummy flow. (Use "Add Action" -> "My Clipboard" -> "Ctrl-v")

{"id":"befed026-ee87-4c24-b7b0-8472c7b283dc","brandColor":"#8C3900","connectionReferences":{"shared_sharepointonline":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-b959cb53-5ecb-4b41-805f-06e7795a0bd2"}},"shared_office365":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_office365/connections/1597557c9920416e9a930edfee990646"}},"shared_commondataserviceforapps":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_commondataserviceforapps/connections/62947705b1a746319ca1e59aae985afc"}}},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Scope_Check_Arrays","operationDefinition":{"type":"Scope","actions":{"Compose_Sharepoint":{"type":"Compose","inputs":[{"Filename":"dummy.pdf","Info":"My Info1"},{"Filename":"dummy2.pdf","Info":"My Info2"},{"Filename":"dummy3.pdf","Info":"My Info3"},{"Filename":"dummy4.pdf","Info":"My Info4"}],"runAfter":{}},"Compose_Excel":{"type":"Compose","inputs":["dummy.pdf","dummy3.pdf"],"runAfter":{"Compose_Sharepoint":["Succeeded"]}},"Select_Check":{"type":"Select","inputs":{"from":"@outputs('Compose_Sharepoint')","select":"@addProperty(item(), 'Exists', contains(outputs('Compose_Excel'), item()['Filename']))"},"runAfter":{"Compose_Excel":["Succeeded"]}}},"runAfter":{}}}

[Phineas]

This confuses me.

It seems like an awful lot of work just to compare one column from the source Excel against the destination, and if the number is not in the source column but is in the destination SharePoint list erase from the SP List.

[Matthy79]

I don’t understand your response.

I gave you a single action that compares those 2 lists. If this is an awful lot of work, I am sorry. Stick with the solution you already got 😉

[Phineas]

I'm not questioning whether it works, or is appropriate; I just don't understand the references to Power Apps when the source is OneDrive Excel and the destination is a SharePoint list.

Also, how does the 'dummy.pdf' play a role? I don't know what to do with this code. You say just cut and paste it into a dummy flow using cut and paste, and add Action, and change column names - I can't even decipher column names in the below.

Thank you for your replies.

[Matthy79]

You wanted an example. This code is an example (the sources don’t natter) but all you need is the select action from the screenshot.

Since I don’t have access to your data sources and you also not provided sample data, it is not possible to send you a working solution. But you only have to modify the field names that are your identifier for the compare.

[Phineas]

Ok, here is the Select action.

   1. What is the 'outputs' suppose to be/represent?
   2. What is in the 'addproperties'?

Thank you,

[Matthy79]

“From” is your SharePoint List array.

addProperty adds an additional “column” to your SharePoint array, telling you if the row exists in excel or not.