Solved

New Checklist

Posted on 2011-02-15
22
436 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
  • 5
22 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 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 52

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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 52

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 52

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 52

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 52

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 52

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 52

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 52

Expert Comment

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

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

710 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