Power Platform Community Forum Thread Details

I have two tables joined in SQL SERVER as an example I have a projects table that is constructed like this

CREATE TABLE [dbo].[Projects](
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [Name] [nvarchar](100) NOT NULL,
   [Manager] [nvarchar](100) NULL,
   [StartDate] [date] NULL,
   [EndDate] [date] NULL,
PRIMARY KEY CLUSTERED
(
   [ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

and a tasks table like this

CREATE TABLE [dbo].[Tasks](
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [ProjectID] [int] NOT NULL,
   [TaskName] [nvarchar](255) NOT NULL,
   [AssignedTo] [nvarchar](100) NULL,
   [StartDate] [date] NULL,
   [DueDate] [date] NULL,
   [Comments] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED
(
   [ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Tasks] WITH CHECK ADD CONSTRAINT [FK_Project_Tasks] FOREIGN KEY([ProjectID])
REFERENCES [dbo].[Projects] ([ID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Tasks] CHECK CONSTRAINT [FK_Project_Tasks]
GO

When I add the tables to dataverse as virtual tables I added the project table with a primary key fields as ID and Tasks table with primary key field as ID. If I use the native sql tables to build the many(tasks) -> 1 (Projects) and use the ProjectID field as the external Name field I get an error that the field entity is already in used by another entity.

I tried following this blog https://community.powerplatform.com/forums/thread/details/?threadid=62d050e2-e6a2-f011-bbd3-000d3a110039 and create views that create text verisons of my primary key columns. When I created the relationship I ran into an error that the string view id fields need to be able to be parsed as a GUID. When I updated my views to turn in ids into 32 string guids with 4 dashes I was able to get the relationship to hold true. However, then my tables become un editable within the dataverse environment and I get insert errors.

Has anyone been able to add related sql server tables to dataverse as virtual tables, recreate the same relationship and maintain the ability to edit the table records????

Categories:

Microsoft Dataverse with Power Apps