Patch or Remove Record Based on For All Loop

[Anonymous]

Hi Power Apps Community,

I'm currently trying to make an app thats updates a selected user access to a certain virtual machine based on whether or not their value is checked in the checkbox by the admin. 

Currently I have the following setup, which works but is incredibly slow because of the double ForAll loop. The data source I'm using ('Virtual Machine Access List') is a sharepoint list.

// For all Items in the Gallery where there's a true checkbox, create a record if it doesnt already exist
ForAll(
    Filter(
        AdminVMGallery.AllItems,
        GrantAcess_Checkbox.Value = true
    ),
    Patch(
        'Virtual Machine Access List',
        Coalesce(LookUp('Virtual Machine Access List', ID = Value(UserAccessID_Lbl.Text)),
        Defaults('Virtual Machine Access List')),
        {
            'VM ID': VM_ID_Value.Text,
            'User Email': SelectedUser.Mail,
            Title: GUID()
        }
    )
);

////remove all items where checkbox is false  (double for all is super slow better way to do?)

ForAll(
    Filter(
        AdminVMGallery.AllItems,
        GrantAcess_Checkbox.Value = false
    ),
    Remove(
        'Virtual Machine Access List',
        LookUp('Virtual Machine Access List',ID = Value(UserAccessID_Lbl.Text))
    )
);

Is there a faster way to add/remove items based on this setup?

[Drrickryp]

@Anonymous 

Please check this video for some tips on how to do this with a collection and hidden toggle https://www.bing.com/videos/search?q=april+dunham+repeating+video&view=detail&mid=58A726BE2D33405FFD6E58A726BE2D33405FFD6E&FORM=VIRE&PC=EMMX20  

[RandyHayes · Accepted solution]

@Anonymous 

Please consider changing your Formula to the following:

// For all Items in the Gallery where there's a true checkbox, create a record if it doesnt already exist
Patch(
    'Virtual Machine Access List',
    ForAll(
        Filter(AdminVMGallery.AllItems, GrantAcess_Checkbox.Value) As _item,
        {
            ID: _item.ID,
            'VM ID': _item.VM_ID_Value.Text,
            'User Email': _item.SelectedUser.Mail,
            Title: GUID()
        }
    )
);

////remove all items where checkbox is false  (double for all is super slow better way to do?)
ForAll(
    Filter(AdminVMGallery.AllItems, GrantAcess_Checkbox.Value) As _item,
    RemoveIf('Virtual Machine Access List',
        ID = _item.ID
    )
);

The Items property of your AdminVMGallery should include an ID.  If there are new records to be created in that gallery, then their ID should be blank. 

With that, there is no need to double and triple lookup items.  The above creates a Table (that is the purpose of a ForAll...to create a Table) with all of the records to be updated/patched.  If the record has an ID (primary key) then Patch will update that particular record.  If the ID is blank, it will create one.

As for the Delete.  Unfortunately, it does NOT take a table of records to delete and they always have to be done one-by-one.  However, you can shorten the time it takes by simply supplying the ID of the record to delete.  And, in this case (unfortunately) you really can only do it by throwing away the output of the ForAll and just use it as a loop.  Again, if the Items property of the Gallery includes the ID (which it should), then the removes will work fairly quickly.

I hope this is helpful for you.