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

When the value of a column for an item in a Sharepoint list is changed it should increment the rest of the columns below it using PowerApps

Hi All, hope everyone here are doing good.
I have a requirement to design a new solution in my team.
I've designed a form in Powerapps which is configured with a sharepoint list as the data source. The requirement is as below.
The Sharepoint list contains items with columns named as Name, Status, Priority, Comments, When Created, Description etc. In these columns, Priority is supposed to have unique values starting from 0, 1, 2, ......,500.
I've designed a form to view all the existing items from the SP list with the use of Gallerylist, forms, search etc which works perfectly fine. Along with that there are additional screens with provision to edit existing items in the list and also to add new items into the list. The list as of today, have 150 items starting from priority 0 to 149 which is unique for every item. This list will grow going forward.

My Challenge:
When i add a new item with priority 150 i have no issues in adding it, but when i need to add a new item with a priority in between for eg 15, i need to have the existing item with priority 15 to be incremented to 16 and thereby should increment all the items after 15 by 1 and finally the item which was having priority 149 should be updated to 150. Now the challenge is to have this logic applied as i've no much idea as i'm not much familiar with PowerApps and very recently started using it and trying to learn. This is the same case while editing existing item to change the priority i.e. this increment should get applied to the later items.

Can someone please assist me with your suggestions.

1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

Hi @pradeesh ,

Do you want to update the rest existing items in your SP List when you create an item with priority between the existing priorities?

 

Regarding the needs that you mentioned, I think the combination of ForAll and Patch function could achieve your needs. I have made a test on my side, please try the following workaround:

Set the OnSelect property of the "Submit" button to following:

If(
   Value(Priority_DataCard.Update) <= Max('Your SP List', Priority),
   ForAll(
          Filter('Your SP List', Priority >= Value(Priority_DataCard.Update)) As LoopRecord,
          Patch(
                'Your SP List',
                LoopUp('Your SP List', ID = Value(LoopRecord.ID)),
                {
                    Priority: LoopRecord.Priority + 1
                }
          )
   )
);
SubmitForm(EditForm1)

or

If(
   Value(PriorityTextInputBox.Text) <= Max('Your SP List', Priority),
   ForAll(
          Filter('Your SP List', Priority >= Value(PriorityTextInputBox.Text)) As LoopRecord,
          Patch(
                'Your SP List',
                LoopUp('Your SP List', ID = Value(LoopRecord.ID)),
                {
                    Priority: LoopRecord.Priority + 1
                }
          )
   )
);
SubmitForm(EditForm1)

Note: I assume that the Priority field is a Number type field in your SP List.

 

Please try above solution, then check if the issue is solved.

 

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.

View solution in original post

7 REPLIES 7
rubin_boer
Super User
Super User

hi there

collect your data from SharePoint. Collect or

ClearCollect(localData,sharepointData)

now check if the priority exist with a filter and if so update all of those values in the column with one and add the new record to the collection and update sharepoint.

 

//check too see if the priority exists
If(
CountRows(
Filter(
localData,
myPriority = Value(TextInput2.Text)
)
) > 0, 

//if it does exists, add one to it and every priority higher than it
UpdateIf(
localData,
Value(myPriority) >= Value(TextInput2.Text),
{myPriority: myPriority + 1}
)
);

//add the new entry to the collection
Collect(
localData,
{
myItem: TextInput1.Text,
myPriority: Value(TextInput2.Text)
}
)

v-xida-msft
Community Support
Community Support

Hi @pradeesh ,

Do you want to update the rest existing items in your SP List when you create an item with priority between the existing priorities?

 

Regarding the needs that you mentioned, I think the combination of ForAll and Patch function could achieve your needs. I have made a test on my side, please try the following workaround:

Set the OnSelect property of the "Submit" button to following:

If(
   Value(Priority_DataCard.Update) <= Max('Your SP List', Priority),
   ForAll(
          Filter('Your SP List', Priority >= Value(Priority_DataCard.Update)) As LoopRecord,
          Patch(
                'Your SP List',
                LoopUp('Your SP List', ID = Value(LoopRecord.ID)),
                {
                    Priority: LoopRecord.Priority + 1
                }
          )
   )
);
SubmitForm(EditForm1)

or

If(
   Value(PriorityTextInputBox.Text) <= Max('Your SP List', Priority),
   ForAll(
          Filter('Your SP List', Priority >= Value(PriorityTextInputBox.Text)) As LoopRecord,
          Patch(
                'Your SP List',
                LoopUp('Your SP List', ID = Value(LoopRecord.ID)),
                {
                    Priority: LoopRecord.Priority + 1
                }
          )
   )
);
SubmitForm(EditForm1)

Note: I assume that the Priority field is a Number type field in your SP List.

 

Please try above solution, then check if the issue is solved.

 

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.

View solution in original post

Thankyou @rubin_boer  for you valuable suggestion. I've gone through your suggestion and trying to implement it on my side. Since i'm new to PowerApps i'm trying to interpret it word by word to see how can i update it into my app. Will surely get back to you with an update.

Thankyou @v-xida-msft  for you valuable suggestion. I've gone through your suggestion and trying to implement it on my side. Since i'm new to PowerApps i'm trying to interpret it word by word to see how can i update it into my app. Will surely get back to you with an update.

Hi @pradeesh ,

Sure. Please try it, check if the issue is solved. If it could solve your problem, please consider go ahead to click "Accept as Solution" to identify this thread has been solved.

 

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.

I  was trying to follow this step and since my design changed a bit i have a doubt in how to apply this. I had to use an Excel table as the datasource. So i'm stuck up in deciding what to use at the step "ID = Value(LoopRecord.ID))" as there are no ID column in my excel table. Apologies for being late to come back with the results.

 

If(
Value(DataCardValue4.Text) <= Max('OnPrem_TPRule_1', Priority),
ForAll(
Filter('OnPrem_TPRule_1', Priority >= Value(DataCardValue4.Text)) As LoopRecord,
Patch(
'Your SP List',
LoopUp('OnPrem_TPRule_1', ID = Value(LoopRecord.ID)),
{
Priority: LoopRecord.Priority + 1
}
)
)
);
SubmitForm(OnPremForm)

A snapshot of my Excel table is as below. It has a unique ID "__PowerAppsId__" as a column in the excel table which i believe is auto-generated column and also not available to be used in place of ID as you mentioned for SharePoint datasource. Please suggest

MyExcelTable1.PNGMyExcelTable2.PNG

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

secondImage

Demo Extravaganza Winner Announcement

Please join us on Wednesday, July 21st at 8a PDT. We will be announcing the Winners of the Demo Extravaganza!

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.

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