Answered

Patch a Collection to SQL

(0) Share

Report

Posted on by SteveMC

I am trying to patch a collection to a SQL data source. The SQL table contains three columns which correspond to the collection labels. a YearKey (int) column, a HolidayDate (date) column, and a HolidayName (varchar(255)) column.

The idea is to check if there is data in the SQL table for the current year, and if not, push the collection to the table... Finally, if there is an error, show a popup error message.

The code below throws no error, but the data is not written to the SQL table... I can't figure out the issue.. Does anyone see anything wrong with the code?

ClearCollect(HolidayNames, LookUp('[dbo].[HolidayNames]',YearKey = Year(Today())));
If(IsEmpty(HolidayNames),
 Collect(HolidayNames,
 { YearKey: Year(Today()),
 HolidayDate : Date(Year(Today()), 1, 1),
 HolidayName : "New Year's Day"},
 { YearKey: Year(Today()),
 HolidayDate : Date(Year(Today()), 7, 4),
 HolidayName : "Independence Day"},
 { YearKey: Year(Today()),
 HolidayDate : Date(Year(Today()), 12, 24),
 HolidayName : "Christmas Eve"},
 { YearKey: Year(Today()),
 HolidayDate : Date(Year(Today()), 12, 25),
 HolidayName : "Christmas Day"},
 { YearKey: Year(Today()),
 HolidayDate : Date(Year(Today()), 12, 31),
 HolidayName : "New Year's Eve"} 
 );
 Patch('[dbo].[HolidayNames]', HolidayNames)
);

 If(!IsEmpty(Errors('[dbo].[HolidayNames]')),
 UpdateContext({ErrorMessage : First(Errors('[dbo].[HolidayNames]')).Message});
 UpdateContext({showErrorPop:true}))

Thanks!

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Sort by

Verified answer

v-xida-msft Microsoft Employee on at

Like
a
(2)

Report
Copy link

Link copied!
Hi @SteveMC,

Could you please share a bit more about the data structure of your SQL table?

Please take a try to modify your formula as below:

ClearCollect(HolidayNames, LookUp('[dbo].[HolidayNames]',YearKey = Year(Today()))); If(IsEmpty(HolidayNames), Collect(HolidayNames, {
YearKey: Year(Today()), HolidayDate : Date(Year(Today()), 1, 1), HolidayName : "New Year's Day"
}, {
YearKey: Year(Today()), HolidayDate : Date(Year(Today()), 7, 4), HolidayName : "Independence Day"
}, {
YearKey: Year(Today()), HolidayDate : Date(Year(Today()), 12, 24), HolidayName : "Christmas Eve"
}, {
YearKey: Year(Today()), HolidayDate : Date(Year(Today()), 12, 25), HolidayName : "Christmas Day"
}, {
YearKey: Year(Today()), HolidayDate : Date(Year(Today()), 12, 31), HolidayName : "New Year's Eve"
} ); Patch(
'[dbo].[HolidayNames]',
Defaults('[dbo].[HolidayNames]'), /* <-- Modify here */
HolidayNames
) ); If(!IsEmpty(Errors('[dbo].[HolidayNames]')), UpdateContext({ErrorMessage : First(Errors('[dbo].[HolidayNames]')).Message}); UpdateContext({showErrorPop:true})
)

In addition, you could also consider take a try to modify your formula as below:

ClearCollect(HolidayNames, LookUp('[dbo].[HolidayNames]',YearKey = Year(Today()))); If(IsEmpty(HolidayNames), Collect(HolidayNames, {
YearKey: Year(Today()), HolidayDate : Date(Year(Today()), 1, 1), HolidayName : "New Year's Day"
}, {
YearKey: Year(Today()), HolidayDate : Date(Year(Today()), 7, 4), HolidayName : "Independence Day"
}, {
YearKey: Year(Today()), HolidayDate : Date(Year(Today()), 12, 24), HolidayName : "Christmas Eve"
}, {
YearKey: Year(Today()), HolidayDate : Date(Year(Today()), 12, 25), HolidayName : "Christmas Day"
}, {
YearKey: Year(Today()), HolidayDate : Date(Year(Today()), 12, 31), HolidayName : "New Year's Eve"
} ); ForAll( /* <-- Modify here start */
RenameColumns(
RenameColumns(
RenameColumns(HolidayNames,"YearKey","YearKey1"),
"HolidayDate","HolidayDate1"
),
"HolidayName","HolidayName1"
),
Patch(
'[dbo].[HolidayNames]',
Defaults('[dbo].[HolidayNames]'),
{
YearKey:YearKey1,
HolidayDate:HolidayDate1,
HolidayName:HolidayName1
}
)
) /* <-- Modify here end */ ); If(!IsEmpty(Errors('[dbo].[HolidayNames]')), UpdateContext({ErrorMessage : First(Errors('[dbo].[HolidayNames]')).Message}); UpdateContext({showErrorPop:true})
)

Note: Please make sure that you have defined a primary key within your SQL table.

More details about the Patch function and ForAll function in PowerApps, please check the following article:

Patch function, ForAll function

Best regards,

Kris

Was this reply helpful? Yes No
SteveMC 21 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you for your help Kris! The issue was the SQL table did not have a primary key... Your code examples were also very helpful!

Thanks again,
Steve

Was this reply helpful? Yes No