hi guys,
i want to know if i have created my table structure correctly, i'm asking as i'm getting a problem on the Add record step where i have a hierachy link on a table.
for example. i have a foreign key link from Credentials.ParentId (NULLABLE) to Credentials.Id
/****** Object: Table [dbo].[Credentials] Script Date: 23/05/2023 7:54:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Credentials](
[Id] [uniqueidentifier] NOT NULL,
[DisplayId] [nvarchar](50) NOT NULL,
[OwnerId] [nvarchar](450) NOT NULL,
[GovernanceOwnerId] [nvarchar](450) NOT NULL,
[ParentId] [uniqueidentifier] NULL,
[Name] [nvarchar](max) NOT NULL,
[RegistrationNumber] [nvarchar](max) NOT NULL,
[GrantingBody] [nvarchar](max) NOT NULL,
[CredentialStatus] [nvarchar](max) NOT NULL,
[DateOfObtainment] [datetime] NOT NULL,
[DateOfExpiry] [datetime] NULL,
[PrinciplePlaceOfProfession] [nvarchar](max) NULL,
[PrincilePlaceOfProfessionAddress] [nvarchar](max) NULL,
[CreatdById] [nvarchar](450) NOT NULL,
[CreatedDt] [datetime] NOT NULL,
[Status] [int] NOT NULL,
CONSTRAINT [PK_Credentials] 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].[Credentials] ADD CONSTRAINT [DF_Credentials_Id] DEFAULT (newid()) FOR [Id]
GO
ALTER TABLE [dbo].[Credentials] ADD CONSTRAINT [DF_Credentials_CreatedDt] DEFAULT (getdate()) FOR [CreatedDt]
GO
ALTER TABLE [dbo].[Credentials] ADD CONSTRAINT [DF_Credentials_Status] DEFAULT ((1)) FOR [Status]
GO
ALTER TABLE [dbo].[Credentials] WITH CHECK ADD CONSTRAINT [FK_Credentials_AspNetUsers] FOREIGN KEY([CreatdById])
REFERENCES [dbo].[AspNetUsers] ([Id])
GO
ALTER TABLE [dbo].[Credentials] CHECK CONSTRAINT [FK_Credentials_AspNetUsers]
GO
ALTER TABLE [dbo].[Credentials] WITH CHECK ADD CONSTRAINT [FK_Credentials_GovernanceOwnerAspNetUsers] FOREIGN KEY([GovernanceOwnerId])
REFERENCES [dbo].[AspNetUsers] ([Id])
GO
ALTER TABLE [dbo].[Credentials] CHECK CONSTRAINT [FK_Credentials_GovernanceOwnerAspNetUsers]
GO
ALTER TABLE [dbo].[Credentials] WITH CHECK ADD CONSTRAINT [FK_Credentials_OwnerAspNetUsers] FOREIGN KEY([OwnerId])
REFERENCES [dbo].[AspNetUsers] ([Id])
GO
ALTER TABLE [dbo].[Credentials] CHECK CONSTRAINT [FK_Credentials_OwnerAspNetUsers]
GO
ALTER TABLE [dbo].[Credentials] WITH CHECK ADD CONSTRAINT [FK_Credentials_ParentCredentials] FOREIGN KEY([ParentId])
REFERENCES [dbo].[Credentials] ([Id])
GO
ALTER TABLE [dbo].[Credentials] CHECK CONSTRAINT [FK_Credentials_ParentCredentials]
GO
when i add a record in Radzen from the generated SCRUD forms, i'm unable to do so.....
should i perhaps alter the definition of the hierachy link to be like this
CREATE TABLE Org_T3
(
EmployeeId hierarchyid PRIMARY KEY,
ParentId AS EmployeeId.GetAncestor(1) PERSISTED
REFERENCES Org_T3(EmployeeId),
LastChild hierarchyid,
EmployeeName nvarchar(50)
)
GO
what is the correct definition of the table to make hierachical links and SCRUD pages work ?