New Checklist

All - I am trying to create a dynamic checklist using Coldfusion and Access.
The basic premis... I want a user to create a new checklist. They enter a project number, then a new checklist get's created for that projnum. I need all of my checklist items to be written into a table in my database that has a relationship to that projnum.
Do i need to loop over my checklist entries? Please advise.

Below is my action code. I kind of suck at CF.
<cfset na="N/A">

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

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

INSERT INTO tbl_master_checklist
(checklist_id, checklist_item, checklist_status)
Values
(tbl_checklist.id, #items.checklist#, '#na#')

</cfquery>
</cfquery>

<cflocation url="/index.cfm">

Open in new window

Capture.JPG
LVL 7
bcrosby007Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
_agx_Connect With a Mentor Commented:
Do i need to loop over my checklist entries?

If I'm understanding your structure correctly - no.  Just use a SELECT to insert the values from your template table (tbl_checklist_items) into tbl_master_checklist

Once you get it working, you should use cfqueryparam for all of the values.

<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, 'N/A'
           FROM    tbl_checklist_items
           WHERE  checklist_cat='Both'
     </cfquery>
</cftransaction>
0
 
_agx_Commented:
Typo correction, this line:

         SELECT #getID.NewRecordID, 'N/A'

should be:

          SELECT #getID.NewRecordID#, checklist, 'N/A'
0
 
gdemariaCommented:

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>

Open in new window

0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
gdemariaCommented:
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
0
 
_agx_Commented:
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.
0
 
bcrosby007Author Commented:
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.
0
 
_agx_Commented:
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.

0
 
gdemariaCommented:
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


0
 
bcrosby007Author Commented:
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">

Open in new window

0
 
bcrosby007Author Commented:
First time trying a loop...
0
 
_agx_Commented:
You DON'T need a loop!  :)  Did you try the code I posted in my first response .. ?
0
 
gdemariaCommented:
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>

Open in new window

0
 
_agx_Commented:

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#')

Open in new window


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

     SELECT @@IDENTITY AS NewRecordID

Open 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'

Open in new window


Note: CFTRANSACTION is required !
0
 
bcrosby007Author Commented:
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">

Open in new window

Capture2.JPG
0
 
_agx_Commented:
Are my responses not showing up or something ... ?
0
 
bcrosby007Author Commented:
Error using the cftransaction code. ScreenShot
<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">

Open in new window

0
 
bcrosby007Author Commented:
Sorry AGX. I am trying your way now.
0
 
_agx_Commented:
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">
0
 
bcrosby007Author Commented:
Jackpot. Worked perfectly.
Thanks!
0
 
gdemariaCommented:
yes, try agx's code, it is correct
0
 
bcrosby007Author Commented:
Super easy once i see the code.
Thanks!
0
 
_agx_Commented:
You're welcome.  We finally got there in the end ;-)
0
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.

All Courses

From novice to tech pro — start learning today.