How to populate one column based on another column

JLueders

I have a Jobs table I am trying to populate that has the columns Id, FacilityId, and ContactId (among others).  There is a Contacts table that has Id, Name, and FacilityID.  When generating data for the Jobs table, I want to make sure the ContactId is valid for the FacilityId based on the Contacts table.  In other words, I want the ContactId and FacilityId combination to exist in the Contacts table.  Is this possible?

hassan_imam

Hi JLueders,

Thank you for reaching out regarding SQL Data Generator. For aligning the ContactId and FacilityId combination in the Jobs table with valid entries in the Contacts table, I recommend utilizing the automatic "Foreign Key generator". This option seamlessly maps and references the primary key relationships in your database, ensuring data consistency.

For a more information, please refer to our documentation: Using Generators (SQL Data Generator Documentation)

Warm regards,

Hassan
Product Support Engineer

JLueders

I did use the automatic Foreign Key generator for the Contact.  The Facility ID uses the generator as it has a foreign key tying it to the Facilities table.  When the Jobs table is populated, it has a valid Contact and Facility, but the Facility ID might not be the facility that is assigned to the contact in the Contacts table.

hassan_imam

Hi JLueders,

Thank you for providing clarity on the issue. To ensure that the FacilityId in the Jobs table matches the one associated with the chosen ContactId in the Contacts table, we need a custom solution. For the FacilityId column in the Jobs table, instead of using the default foreign key generator (which just ensures it's a valid facility), use the "SQL Statement generator." The statement will fetch the corresponding FacilityId for the generated ContactId. The SQL statement would be something like:

• SELECT FacilityId FROM Contacts WHERE Id = [Jobs].[ContactId]

JLueders

I tried your SQL Statement and got the error: An error occurred.  The multi-part identifier "Jobs.ContactId" could not be bound.

I also tried the following:

SELECT Contacts.FacilityId FROM Contacts 

join Jobs on Contacts.Id = Jobs.ContactId

This gives the message "Not enough rows for column 'FacilityId' in the preview.

hassan_imam

Hi JLueders,

Thank you for reaching out regarding SQL Data Generator. For aligning the ContactId and FacilityId combination in the Jobs table with valid entries in the Contacts table, I recommend utilizing the automatic "Foreign Key generator". This option seamlessly maps and references the primary key relationships in your database, ensuring data consistency.

For a more information, please refer to our documentation: Using Generators (SQL Data Generator Documentation)

Warm regards,

Hassan
Product Support Engineer

JLueders

As I said before, the automatic Foreign Key generator will not work in this instance.  What I need is to have the Facility ID based on the Contact ID.  Using the SQL Statement generator makes sense, but I got an error when I tried the statement you provided.

JLueders

Any other ideas on how to accomplish this?

hassan_imam

Hi Leuders,

I sincerely apologize for the delay in addressing your concern. Regarding the error message "Not enough rows for column 'FacilityId'", Im not seeing this on my end. Could you kindly share the DDL for your tables? This will enable me to replicate and further investigate the matter.

All the best,

Hassan 
Product Support Engineer

JLueders

Here is the DDL for the 3 tables:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Facilities](

[Id] [int] IDENTITY(1,1) NOT NULL,

[Name] [nvarchar](1000) NOT NULL,

[StreetAddress] [nvarchar](1000) NOT NULL,

[City] [nvarchar](1000) NOT NULL,

[ZipCode] [nvarchar](20) NOT NULL,

[PhoneNumber] [nvarchar](100) NOT NULL,

[MailingAddress] [nvarchar](1000) NOT NULL,

[MailingCity] [nvarchar](1000) NOT NULL,

[MailingZipCode] [nvarchar](20) NOT NULL,

[StateId] [int] NOT NULL,

 CONSTRAINT [PK_Facilities] PRIMARY KEY CLUSTERED 

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE TABLE [dbo].[Contacts](

[Id] [int] IDENTITY(1,1) NOT NULL,

[FacilityId] [int] NULL,

[FirstName] [nvarchar](200) NOT NULL,

[LastName] [nvarchar](200) NOT NULL,

[Email] [nvarchar](200) NOT NULL,

[WorkPhone] [nvarchar](100) NOT NULL,

[CellPhone] [nvarchar](100) NOT NULL,

[Extension] [nvarchar](100) NOT NULL,

 CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED 

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Contacts]  WITH CHECK ADD  CONSTRAINT [FK_Contacts_Facilities_FacilityId] FOREIGN KEY([FacilityId])

REFERENCES [dbo].[Facilities] ([Id])

GO

ALTER TABLE [dbo].[Contacts] CHECK CONSTRAINT [FK_Contacts_Facilities_FacilityId]

GO

CREATE TABLE [dbo].[Jobs](

[Id] [int] IDENTITY(1,1) NOT NULL,

[FacilityId] [int] NULL,

[DisciplineSpecialtyId] [int] NOT NULL,

[JobTypeId] [int] NOT NULL,

[ContactId] [int] NOT NULL,

[Active] [bit] NOT NULL,

 CONSTRAINT [PK_Jobs] PRIMARY KEY CLUSTERED 

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[Jobs]  WITH CHECK ADD  CONSTRAINT [FK_Jobs_Contacts_ContactId] FOREIGN KEY([ContactId])

REFERENCES [dbo].[Contacts] ([Id])

GO

ALTER TABLE [dbo].[Jobs] CHECK CONSTRAINT [FK_Jobs_Contacts_ContactId]

GO

ALTER TABLE [dbo].[Jobs]  WITH CHECK ADD  CONSTRAINT [FK_Jobs_Facilities_FacilityId] FOREIGN KEY([FacilityId])

REFERENCES [dbo].[Facilities] ([Id])

GO

ALTER TABLE [dbo].[Jobs] CHECK CONSTRAINT [FK_Jobs_Facilities_FacilityId]

GO

JLueders

I just realized there has been a change to the Jobs table since I got the error and it now causes a different problem.  The FacilityId has been changed to allow NULL values.  Now, I get only NULLs for the FacilityId when using the below query for FacilityId and the automatic Foreign Key generator for ContactId.

SELECT Contacts.FacilityId FROM Contacts 

join Jobs on Contacts.Id = Jobs.ContactId

Sorry I didn't realize this sooner.  The DDL I sent is correct.

hassan_imam

Hey JLueders,

Thank you for your patience and providing the DDL for your tables. I'll need a bit more time to thoroughly test and ensure we have a script that works seamlessly for your requirements. Please bear with me; I should have a solution ready for you by Thursday.

Warm regards,

Hassan

Product Support Engineer

hassan_imam

Hi JLueders,

Thank you for your patience and for providing detailed information regarding your table structures.

Regarding the challenge you're facing with the FacilityId and ContactId combination  When a column is set with the IDENTITY property, it's controlled by the server to auto-generate values. This ensures uniqueness and is typically used for primary key columns.

If you're trying to generate or insert specific values into an identity column, such as Id in your Jobs table, In order to generate into that column, you need to remove that IDENTITY constraint. After that is removed you can move forward without using SQL Statements and just set the relation to user defined foreign key for the column

Best, 

Hassan 

Product Support Engineer

JLueders

I am not trying to set the Id column in the Jobs table.  What I am trying to do is set the ContactId based on the FacilityId in the Contacts table.  Is it possible to use a generated value in a table in a SQL Query for another column in the same table.

hassan_imam

Hi JLueders,

Apologies for the extended delay. During the process of finding a solution, a few bugs were encountered that might have been causing the issue.

To begin, it’s important to note that using SQL Statement options won’t be necessary. Instead, some changes need to be made in the job table. Specifically, in the default foreign key (Automatic) Column Generation settings, the population method should be set to “All Key Values Unique”. Additionally, the ‘allow nulls’ box for both of the foreign keys, ‘contactid’ and ‘facilityid’, should be unchecked.

This should help in resolving the issue.

Best regards,

Hassan 

Product Support Engineer

JLueders

This did not work.  The foreign key for FacilityId references the Facilities table while the ContactId references the Contacts table.  I tried using Foreign Key (manual) for FacilityId and selecting the Contacts table, but could not get that to work.

I was able to make it work using a SQL Statement.

One other question: When using a SQL Statement for a column, is there a way to have it use the same connection as the project?

hassan_imam

HI JLueders,

I would like to clarify your requirement. Are you suggesting that instead of manually choosing a database for each SQL statement, we set a default database aligned with the current project?

Best, 

Hassan 

JLueders

Hassan,

That is correct.  This way, if I need to generate data for a different database, I only have to change the default database for the project and not go into every column that uses a SQL Statement.

Jeff

hassan_imam

JLueders, 

I regret to inform you that the option you're inquiring about is not available at this time. Nonetheless, I recognize the value it could add to streamlining the project. Rest assured, I will raise this matter with our development team for consideration.

Thank you, 

Hassan
Product Support Engineer