cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
msze400
Frequent Visitor

Patch or Remove Record Based on For All Loop

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. 

 

msze400_1-1620048767930.png

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User III
Super User III

@msze400 

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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

View solution in original post

2 REPLIES 2
Drrickryp
Super User II
Super User II

@msze400 

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=58A726BE2D33405FFD...  

RandyHayes
Super User III
Super User III

@msze400 

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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Carousel April Dunnam Updated 768x460.jpg

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors
Users online (2,350)