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
