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

Patching SQL Table with Calculated Columns

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Patching SQL Table with Calculated Columns

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

View solution in original post

3 REPLIES 3
Highlighted
Super User III
Super User III

Re: Patching SQL Table with Calculated Columns

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.

Highlighted
Frequent Visitor

Re: Patching SQL Table with Calculated Columns

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.

Highlighted
Community Support
Community Support

Re: Patching SQL Table with Calculated Columns

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

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (7,216)