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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (3,425)