Clarification on Deleting One Record From a SharePoint List Using Remove/RemoveIf

[Anonymous]

I need a little clarification on deleting a record from a SharePoint list where there is no form involved (e.g., in a Gallery). If I use example 1, it has the advantage of only deleting a single record but it has a delegation warning since it uses 'First()'. Example 2 works but might delete more than one record (my Power App enforces unique records when a new user is added by first checking if record already exists so this might not be a big deal).  Any advice?

// Ex 1: Works but 'First' creates a delegation warning and may not work on large data sets
Remove( 'Assigned-Users', First( Filter( 'Assigned-Users', 'Deal-Request-ID' = ThisItem.'Deal-Request-ID' && 'Assigned-Email' = ThisItem.'Assigned-Email' ) ) ); 

// Ex 2: Works but could delete more than one record
RemoveIf( 'Assigned-Users', 'Deal-Request-ID' = ThisItem.'Deal-Request-ID' && 'Assigned-Email' = ThisItem.'Assigned-Email' );

[RandyHayes · Accepted solution]

@Anonymous 

Consider the following formula as a one-record delete.  Since you are SharePoint based, the ID will be unique and can be used to remove the one record you want:

RemoveIf('Assigned-Users', ID = ThisItem.ID);

If you want to use the Remove function, then this formula would resolve your delegation issue:

Remove( 'Assigned-Users', 
    LookUp('Assigned-Users', 
           'Deal-Request-ID' = ThisItem.'Deal-Request-ID' && 'Assigned-Email' = ThisItem.'Assigned-Email'
    )
); 

This can also be the unique record with this:

Remove( 'Assigned-Users', 
    LookUp('Assigned-Users', ID = ThisItem.ID)
); 

The last two formulas above are less efficient as PowerApps needs to first perform a lookup, then then perform a delete/remove.  The RemoveIf function example first stated is superior as it does it all in one operation.

I hope this is helpful for you.

[Anonymous]

@RandyHayes - Thank you for the quick response.  I forgot the mention the Deal-Request-ID is not a unique identifier in the SP list which is why I need to also use the 'Assigned-Email'.  Thank you for this clarification!  So this was my solution and it works as expected. 

Remove( 'Assigned-Users', 
    LookUp('Assigned-Users', 
           'Deal-Request-ID' = ThisItem.'Deal-Request-ID' && 'Assigned-Email' = ThisItem.'Assigned-Email'
    )
); 

[RandyHayes]

@Anonymous 

Yes, I realized that the Deal-Request-ID would probably not be unique, but the SharePoint ID column is always unique.  If you are referring to a record (i.e. ThisItem) then ThisItem.ID is unique and can be used in your RemoveIf formula without any other criteria.

[Anonymous]

@RandyHayes - Thank you for the additional info.  

If I want to make sure the item was removed from the SP list, is this an acceptable check or could it be unreliable due to the time required for SP to remove the item from the list?

If( 
    !IsBlank( LookUp( 'Assigned-Users', 'Deal-Request-ID' = ThisItem.'Deal-Request-ID' && 'Assigned-Email' = ThisItem.'Assigned-Email' ) ),
    // Throw Error
    Notify( "Error Msg", Error ),
    // ELSE - Success
    Notify( "Success Msg", Success )
)

[RandyHayes]

@Anonymous 

So this will not really do you much good.  From the PowerApp perspective, the item is gone.  IF there was some reason that it was not really gone in SharePoint (which would be very rare), then you would have to take the painful performance hit of doing a Refresh in PowerApps before you did a check.  This would probably NEVER show that the item is still there after being deleted and it would only incur the cost of the performance in your app.

If for some reason you felt that SharePoint would not actually delete the item at the time, then your formula would be this (and again, based on the ID...there is no other criteria needed):

Refresh('Assigned-Users');

If( 
    !IsBlank( LookUp('Assigned-Users', ID = ThisItem.ID, ID) ),
    // Throw Error
    Notify( "Error Msg", Error ),
    // ELSE - Success
    Notify( "Success Msg", Success )
)

HOWEVER, this would not be effective and would actually ALWAYS throw an error message.  The reason is, once you Refresh your datasource, the reference to ThisItem is most likely now pointing at something completely different (because the original is gone).  In that case, it would always find a record and report as an error.

I'd avoid all of the above.  There is no need for that level of complexity in PowerApps.  It will most certainly remove the record and the ID will most certainly point to the exact record to delete.  Anything else is just redundant and subject to issues.

[Anonymous]

Thanks for the detailed explanation!  I really appreciate it.