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
Super User

@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

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

2 REPLIES 2
Drrickryp
Super User
Super User

@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
Super User

@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

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (965)