Answered

Patching SQL Table with Calculated Columns

(0) Share

Report

Posted on by Cazzym

I am looking to patch an SQL table (SQL On-Prem Connector) that includes calculated columns.

The patch is successful, but throws an error saying that the calculated columns are read only and cannot be changed. This necessitates me filtering out any readonly errors (dangerous) and I also worry it slows performance of the patch statement.

This occurs even if the table of patches includes no Read-Only columns.

In the below example, CollectionPrePatch is a collection of the records to be modified, direct from the DB.
CollectionPostPatch is a collection of the records after modification, to be patched.

Patch(SQLTable, CollectionPrePatch, CollectionPostPatch)
Raises this error, which is expected.

But so does
Patch(SQLTable, CollectionPrePatch, DropColumns(CollectionPostPatch, CalculatedColumns))

And so does

ClearCollect(TempCollectionPostPatch, DropColumns(CollectionPostPatch, CalculatedColumns));
Patch(SQLTable, CollectionPrePatch, TempCollectionPostPatch)

I understand that some other members have avoided this error by directly specifying records in their patch statement, rather than a table of records, however there are many many columns in this table and they may be changed in future, so I would rather avoid such a manual workaround, where I have to type out every single column.

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

Hi @Cazzym

As a general principle of database development, calculated columns should not be included in the back end of a database. The calculations are generally done in the front end from raw data in the back end. This saves space and the necessity of doing what you are asking for.

Was this reply helpful? Yes No
Cazzym 42 on at

Like (0)

Report

Hi @Drrickryp,
While I appreciate the sentiment, these calculated columns are actually here for the sake of PowerApps. PowerApps cannot do lookups on date fields, so I have calculated columns that represent these date fields as integers, which powerapps can do lookups on.

Was this reply helpful? Yes No
Verified answer

v-siky-msft on at

Like (0)

Report

Hi @Cazzym ,

Computed columns actually are virtual columns that are not physically stored in the table. Values of computed column are updated when any columns that are part of their calculation change.

Hence, please try to ensure these columns composing the computed columns are patched, and then computed column should be generated automatically.

Hope this helps.

Sik

Was this reply helpful? Yes No