Copy list structure with circular lookup columns

I'm using the built-in "New list --> From existing list" copy feature and trying to copy a very simple list. The problem is that this list contains a lookup to another list, and that list contains a ...

Lists

jcgonzalezmartin
Jun 28, 2022
Hi,
I believe the native creation of lists from an exting ones does not support this senario. However, with Sharegate you should not have problems at all and should copy everything as long as you copy firts all the related lists

jcgonzalezmartin

MVP

Jun 28, 2022

Hi,
I believe the native creation of lists from an exting ones does not support this senario. However, with Sharegate you should not have problems at all and should copy everything as long as you copy firts all the related lists

ParAdeen

Copper Contributor

Jun 28, 2022

jcgonzalezmartin Thanks a lot.

Transforming this to the SQL doamin. This is what Microsofts built in tool are trying to do:

CREATE TABLE [dbo].[Table10]
(
    [Id] INT NOT NULL PRIMARY KEY, -- Primary Key column
    [Title] NVARCHAR(50) NOT NULL,
    [LookupColumn] INT NULL REFERENCES Table20 (Id)  -- Foreign Key to table20
);
GO
CREATE TABLE [dbo].[Table20]
(
    [Id] INT NOT NULL PRIMARY KEY, -- Primary Key column
    [Title] NVARCHAR(50) NOT NULL,
    [LookupColumn] INT NULL REFERENCES Table10 (Id) -- Foreign Key to table10
);
GO

While what they should be doing is this:

CREATE TABLE [dbo].[Table1]
(
    [Id] INT NOT NULL PRIMARY KEY, -- Primary Key column
    [Title] NVARCHAR(50) NOT NULL,
    [LookupColumn] INT NULL
);
GO

CREATE TABLE [dbo].[Table2]
(
    [Id] INT NOT NULL PRIMARY KEY, -- Primary Key column
    [Title] NVARCHAR(50) NOT NULL,
    [LookupColumn] INT NULL
);
GO

ALTER TABLE Table1
ADD FOREIGN KEY (LookupColumn) REFERENCES Table2 (Id);
GO

ALTER TABLE Table2
ADD FOREIGN KEY (LookupColumn) REFERENCES Table1 (Id);
GO

Then... One could ask why anyone would like to create a design like this with circular references, but that's another question 🙂

Forum Discussion

Copy list structure with circular lookup columns

Resources