Unanswered

Saving values from calculated column in collection to datasource

(0) Share

Report

Posted on by sinha_d

Hi,

I have a collection with 1 calculated column. In the corresponding data table, on selecting a row, I display a form with the values to edit 2 fields. On clicking an icon, I am trying to use the Patch command to save the values back to the data source. However, I get an error "The specified column "Ageing" does not exist". 'Ageing' is the calculated column.

Can somebody help pls?

On clicking a button, the following collection is created:

ClearCollect(collTempAgeing, Filter(ProjectActivity, IsBlank(Activity_Completion)));
ClearCollect(collActivity_Pending, AddColumns(collTempAgeing,"Ageing",DateDiff(DateValue(Activity_Date),Today())&" days"));
SortByColumns(collActivity_Pending,"Project")

On clicking the icon, I am trying to save the updated values by:

Patch(ProjectActivity,dtblPendingActivity.Selected, {Activity_Completion:dtPendingEndDate.SelectedDate, Activity_Description:txtPendingDetails.Text})

Categories:

Building Power Apps

All responses (13)

Answers (0)

Sort by

AJ_Z 3,711 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

is the datasource for dtblPendingActivity one of the collections or the table you are patching to. Is it possible the datasource/Items on the gallery is collActivity_Pending?
sinha_d 14 on at

Like
a
(1)

Report
Copy link

Link copied!

dtblPendingActivity is the table where I'm displaying the collection collActivity_Pending.

Hope this helps.
AJ_Z 3,711 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Is ProjectActivity a dataverse table or another Datasource e.g. sharepoint List, SQL etc.

Whats the Guid Columns in it called. GUID or Unique Identifier e.g. ID or RECID in some tables
sinha_d 14 on at

Like
a
(0)

Report
Copy link

Link copied!

ProjectActivity is a Google sheet on a Google drive.
There is no explicit GUID / ID column. The values are retrieved based on the value of the 'Project' field. Values are not unique.
The calculated column in the collection and being displayed in the data table is for the UI only - it does not need to be stored back to the google sheet.
AJ_Z 3,711 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

I believe this is the cause of your problem.
The difference in the collection schema and the data source table schema. Caused when you add columns
dgatewood 34 on at

Like
a
(0)

Report
Copy link

Link copied!

I have yet to write to any datasources columns that are calculated columns. IIRC PowerApps calculated column types are read-only. Wouldn't the calculated column update when the row is updated?
AJ_Z 3,711 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
I had three ideas to resolve this:
1) Add a a column for this to the Google Sheet called Ageing
2) Add a GUID or Unique Column or combination of columns that will always be unique this way the item in the patch could be a lookUp
3) Do not add a column to you collection instead change the area in the UI to have a formula similar to
DateDiff(DateValue(ThisItem.Activity_Date),Today())&" days"

this prevents the schema change as you do not add the column

If you need me to go into more detail for one of these solutions let me know which one and i will do so. also 2 and 3 are more likely to be what you want instead of option one.

if you appreciated my comments please like/kudo them as it does make me smile 🙂 and if it is your solution please select accept as solution
AJ_Z 3,711 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@dgatewood you make a valid point if this was a calculated columns in the original datasource that would be the case however @sinha_d has advised that this is not a column that exists in data and is a column they added in a collection for the UI e.g. so users see it but it doesn't need to be stored. The reason they see this is due to the Patch formula they are Patching to the datasource and selecting an item in a gallery referencing a collection in which the schema no longer matches. this is why they see the error
dgatewood 34 on at

Like
a
(0)

Report
Copy link

Link copied!

I guess I should read a little slower next time. Is this the first spot in the powerapps that the Pending collection gets pulled together? I know it gives me a hard time when I call the collection, clear it and refill with new columns or different columns after it has already filled that collection. You can see this behavior when you just run that collect and go view the collection table. Another trick is forcing the column type for the collection by wrapping it in a Text() for the whole formula that way it knows its a text column and not date time
sinha_d 14 on at

Like
a
(1)

Report
Copy link

Link copied!

Thanks AJ_Z. Sorry for the late response - have been out the last couple of days.

I did not want the calculated column to be saved / updated back into the source. I changed to using a gallery, with your suggestion of using a label in the UI to calculate and show the calculated value.