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

[pradeesh]

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.

[rubin_boer]

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 · Accepted solution]

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,

[pradeesh]

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.

[pradeesh]

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.

[v-xida-msft]

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,

[pradeesh]

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)

[pradeesh]

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