cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kart-19
Level: Powered On

How to bulk update with IF condition

Hello All,

I am stumbled with an issue that is preventing me for a Bulk update and would be grateful for a solution.

 

I have created an app that keeps and update of the statuses of Orders on a daily basis. I have two databases(both in Sharepoint) , one is the master data 'Data_master' and the other is the data with updated statuses 'data_out'. Two screens are created, one with a datatable that connects to 'data_master' and also a dropdown to filter the orders based on the 'order_id' . The datatable only has a 'item_number' and when clicked it will take me to another screen with a form that displays complete details of the 'item_number'. 

 

Apart from the form i have two buttons below one is the Submit and the other is Back. I also have a text input called Comments, so when i update the comment and submit, the data will be saved as a collection first and then will be patched to SP list 'data_out'.

 

The Issue - 

The order_id might have multiple item_number or only one item_number. The condition is same comment should be updated in the collection if the order id has multiple item_number or if it has only one item_number then i should update a different comment to the collection. Overall all these comments should be patched to the 'data_out' list. 

 

I have added a check box 'BulkUpdate ' so that when i see multiple 'order_number', the box will be checked and on the click of 'submit' all those 'item_number' will be updated with the same comment. if unchecked , then the comment should be updated only for the selected 'item_number'. Below is the code.

