Patch Function and SharePoint List Calculated column

[jasenpeters]

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)

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

[WarrenBelz]

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