Remove all rows in Dataverse that match items in filtered gallery

[Palaces]

Hello fellas,

I'm trying to remove all rows in a Dataverse table using a filtered gallery.

I can use Remove(Employees, galEmployees.Allitems) when the gallery has a simple filter [ Filter(Employees, ID=txtEmpID.text) ], but when my gallery has multiple filters combined with "If" and from different tables, I cannot use the function Remove as simple as it should be. I get the error "Cannot use a non-record value in this context 'galEmployees.Allitems'

My gallery filter looks like this:

If(lblCity.Text = "Oak Creek", Filter(Indiana, StartsWith(PositionID.PositionID, cbxEPPosition.Selected.PositionID)), 
If(lblCity.Text ="LA", Filter(California, StartsWith(PositionID.PositionID, cbxEPPosition.Selected.PositionID)), 
Filter(AllOtherStates, StartsWith(PositionID.PositionID, cbxEPPosition.Selected.PositionID))))

On my delete button I have this code:

If(lblCity.Text = "Oak Creek", Remove(Indiana, galStates.Allitems), 
If(lblCity.Text = "LA", Remove(California, galStates.Allitems),
Remove(AllOtherStates, galStates.Allitems)))

Any ideas why this can not be accomplished when filter the gallery from different tables?

When filtering the gallery from only one table it works just normal.

Thank you and appreciate your help.

Best regards.

Ps.

@RandyHayes, I have seen good comments from you on other posts, if you have had any situation like this I'd appreciate your insights as well.

[SebS]

@Palaces 

This is super complex, and I don't have access to Datavers now so I can't check if I will be right that's why sorry if I fail 🙂

main issue here is Remove() function :

The issue you're encountering is that you're trying to use the Remove() function with items from multiple tables. The Remove() Function expects the second argument to be a collection of records from a single table, not a combination of records from different tables.

Now here is something that I think should work but I can't test it.

Set(
    itemsToDelete,
    If(lblCity.Text = "Oak Creek", galStates.AllItems,
    If(lblCity.Text = "LA", galStates.AllItems,
    galStates.AllItems))
);
ForAll(itemsToDelete, Remove(
    If(lblCity.Text = "Oak Creek", Indiana,
    If(lblCity.Text = "LA", California,
    AllOtherStates)),
    LookUp(
        If(lblCity.Text = "Oak Creek", Indiana,
        If(lblCity.Text = "LA", California,
        AllOtherStates)),
        ID = ThisRecord.ID
    )
));
Refresh(galStates)

[Palaces]

Hi @SebS ,

Appreciate your reply!

I tested this approach and got an error

I have never used the ForAll function, not sure why it's not working.

Also, I think it shouldn't have any impact if this is on a SP List or on Dataverse?

Best regards.

[SebS]

@Palaces 

If no one comes up with something, I will recreate this on SP List and check it out, I was thinking to do it, but it's very late over here (2:30 am)  so I just attempted to write it down without any DataSource and test.

I check the topic tomorrow when I wake up and if nothing comes up I try to test it and see if I can get it to work for You 🙂

[Palaces]

I'll continue testing over here since it's only 6:30 PM :D.

Have a good rest, I appreciate your interest!!

[SebS · Accepted solution]

@Palaces 

Ok, So I modify the code and simplify it. It is working for Sp List.

The issue is I do not know what is PositionID.PositionID at your Data Source I recreate it as a Column PositionID in SP List means in my code, I'm Filtering Indiana where PositionID = cbxEPPosition.Selected.PositionID but in theory this should not have any inpact

If(lblCity.Text = "Oak Creek",
    Remove(Indiana, Filter(Indiana, PositionID.PositionID = cbxEPPosition.Selected.PositionID)),
    If(lblCity.Text = "LA",
        Remove(California, Filter(California, PositionID.PositionID = cbxEPPosition.Selected.PositionID)),
        Remove(AllOtherStates, Filter(AllOtherStates, PositionID.PositionID = cbxEPPosition.Selected.PositionID))
    )
);

You notice I skip Gallery and just place Filters in the formula direct it should not have any impact to what you want to achivew I think but I may be wrong.

[Palaces]

Hey @SebS ,

Pretty good!!

So far this work!!! 🙂 

I made a test with another table and another gallery, I'll test this with real data on Monday and I'll get back to you!

Much appreciated man!!

Have a great weekend! 🙂

Best regards!

[Palaces]

Hi @SebS ,

So, this is working pretty good!

Once again, I appreciate the time you put on this.

The PositionID.PositionID is a lookup field hosted in tables States to locate the employees position.

Thanks again, keep going man, you're doing a great job with your knowledge!

Best regards,

[SebS]

@Palaces 

Thank You for kind worlds and it was a pleasure to help You out.