cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jasenpeters
Regular Visitor

Patch Function and SharePoint List Calculated column

Given:

A SharePoint List (SPL) called "_Test" with the following columns:

---------------------------------------------------------------------------------------------------------------------

ID - The built-in, automatically incremented SPL column (users can NOT edit values in this column)

Title - The built-in, single line of text, default column for a new SPL (users can edit values in this column)

ItemName - A single line of text column, (users can edit values in this column)

ID_ItemName - A calculated column (users can NOT edit values in this column)

Title_ItemName - A calculated column (users can NOT edit values in this column)

 

CalculatedColumns.PNG

Please note that there are 2 Calculated columns (calculation formulas are as written under List Settings):

 ID_ItemName    =ID & " - " & ItemName

 Title_ItemName =Title & " - " & ItemName

 

The first 2 records (ID = 1, ID = 2), were created before the calculated columns were added to the SPL.

When the calculated columns were added, the calculations were as expected for the first 2 records.

 

After the calculated columns were added, 2 additional records were then added (ID = 3, ID = 4).

 

Note that Title_ItemName is correct in its calculation, but ID_ItemName is incorrect.

 

My only guess is that it has to do with the timing of the ID field and when the next ID is created internally.

It's as if the calculated column (ID_ItemName) is calculating before the new ID is posted to the ID field (column) for the new record,

and is therefore getting a blank ID. Hence " - C", as in the example above (instead of "3 - C", as expected).

 

Therefore any new records will not have the ID appended/concatenated to the ID_ItemName as expected in the calculation.

This behaviour is also verified using a PowerApps Patch function on the SPL.

 

The only way I have found to update the SPL records to get the correct calculations is to edit the SPL under List Settings,

click the corresponding calculated column, and save the column again. (No changes to the column were necessary, just click "ok")

 

Is there anyway to get this to calculate correctly?

or, Is there a way to get this to happen using the Patch function in a PowerApp?

 

Thanks in Advance

1 REPLY 1
WarrenBelz
Super User
Super User

Hi @jasenpeters ,

I have one piece of initial advice for you - SharePoint calculated columns do not "play well" with Power Apps and should not be relied upon to produce results. Do the calculation in Power Apps using the same columns and logic.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

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.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

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
Top Kudoed Authors
Users online (1,807)