If(BulkUpdate.Value=true,Collect(Collection3,Filter(Collection3,Order_id=OrderDropDown_1.Selected.Value,{Do the collection}, Collect and then patch

But this is not working and also i am not able to figure out the exact code to be used. 

 

Please help me out !!

 

Thanks 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: How to bulk update with IF condition

Hi @Kart-19 ,

Could you please share a screenshot about your app's configuration?

How do you create your Collection (Collection3) within your app? Is the Collection created from your 'Data_master' SP List?

Futher, do you want to do some bulk-update to your Collection?

 

Based on the formula that you mentioned, I think there is something wrong with it. I assume that the Collection (Collection3) that you mentioned created from your 'Data_master' SP List, I have made a test on my side, please take a try with the following workaround:

Set the OnStart property of the App control or OnVisible property of the first screen of your app to following:

ClearCollect(
              Collection3,
             'Data_master'      
)

Add a Data table control, set the Items property to following:

Collection3

Set the OnSelect property of the "Submit" button in your Detail Screen to following formula:

If(
   BulkUpdate.Value = true,
   UpdateIf(   /* <-- bulk-update multiple 'item_number' with same comment content  */
Collection3,
Order_id = OrderDropDown_1.Selected.Value,
{
CommentsColumn: CommentsTextBox.Text
}
),
Patch(
Collection3,
LookUp(Collection3, ID = Gallery1.Selected.ID),
{
CommentsColumn: CommentsTextBox.Text
}
) );
ForAll( /* <-- Patch your Collection3 records into your 'Data_master' SP list */
Collection3,
Patch(
'Data_master',
LookUp('Data_master', ID = Collection3[@ID]),
{
CommentsColumn: Collection3[@CommentsColumn]
}
)
)

More details about the Patch function, ForAll function and UpdateIf function, please check the following article:

Patch functon, ForAll function and UpdateIf function

 

Please consider take a try with above solution I provided, then re-load your app, check if the issue is solved.

 

More details about the bulk-updates in PowerApps, please also check and see if the following video would help in your scenario:

https://www.youtube.com/watch?v=gRAnpZJESiE

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: How to bulk update with IF condition

Hi @Kart-19 ,

Could you please share a bit more about your issue?

 

Based on the needs that you mentioned, I think the solution I provided above could achieve your needs. I think the UpdateIf() function could achieve your needs:

 

UpdateIf(
             Collection3,
             Order_id = OrderDropDown_1.Selected.Value,  /* <-- Filter condition */
             {
               CommentsColumn: CommentsTextBox.Text
             }
)

 

In addition, you could also take a try with the following Patch formula:

ForAll(
          RenameColumns(Collection3, "ID", "ID1"),
          If(
             Order_id = OrderDropDown_1.Selected.Value,   /* <-- Filter Condition */
             Patch(
                       Collection3,
                       LookUp(Collection3, ID = ID1),    /* <-- Find the specific record which meets the filter condition in your Collection3 that you want to update */
                       {
                         CommentsColumn: CommensTextBox.Text
                       }
             )
          )
)

Please consider take a try with the solution I provided above, then check if the issue is solved.

 

Best regards,

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
JimmyBecks
Level: Powered On

Re: How to bulk update with IF condition

Hi Kart-19,

 

It sounds like you could make use of the ForAll function to update multiple rows in SharePoint.

 

Vasavi Bhaviri Setty has written a great article about how to bulk update items:

 

https://powerapps.microsoft.com/ru-ru/blog/bulk-update-using-forall-and-patch/

 

Community Support Team
Community Support Team

Re: How to bulk update with IF condition

Hi @Kart-19 ,

Could you please share a screenshot about your app's configuration?

How do you create your Collection (Collection3) within your app? Is the Collection created from your 'Data_master' SP List?

Futher, do you want to do some bulk-update to your Collection?

 

Based on the formula that you mentioned, I think there is something wrong with it. I assume that the Collection (Collection3) that you mentioned created from your 'Data_master' SP List, I have made a test on my side, please take a try with the following workaround:

Set the OnStart property of the App control or OnVisible property of the first screen of your app to following:

ClearCollect(
              Collection3,
             'Data_master'      
)

Add a Data table control, set the Items property to following:

Collection3

Set the OnSelect property of the "Submit" button in your Detail Screen to following formula:

If(
   BulkUpdate.Value = true,
   UpdateIf(   /* <-- bulk-update multiple 'item_number' with same comment content  */
Collection3,
Order_id = OrderDropDown_1.Selected.Value,
{
CommentsColumn: CommentsTextBox.Text
}
),
Patch(
Collection3,
LookUp(Collection3, ID = Gallery1.Selected.ID),
{
CommentsColumn: CommentsTextBox.Text
}
) );
ForAll( /* <-- Patch your Collection3 records into your 'Data_master' SP list */
Collection3,
Patch(
'Data_master',
LookUp('Data_master', ID = Collection3[@ID]),
{
CommentsColumn: Collection3[@CommentsColumn]
}
)
)

More details about the Patch function, ForAll function and UpdateIf function, please check the following article:

Patch functon, ForAll function and UpdateIf function

 

Please consider take a try with above solution I provided, then re-load your app, check if the issue is solved.

 

More details about the bulk-updates in PowerApps, please also check and see if the following video would help in your scenario:

https://www.youtube.com/watch?v=gRAnpZJESiE

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Kart-19
Level: Powered On

Re: How to bulk update with IF condition

Hello ,

Thank you for the turnaround. Currently I'm trying with the suggested code .

But I am also to try another route that is to update existing record. It actually works only for the first line of the record but when I try to update another new record , it replaces the old one so I am not able to update comments for all the records . Is there a way to resolve
Community Support Team
Community Support Team

Re: How to bulk update with IF condition

Hi @Kart-19 ,

Could you please share a bit more about your issue?

 

Based on the needs that you mentioned, I think the solution I provided above could achieve your needs. I think the UpdateIf() function could achieve your needs:

 

UpdateIf(
             Collection3,
             Order_id = OrderDropDown_1.Selected.Value,  /* <-- Filter condition */
             {
               CommentsColumn: CommentsTextBox.Text
             }
)

 

In addition, you could also take a try with the following Patch formula:

ForAll(
          RenameColumns(Collection3, "ID", "ID1"),
          If(
             Order_id = OrderDropDown_1.Selected.Value,   /* <-- Filter Condition */
             Patch(
                       Collection3,
                       LookUp(Collection3, ID = ID1),    /* <-- Find the specific record which meets the filter condition in your Collection3 that you want to update */
                       {
                         CommentsColumn: CommensTextBox.Text
                       }
             )
          )
)

Please consider take a try with the solution I provided above, then check if the issue is solved.

 

Best regards,

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Kart-19
Level: Powered On

Re: How to bulk update with IF condition

Hello,

 

I tried with your code and it works without any flaws. But there is only one delegation warning in the lookup part of FORALL function as this is indicating that the formula might not work properly on large datasets. The current dataset contains over 450 lines . Is this an issue i mean is there any limitation. As far as i know i have set the data row limit in settings to 2000 lines.

 

Thank you

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, August 21st at 8am PDT

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

SixthImage

Power Summit Australia 2019

August 20-23rd 2019

Users Online
Currently online: 18 members 4,305 guests
Please welcome our newest community members: