Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 440
  • Last Modified:

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
0
bcrosby007
Asked:
bcrosby007
  • 9
  • 8
  • 5
1 Solution
 
_agx_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
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
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

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 9
  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now