Answered

Patching Gallery to SQL Table

(0) Share

Report

Posted on by DeeCiii15

Hi,

In my app I am trying to update records in my SQL data source from a gallery. I am allowing the user to edit fields in a line and want to patch any field they might update to the corresponding record in SQL. Below, is my update code and the screen from my app. I am getting an error that the record is not found when I run the update. However, if I create a gallery with the same filter, I find my record.

Save Button On Select:

Update(TABLE, First(Filter(TABLE, ID = Value(IDLabel.Text) && AXIS = Text(varAxis) && SITE = varSite && SCENARIO_ID = varScenarioID)), {ID: Value(IDLabel.Text), AXIS: Text(varAxis),SITE: varSite, SCENARIO_ID: varScenarioID, START_DATE: DateTimeValue(TextInput1.Text), END_DATE: DateTimeValue(TextInput2.Text)})

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (1)

Sort by

Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@DeeCiii15

Patch(TABLE, ThisItem, {ID: Value(IDLabel.Text), AXIS: Text(varAxis),SITE: varSite, SCENARIO_ID: varScenarioID, START_DATE: DateTimeValue(TextInput1.Text), END_DATE: DateTimeValue(TextInput2.Text)})

Was this reply helpful? Yes No
DeeCiii15 84 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi,

Thanks for the reply. I tried your code and I'm getting the same response "The specified record was not found."

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@DeeCiii15

I am assuming that the ID is the Primary key of your table so try this

Patch(TABLE, LookUp(TABLE,ID=ThisItem.ID), {ID: Value(IDLabel.Text), AXIS: Text(varAxis),SITE: varSite, SCENARIO_ID: varScenarioID, START_DATE: DateTimeValue(TextInput1.Text), END_DATE: DateTimeValue(TextInput2.Text)})

I'm no SQL user but for this to work, you need to create a constraint on the Primary key of the table. https://www.w3schools.com/sql/sql_primarykey.ASP

Was this reply helpful? Yes No
DeeCiii15 84 on at

Like
a
(0)

Report
Copy link

Link copied!

I tried that code also, but I'm getting a syntax error saying Patch has some unsupported functions related to the Lookup. (below)
My primary key is setup as a constraint key in SQL Server using a combination of (ID, Site, Axis and ScenarioID)

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

@DeeCiii15

That is likely the problem. PowerApps works better with a single ID as the primary key. @timl is much more familiar with sql than I am.

Was this reply helpful? Yes No
DeeCiii15 84 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for your help @Drrickryp Do you have any thoughts @timl ? Would it be better to do this with a called procedure directly to SQL?

Was this reply helpful? Yes No
Verified answer

timl 37,229 Super User 2026 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @DeeCiii15

Rather than specifying the target record with a call to LookUp, you should be able to just specify the combination PK values.

I'd try the following to see if that works any better:

Patch(SCENARIO_TABLE, { C_SLOT_ID:Value(SlotIDLabel.Text), C_AXIS:Text(varFilterAxis), C_PROD_SITE_PDP:varFilterSite, SCENARIO_ID:parmScenarioID }, { C_PROD_START_DATE: DateTimeValue(ProdStart.Text), C_PROD_END_DATE: DateTimeValue(ProdEnd.Text) } )

Was this reply helpful? Yes No
DeeCiii15 84 on at

Like
a
(0)

Report
Copy link

Link copied!

that worked perfect thanks!

Was this reply helpful? Yes No