New Checklist

Typo correction, this line:

         SELECT #getID.NewRecordID, 'N/A'

should be:

          SELECT #getID.NewRecordID#, checklist, 'N/A'

Yes, you would need to loop over your checklist items to insert them into the tables.   The format is a bit more like I'm showing below.

We can help guide you through this project if you like, I am a bit confused by your table structure, can we start there?

Project - it seems this is the top level table that holds the project informaton

You have MASTER_CHECKLIST, CHECKLIST and CHECKLIST_ITEMS

Does this mean that there is a Master or General list of items that every project will use and that is stored in the MASTER?

Then the Project has it's COPY of the master checklist.

Perhaps a small example of the data?

Also, one tip - you should name the ID field for each table with the Table Name, not just ID.  It will become very confusing when you start joining tables.

Project_ID
CheckList_ID
etc..

<cfquery datasource="projects" name="addNewChecklist">
  INSERT INTO tbl_checklist  (proj_num)
  Values ('#form.projnum#')
</cfquery>

<cfloop .. for each checklist item ... >
  <cfquery datasource="projects" name="newcachecklist">
   INSERT INTO tbl_master_checklist 
    (checklist_id, checklist_item, checklist_status)
   Values
    (tbl_checklist.id, #items.checklist#, '#na#')
  </cfquery>
</cfloop>

Select allOpen in new window

I was thinking this would be a form with a list of items and checkboxes, perhaps you could describe what the form page would look like

I am a bit confused by your table structure

I agree. I'd expect the MASTER table to be a template for all projects.  But I think some samples and maybe a screen shot of the form page would help clarify.

The form page to add a new project would be simply a text box wth submit button. The submit passes the projnum variable to the action page where a single entry gets put in the tbl_checklist table. Then the tbl_master_checklist gets populated with the checklist_id from the tbl_checklist in every row as well as all items in the tbl_checklist_items.

Then the code in my 1st response should do what you need.

But .. if it were me, I'd rename the tables.  The name "Master" typically implies the table is a template of some sort.  In your schema it's the reverse.

I agree, perhaps something like this:

MasterListItems
  - MasterItem_id

Projects
  - Project_id
ProjectList
  - ProjectList_Id
  - Project_Id  -- foreign Key toProject table
ProjectListItems
  - ListItem_id
  - ProjectList_id -- foreign key to list table
  - MasterItem_id -- foreign key to master checklist table

Here is where i am now. Can you expand on the cfloop?

<cfset na="N/A">
<cfquery datasource="projects" name="newcachecklist">
INSERT INTO tbl_checklist 
(proj_num)
Values
('#form.projnum#');
</cfquery>

<cfquery datasource="projects" name="items">
Select * From tbl_checklist_items Where checklist_cat="Both"
</cfquery>

<cfquery datasource="projects" name="getnewprojinfo">
Select * from tbl_checklist where projnum='#form.projnum#'
</cfquery>

<cfloop >
<cfoutput query="getnewprojectinfo">
<cfquery datasource="projects" name="populatechecklist">
INSERT INTO tbl_master_checklist
(checklist_id, checklist_item, checklist_status)
Values
(getnewprojectinfo.id, #items.checklist#, '#na#')
</cfquery>
</cfoutput>
</cfloop>


<cflocation url="/index.cfm">

Select allOpen in new window

First time trying a loop...

You DON'T need a loop!  :)  Did you try the code I posted in my first response .. ?

Right, I was thinking you were doing something different, you don't need the loop.

Agx shows you how to insert into a table selecting from another table

based on your latest code, i think it may be something like this..

<cfquery datasource="projects" name="populatechecklist">
  insert into  tbl_master_checklist  (checklist_id, checklist_item, checklist_status)
  select getnewprojectinfo.id, #items.checklist#, '#na#'
  from tbl_checklist 
  where projnum='#form.projnum#'
</cfquery>

Select allOpen in new window

Guys, barring any additional typos ;-) the code original code should do everything ... exactly as is.

The 1st query inserts your project

  INSERT INTO tbl_checklist (proj_num)
  VALUES ('#form.projnum#')

Select allOpen in new window

The 2nd query get's the ID of that record (ie autonumber value).  

     SELECT @@IDENTITY AS NewRecordID

Select allOpen in new window

The 3rd query copies all the records from your tbl_checklist_items table into  tbl_master_checklist

         INSERT INTO tbl_master_checklist (checklist_id, checklist_item, checklist_status)
         SELECT #getID.NewRecordID#, checklist, 'N/A'
         FROM    tbl_checklist_items
         WHERE  checklist_cat='Both'

Select allOpen in new window

Note: CFTRANSACTION is required !

Ok. Here is where i am plus my error... I think I am close. Maybe...

<cfset na="N/A">
<cfquery datasource="projects" name="newcachecklist">
INSERT INTO tbl_checklist 
(proj_num)
Values
('#form.projnum#');
</cfquery>

<cfquery datasource="projects" name="items">
Select * From tbl_checklist_items Where checklist_cat="Both"
</cfquery>

<cfquery datasource="projects" name="getnewprojinfo">
Select * from tbl_checklist where proj_num='#form.projnum#'
</cfquery>


<cfquery datasource="projects" name="populatechecklist">
insert into  tbl_master_checklist  (checklist_id, checklist_item, checklist_status)
  select getnewprojectinfo.id, #items.checklist#, '#na#'
  from tbl_checklist 
  where proj_num='#form.projnum#'

</cfquery>




<cflocation url="/index.cfm">

Select allOpen in new window

Capture2.JPG

Are my responses not showing up or something ... ?

Error using the cftransaction code.

<cftransaction>
     <cfquery datasource="projects" name="newcachecklist">
        INSERT INTO tbl_checklist (proj_num)
        VALUES ('#form.projnum#')
     </cfquery>
     <cfquery datasource="getID" name="newcachecklist">
         SELECT @@IDENTITY AS NewRecordID
     </cfquery>
     <cfquery datasource="addItems" name="newcachecklist">
           INSERT INTO tbl_master_checklist (checklist_id, checklist_item, checklist_status)
           SELECT #getID.NewRecordID#, checklist, 'N/A'
           FROM    tbl_checklist_items 
           WHERE  checklist_cat='Both'
     </cfquery>
</cftransaction>


<cflocation url="/index.cfm">

Select allOpen in new window

Sorry AGX. I am trying your way now.

The dsn is swapped with the query name. Change these lines:

     <cfquery datasource="projects" name="newcachecklist">
     <cfquery datasource="getID" name="newcachecklist">
     <cfquery datasource="addItems" name="newcachecklist">

To

     <cfquery datasource="projects" name="newcachecklist">
     <cfquery datasource="projects" name="getID">
     <cfquery datasource="projects" name="addItems">

Jackpot. Worked perfectly.
Thanks!

yes, try agx's code, it is correct

Super easy once i see the code.
Thanks!

You're welcome.  We finally got there in the end ;-)