XrmToolbox and dbdiagram.io
Use XrmToolbox and dbdiagram.io Tools for ERD
- Connect to Dataverse using XrmToolbox (https://www.xrmtoolbox.com/)
- Generate 'script' of your selected entities like account and contact etc. https://dbdiagram.io/
- Paste the script to dbdiagram.io and export, publish, or share the ERD of your Dataverse Entity Relationships
- When Exporting, it even creates the SQL statements to create tables if needed.
(You don't need them for Dataverse, but you can feed them to Copilot or ChatGPT to generate 'Dataverse' fields instead for your own Tables.
Example:
Export Example for SQL:
CREATE TABLE [account] (
[accountid] uniqueidentifier PRIMARY KEY,
[masterid] lookup,
[msa_managingpartnerid] lookup,
[parentaccountid] lookup,
[primarycontactid] lookup
)
GO
CREATE TABLE [contact] (
[contactid] uniqueidentifier PRIMARY KEY,
[accountid] lookup,
[masterid] lookup,
[msa_managingpartnerid] lookup,
[parentcontactid] lookup
)
GO
ALTER TABLE [account] ADD FOREIGN KEY ([masterid]) REFERENCES [account] ([accountid])
GO
ALTER TABLE [account] ADD FOREIGN KEY ([msa_managingpartnerid]) REFERENCES [account] ([accountid])
GO
ALTER TABLE [account] ADD FOREIGN KEY ([parentaccountid]) REFERENCES [account] ([accountid])
GO
ALTER TABLE [account] ADD FOREIGN KEY ([primarycontactid]) REFERENCES [contact] ([contactid])
GO
ALTER TABLE [contact] ADD FOREIGN KEY ([accountid]) REFERENCES [account] ([accountid])
GO
ALTER TABLE [contact] ADD FOREIGN KEY ([masterid]) REFERENCES [contact] ([contactid])
GO
ALTER TABLE [contact] ADD FOREIGN KEY ([msa_managingpartnerid]) REFERENCES [account] ([accountid])
GO
ALTER TABLE [contact] ADD FOREIGN KEY ([parentcontactid]) REFERENCES [contact] ([contactid])
GO
https://chat.openai.com/
Ask ChatGPT to convert it for you to Dataverse: not quite, but at least it gave us lookup
Can you reformat SQL Table Create Scripts below to Datavers format please: Here is the SQL Scripts. -- Account Table CREATE TABLE [account] ( [accountid] uniqueidentifier PRIMARY KEY, [masterid] lookup, [msa_managingpartnerid] lookup, [parentaccountid] lookup, [primarycontactid] lookup ); GO -- Contact Table CREATE TABLE [contact] ( [contactid] uniqueidentifier PRIMARY KEY, [accountid] lookup, [masterid] lookup, [msa_managingpartnerid] lookup, [parentcontactid] lookup ); GO -- Foreign Key Constraints ALTER TABLE [account] ADD FOREIGN KEY ([masterid]) REFERENCES [account] ([accountid]); GO ALTER TABLE [account] ADD FOREIGN KEY ([msa_managingpartnerid]) REFERENCES [account] ([accountid]); GO ALTER TABLE [account] ADD FOREIGN KEY ([parentaccountid]) REFERENCES [account] ([accountid]); GO ALTER TABLE [account] ADD FOREIGN KEY ([primarycontactid]) REFERENCES [contact] ([contactid]); GO ALTER TABLE [contact] ADD FOREIGN KEY ([accountid]) REFERENCES [account] ([accountid]); GO ALTER TABLE [contact] ADD FOREIGN KEY ([masterid]) REFERENCES [contact] ([contactid]); GO ALTER TABLE [contact] ADD FOREIGN KEY ([msa_managingpartnerid]) REFERENCES [account] ([accountid]); GO ALTER TABLE [contact] ADD FOREIGN KEY ([parentcontactid]) REFERENCES [contact] ([contactid]); GO
-----------------------------------------------------------------------------------------------------------------------------can you add the data types used in Dataverse? Can you reformat SQL Table Create Scripts below to Datavers format please: Here is the SQL Scripts. -- Account Table CREATE TABLE [account] ( [accountid] uniqueidentifier PRIMARY KEY, [masterid] lookup, [msa_managingpartnerid] lookup, [parentaccountid] lookup, [primarycontactid] lookup ); GO -- Contact Table CREATE TABLE [contact] ( [contactid] uniqueidentifier PRIMARY KEY, [accountid] lookup, [masterid] lookup, [msa_managingpartnerid] lookup, [parentcontactid] lookup ); GO -- Foreign Key Constraints ALTER TABLE [account] ADD FOREIGN KEY ([masterid]) REFERENCES [account] ([accountid]); GO ALTER TABLE [account] ADD FOREIGN KEY ([msa_managingpartnerid]) REFERENCES [account] ([accountid]); GO ALTER TABLE [account] ADD FOREIGN KEY ([parentaccountid]) REFERENCES [account] ([accountid]); GO ALTER TABLE [account] ADD FOREIGN KEY ([primarycontactid]) REFERENCES [contact] ([contactid]); GO ALTER TABLE [contact] ADD FOREIGN KEY ([accountid]) REFERENCES [account] ([accountid]); GO ALTER TABLE [contact] ADD FOREIGN KEY ([masterid]) REFERENCES [contact] ([contactid]); GO ALTER TABLE [contact] ADD FOREIGN KEY ([msa_managingpartnerid]) REFERENCES [account] ([accountid]); GO ALTER TABLE [contact] ADD FOREIGN KEY ([parentcontactid]) REFERENCES [contact] ([contactid]); GO
----------------------------------------------------
-- Account Table
CREATE TABLE [account]
(
[accountid] uniqueidentifier PRIMARY KEY,
[masterid] uniqueidentifier,
[msa_managingpartnerid] uniqueidentifier,
[parentaccountid] uniqueidentifier,
[primarycontactid] uniqueidentifier
);
GO
-- Contact Table
CREATE TABLE [contact]
(
[contactid] uniqueidentifier PRIMARY KEY,
[accountid] uniqueidentifier,
[masterid] uniqueidentifier,
[msa_managingpartnerid] uniqueidentifier,
[parentcontactid] uniqueidentifier
);
GO
-- Foreign Key Constraints
ALTER TABLE [account] ADD FOREIGN KEY ([masterid]) REFERENCES [account] ([accountid]);
GO
ALTER TABLE [account] ADD FOREIGN KEY ([msa_managingpartnerid]) REFERENCES [account] ([accountid]);
GO
ALTER TABLE [account] ADD FOREIGN KEY ([parentaccountid]) REFERENCES [account] ([accountid]);
GO
ALTER TABLE [account] ADD FOREIGN KEY ([primarycontactid]) REFERENCES [contact] ([contactid]);
GO
ALTER TABLE [contact] ADD FOREIGN KEY ([accountid]) REFERENCES [account] ([accountid]);
GO
ALTER TABLE [contact] ADD FOREIGN KEY ([masterid]) REFERENCES [contact] ([contactid]);
GO
ALTER TABLE [contact] ADD FOREIGN KEY ([msa_managingpartnerid]) REFERENCES [account] ([accountid]);
GO
ALTER TABLE [contact] ADD FOREIGN KEY ([parentcontactid]) REFERENCES [contact] ([contactid]);
GO
-------------------------------------------------------------------------------
chatgpt replaced the
lookup data type with uniqueidentifier for the columns that represent foreign keys, as Dataverse typically uses the uniqueidentifier data type for these fields.
=========================================================
Comments
Post a Comment