After recreating a view I use as the data source for my Power App. I now cannot edit any entries. After opening the Edit screen, making a change, and trying to save I receive the following message:
"The requested operation is invalid.
Server Response: Cannot insert duplicate key row in object 'dbo.Events' with unique index 'IX_Events'. The duplicate key value is (2022-03-22)."
dbo.Events is one of a few tables I reference in the view. "2022-03-22" refers to a date in the EventDate column in the Events table. I have made no changes to the dbo.Events table. When recreating my view (dbo.EventsView) I added two new columns; one to format the EventDate to use a longer format (Tuesday, March 22, 2022, as LongDate) and a calculated column (MyAge) to calculate age based on a birthdate and the EventDate.
Not sure if this is related, but when I open my app in PA Studio I see a bunch of invalid 'ID' name errors associated with the save edits button. Please see attached image. Here is the EventsView code:
CREATE VIEW [dbo].[EventsView] AS
SELECT dbo.Events.EventDate, FORMAT(dbo.Events.EventDate, 'dddd, MMMM dd, yyyy') AS LongDate, dbo.Events.Event, dbo.Homes.Home, dbo.Eras.Era, dbo.Eras.EraSort, dbo.Employers.Employer, dbo.Events.EventFavorite,
DATEDIFF(YEAR, '1971-11-11 00:00:00:00', dbo.Events.EventDate) -
CASE
WHEN MONTH('1971-11-11 00:00:00:00') > MONTH(dbo.Events.EventDate)
OR (MONTH('1971-11-11 00:00:00:00') = MONTH(dbo.Events.EventDate) AND DAY('1971-11-11 00:00:00:00') > DAY(dbo.Events.EventDate))
THEN 1
ELSE 0
END AS MyAge
FROM dbo.Events, dbo.Homes, dbo.Eras, dbo.Employers
WHERE (dbo.Events.EventDate BETWEEN dbo.Homes.HomeStartDate AND dbo.Homes.HomeEndDate)
AND (dbo.Events.EventDate BETWEEN dbo.Eras.EraStartDate AND dbo.Eras.EraEndDate)
AND (dbo.Events.EventDate BETWEEN dbo.Employers.EmployerStartDate AND dbo.Employers.EmployerEndDate)
Rob