[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

New Checklist

Posted on 2011-02-15
22
Medium Priority
?
441 Views
Last Modified: 2013-12-24
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
0
Comment
Question by:bcrosby007
  • 9
  • 8
  • 5
22 Comments
 
LVL 53

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 34898364
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
 
LVL 53

Expert Comment

by:_agx_
ID: 34898375
Typo correction, this line:

         SELECT #getID.NewRecordID, 'N/A'

should be:

          SELECT #getID.NewRecordID#, checklist, 'N/A'
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34898383

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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
LVL 39

Expert Comment

by:gdemaria
ID: 34898406
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
 
LVL 53

Expert Comment

by:_agx_
ID: 34898453
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
 
LVL 7

Author Comment

by:bcrosby007
ID: 34898490
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
 
LVL 53

Expert Comment

by:_agx_
ID: 34898627
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 34898675
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
 
LVL 7

Author Comment

by:bcrosby007
ID: 34899097
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
 
LVL 7

Author Comment

by:bcrosby007
ID: 34899101
First time trying a loop...
0
 
LVL 53

Expert Comment

by:_agx_
ID: 34899149
You DON'T need a loop!  :)  Did you try the code I posted in my first response .. ?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34899178
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
 
LVL 53

Expert Comment

by:_agx_
ID: 34899286

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
 
LVL 7

Author Comment

by:bcrosby007
ID: 34899289
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
 
LVL 53

Expert Comment

by:_agx_
ID: 34899303
Are my responses not showing up or something ... ?
0
 
LVL 7

Author Comment

by:bcrosby007
ID: 34899338
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
 
LVL 7

Author Comment

by:bcrosby007
ID: 34899351
Sorry AGX. I am trying your way now.
0
 
LVL 53

Expert Comment

by:_agx_
ID: 34899363
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
 
LVL 7

Author Comment

by:bcrosby007
ID: 34899382
Jackpot. Worked perfectly.
Thanks!
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34899388
yes, try agx's code, it is correct
0
 
LVL 7

Author Closing Comment

by:bcrosby007
ID: 34899399
Super easy once i see the code.
Thanks!
0
 
LVL 53

Expert Comment

by:_agx_
ID: 34899449
You're welcome.  We finally got there in the end ;-)
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The following information will get you familiar with your new DV server, including the (mt) Account Center, the Plesk Control Panel, our world-renowned support department and the rest of the (mt) tools that come with your new service.
The Super Bowl is just days away. Millions of advertising dollars will be spent in just a few hours to drive people to websites around the globe. Optimizing your site in anticipation of a big event like this (and the traffic surges that follow) will…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Free Data Recovery software is an advanced solution from Kernel Tools to recover data and files such as documents, emails, database, media and pictures, etc. It supports recovery from physical & logical drive after a hard disk crash, accidental/inte…
Suggested Courses
Course of the Month9 days, 3 hours left to enroll

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question