• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 146
  • Last Modified:

How do you create a new record in two tables where the information is linked from a single form

I am new to cold fusion and using cfmx. The data structure is company information in one table and addresses in another. I want to be able to input a new company with addresses from one form.
1 Solution
Do you have some sort of ID field relating the two tables? if so you could write two insert statements the first one would populate the first table Then I would query that table to get the relating id number and insert the rest of the data including the relating id ito table two. THis will make it easy to refrense the related info by filtering by id in your where statement.

Hope that helps
You will need to run a series of queries to update the database properly (you can either use the cfquery or cfinsert tag) - my recommendation (pseudocode) would be as follows:

- (assumption) the companyInfo table is the "master" table and contains a unique ID, and that ID is an autoincrement integer
- use cflock so that no other process can update the companyInfo table
- insert the data into the companyInfo table
- get the max unique ID from the companyInfo table
- un-cflock the companyInfo table
- (assumption) the companyAddress table may contain more than one address for any entry in the companyInfo table and each address has a unique ID, but also contains a foreign key that links to a record in the companyInfo table
- insert the data and the max unique ID of the previous query into the companyAddress table
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now