Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!

Report · Oct 27, 2009

I have two tables. The first is called Orders and contains a autonumber field named ID. This field is linked to a field named OrderID in another table named ProductOrders. I am using a submission form with a cfinclude to submit to both tables. There should only be one Orders.ID and numerous ProductOrders.OrderID (but they should be the same number). When I submit the form there are no error messages but the ProductOrders.OrderID is blank and therefore not linked to Orders.ID. What should I be looking for? I have linked the fields in Access in properties and relationships. What else?

Report · Oct 27, 2009

First question are you reading data or writing data?

I think you are writing data, if so you have to write the orderID to all the tables that need it. Just having the fields linked in the Access database properties and relationships is not going cause the value to propergate in some magical manner.

Report · Oct 27, 2009

Thanks for your reply Ian, and yes I am trying to write data. Bear with me, I'm teaching myself this stuff and know enough to frustrate the crap out of me and not much else. I don't understand how to instruct the database to enter the Orders.ID into all corresponding ProductOrders.OrderID. Since the Orders.ID generates on submission how do I carry it over to the other table?

Report · Oct 27, 2009

To get the order id of the record you just entered, do something like this:

select max(order_id) orderid

from your_table

where field1 = the value you just entered

and field2 = the value you just entered

etc

Report · Oct 27, 2009

Where would I place this code?

Report · Oct 27, 2009

Perhaps this will help, here is my code:

This is the input form (I stripped out the unecessary stuff and yes I realize the cfinclude appears after the submit button but it is within cfform tag and is submitting everything but the ID number):

<cfform action="mp_order_form2.cfm" method="post">
<cfoutput query="CheckUser">
<input type="hidden" name="UserID" value="#UserID#">
</cfoutput>
<p align="center"><img src="../images/mp_order_header.jpg" width="308" height="91" /></p>
<table width="650" border="0" align="center">
<tr>
    <td align="left" bgcolor="#FFFFFF"><font face="Tahoma"><strong>Sale Date*:</strong></font></td>
    <td align="left"><cfoutput>
      <input name="SaleDate" readonly="readonly" value="#DateFormat(Now(), "mm/dd/yyyy")#" />
      </cfoutput></td>
</tr>
<tr>
    <td align="left"><font face="Tahoma"><strong>TM Name*: </strong></font></td>
    <td align="left">
                        <cfoutput query="CheckUser">
                        <cfinput name="TerritoryManager" readonly="readonly" type="text" id="TerritoryManager" value="#UserFirstName# #UserLastName#" />
                        </cfoutput></td>
</tr>
<tr>
    <td align="left"><font face="Tahoma"><strong>Distributor*:</strong></font></td>
    <td align="left"><cfselect size="1" name="Distributor" required="Yes" message="Please select a distributor">
<cfoutput query="distributors">
<option value="#distributors.Field2#">
   #distributors.Field2# </option>
</cfoutput>
</cfselect></td>
</tr>
<tr>
     <td align="left"><font face="Tahoma"><strong>Dealer:</strong></font></td>
    <td align="left"> </td>
</tr>
<tr>
    <td align="left"><cfoutput query="dealer_info"><input type="hidden" name="DealerID" value="#dealer_info.ID#"></td></tr>
<tr>
    <td colspan="2" align="center" bgcolor="##CCCCCC"><font face="Tahoma" size="+1"><strong>#DealerName#</strong></font><br>
          <font face="Tahoma">#DealerAddress#</font><br>
          <font face="Tahoma">#DealerCity#, #DealerState# #DealerZIPCode#</font><br>
<br><br>
<font face="Tahoma">(#Left(DealerPhone,3)#) #Mid(DealerPhone,4,3)#-#Right(DealerPhone,4)#</font></td></tr></cfoutput>

<tr>
    <td align="left" class="style8"> </td>
    <td align="left"> </td>
</tr>
<tr>
    <td align="left"><font face="Tahoma"><strong>PO Number: </strong></font></td>
    <td align="left"><cfinput type="text" name="PONumber" /></td>
</tr>
<tr>
    <td align="left"><font face="Tahoma"><strong>Promotion:</strong></font></td>
    <td align="left">
      <cfselect name="Variable" size="1" id="Variable">
        <option value="No promotion" selected="selected">No promotion</option>
        <option value="A+ Promotion (Send Kit)">A+ Promotion (Send Kit)</option>
        <option value="A+ Promotion (Do Not Send Kit)">A+ Promotion (Do Not Send Kit)</option>
        <option value="Chick Days Pre-book">Chick Days Pre-book</option>
<option value="Fly Spray Pre-book">Fly Spray Pre-book</option>
<option value="ISO Program">ISO Program</option>
<option value="Promotion plus additional products">Promotion plus additional products</option>

      </cfselect></td>
</tr>
<tr>
    <td align="left"> </td>
    <td align="left"></td>
</tr>
<tr>
    <td align="left"><font face="Tahoma"><strong>ISO Check: </strong></font></td>
    <td align="left">
      <cfselect name="ISOCheck">
        <option value="Retail ISO Placement">Retail ISO Placement</option>
        <option value="DSR Spiff Check">DSR Spiff Check</option>
      </cfselect>
    </td>
</tr>
<tr>
    <td align="left"><font face="Tahoma"><strong>ISO Check Number: </strong></font></td>
    <td align="left"><cfinput type="text" name="ISOCheckNumber" /></td>
</tr>
<tr>
    <td align="left"><font face="Tahoma"><strong>ISO Check Amount: </strong></font></td>
    <td align="left">
      <cfinput type="text" name="ISOCheckAmount" />    </td>
</tr>
<tr>
    <td align="left"> </td>
    <td align="left"> </td>
</tr>
<tr>
    <td align="left"><font face="Tahoma"><strong>Customer Service Call Back: </strong></font></td>
    <td align="left">
      <cfselect name="CallBack">
   <option value=" " selected="selected"> </option>
   <option value="Yes">Yes</option>
   <option value="No">No</option>
      </cfselect>    </td>
</tr>
<tr>
    <td align="left"> </td>
    <td align="left"> </td>
</tr>
</table>

<hr align="center" width="65%" color="#999999">

   <table width="677" align="center">
<tr>
    <td height="22" colspan="4"><font face="Tahoma"><strong>Product Orders:</strong> </font></td>
</tr>
<tr>
    <td width="314" bgcolor="#CCCCCC"><div align="center"><font face="Tahoma" size="-1"><strong>Product</strong></font></div></td>
    <td width="62" bgcolor="#CCCCCC"><div align="center"><font face="Tahoma" size="-1"><strong>Qty</strong></font></div></td>
    <td width="124" bgcolor="#CCCCCC"><div align="center"><font face="Tahoma" size="-1"><strong>Size</strong></font></div></td>
    <td width="157" bgcolor="#CCCCCC"><div align="center"><font face="Tahoma" size="-1"><strong>New or Existing at Dealer</strong></font></div></td>
</tr>
   <tr>
     <td> </td>
     <td> </td>
     <td> </td>
     <td> </td>
   </tr>
<tr>
    <td colspan="4"><font face="Tahoma" size="+1"><strong>Comments:</strong></font></td>
    </tr>
<tr>
    <td colspan="4"> </td>
</tr>

<tr>
    <td colspan="4"><font face="Tahoma">* - indicates a required field that must be filled in to submit form</font> </td>
    </tr>
<tr>
    <td colspan="4">
      <div align="center">
        <input type="submit" value="Submit">
<input type="reset" value="Reset">
      </div>    </td>
</tr>
</table>
<cfinclude template="product_input.cfm" >
</cfform>
</table>

Here is the cfinclude template:

<table width="579">
<tr>
    <td>
      <cfselect name="Product">
     <option value="" selected="selected"> </option>
   <cfoutput query="products">
<option value="#products.Product#">#products.Product#</option>
   </cfoutput>
      </cfselect>
    </td>
    <td>
      <cfselect name="Quantity">
     <option value="" selected="selected"> </option>
   <cfoutput query="quantity">
        <option value="#quantity.Quantity#">#quantity.Quantity#</option>
   </cfoutput>
      </cfselect>
    </td>
    <td>
      <cfselect name="BagsCases" id="BagsCases">
    <option value=" " selected="selected"> </option>
       <option value="Bags / Eaches">Bags / Eaches</option>
       <option value="Cases">Cases</option>
    <option value="Pallet">Pallet</option>
      </cfselect>
    </td>
    <td>
     <cfselect name="NewExisting" id="NewExisting">
      <option value=" " selected="selected"> </option>
      <option value="New">New</option>
      <option value="Existing">Existing</option>
    </cfselect>
    </td>
<td><label>
   <input type="text" name="OrderComments" />
</label> </tr>
</table>

And finally, the action page:

Report · Oct 27, 2009

The usual process for this is something like this:

1) Insert data into parent table.

2) Query generated record ID out of parent table

3) Insert data into related child table(s) using that ID.

There are some dangers with the select max(id) with heavily utilized, enterprise database systems. But as access does not qualify as one of those, you should be ok to use this method.

Report · Oct 27, 2009

So I would have to abandon the cfinclude and present the child table form separately with the previous mentioned query?

Report · Oct 27, 2009

NO

You would just need to add a SELECT query between your two inserts that will retreive the ID from the first insert and then use that ID in the second insert.

Report · Oct 27, 2009

You would just need to add a SELECT query between your two inserts that will retreive the ID from the first insert and then use that ID in the second insert.

And do the whole lot as a transaction, so that the select is definitely querying for the record inserted by the first insert for it's request, not some other one that occurs at a similar time.

--

Adam

Report · Oct 27, 2009

I'm a little unclear on the SELECT statement, specifically the WHERE clause. Also, would the select statement be between query tags?

SELECT MAX(ID) AS OrderID
FROM Orders

where field1 = the value you just entered

and field2 = the value you just entered

Report · Oct 27, 2009

Yes the sql has to be in a cfquery tag. You'll also have to convert your 2nd cfinsert to a cfquery because the orderid is not coming from a form.

This:

where field1 = the value you just entered

and field2 = the value you just entered

would resemble something like

where saledate = <cfqueryparam value = "#form.saledate#">

and TerritoryManager = <cfqueryparam value = "#form.TerritoryManager#">

etc

If this is a school assignment, you might be ok. If it's for real, it needs a lot of work to userproof it.

Report · Oct 27, 2009

Okay...now I'm even more confused. Although the OrderID is not coming from a form all the other fields are. Can't I just address the OrderID somehow? I tried this but it doesn't work. What am missing here?

<cfquery name="qGetID" datasource="manna_premier">
SELECT MAX(ID) AS OrderID
FROM Orders
</cfquery>

</cftransaction>

Report · Oct 27, 2009

Thank you Dan and everyone else who contributed on this...YOU GUYS ARE DA BOMB!!!! This is the code that cured the problem. Thanks again1

<cfquery name="qGetID" datasource="manna_premier">
SELECT MAX(ID) AS OrderID
FROM Orders
</cfquery>

<cfquery name="AddProduct" datasource="manna_premier">
INSERT INTO ProductOrders
VALUES ('#qGetID.OrderID#', '#Form.Product#','#Form.Quantity#', '#Form.BagsCases#','#Form.NewExisting#', '#Form.OrderComments#')
</cfquery>

</cftransaction>

Report · Oct 28, 2009

<cfquery name="qGetID" datasource="manna_premier">
SELECT MAX(ID) AS OrderID
FROM Orders
</cfquery>

You may want to read the comments again. That is not the query that was suggested.

Report · Oct 28, 2009

Yeah I know. The query I wrote worked until I added more than one record at a time...then it forced all the records I entered into one line. I now realize that the query is generating only one number. I only want one number, but I need multiple instances of that number. Any thoughts?

Report · Oct 28, 2009

then it forced all the records I entered into one line.

I am not sure what you mean by that. My comment was that this query:

SELECT MAX(ID) AS OrderID

FROM Orders

Is not the proper way to get the new Autonumber value created by the previous insert statement. That query would return the maximum record ID in that table, regardless of when it was entered.

Report · Oct 28, 2009

What is the proper way to retrieve the new Autonumber? What I meant was that now when I enter multiple products I get this result in my database;

OrderID	Products	Quantity	BagsCases	NewExisting	OrderComments
1	Product, Product, Product, Product	10,12,6,8	Bags, Bags, Cases, Bags	Existing,New,New,New	test,test,test,test

I need it to look like this;

OrderID	Products	Quantity	BagsCases	NewExisting	OrderComments
1	Product	10		Existing	test
1	Product	12		New	test
1	Product	6		New	test
1	Product	8		New	test

How do I do this? What am I doing wrong?

Report · Oct 28, 2009

Is this for a live application or a school assignment? MS Access is not really recommended for live applications. Plus, as Dan already mentioned your code needs work to make it user proof.

As far as grabbing the Autonumber value, there are a few options for Access. It is important to grab the correct ID, or you will end up linking information under the wrong record totally corrupting your data.

1. One option for MS Access is to use @@IDENTITY. SELECT this value directly after the first insert to retrieve the new Autonumber created in the previous statement

2) Dan described another option for grabbing the inserted Autonumber value. While I am not a fan of this method, it can work in some situations.

Your current query does not properly implement that approach. It simply grabs the MAX id in the entire table. That is not necessarYou do not want the MAX id in the entire table. You want the ID of the record you just inserted. To identify that record, you need to filter on the key values you just entered.

ie

SELECT MAX(ID) AS OrderID FROM SomeTable

WHERE SaleDate = '#form.SaleDate#'

AND TerritoryManager = '#form.TerritoryManager#'

.... etcetera ...

3. Another option is to use a UUID value:

http://mysecretbase.com/get_the_last_id.cfm

What I meant was that now when I enter multiple products I get
this result in my database;

That sounds like it has more to do with your FORM, than the query. When you give multiple form fields the same name, the values will be sent to the action page as a comma delimited list. On your action page, dump the FORM scope so you can see what values are submitted. If the fields contain multiple values like "10,12,6,8", the problem is your form.

<cfdump var="#FORM#"

Report · Oct 28, 2009

Where could I find an example of the type of form I need to use? My form is returning a comma delimited list but I don't know how to work around this. How would I structure the input form to accept multiple products with the same OrderID number? Any help on this would be greatly appreciated.

Report · Oct 28, 2009

You can use cfloop list ="#form.something#" to handle your lists. If the list comes from checkboxes, you'll need to ensure that at least one of the checkboxes was checked.

Report · Oct 28, 2009

Dan Bracuk wrote:
You can use cfloop list ="#form.something#" to handle your lists.

The only problem with that method is it breaks down if the form field values contain commas. With forms that add/edit multiple entries, I prefer using separate field names. Fields generated by a query loop can be named dynamically using the query objects #currentRow# variable.

That will produces field names like FirstName1, FirstName2, etcetera. The total number of fields can be stored in a hidden field _outside_ the query loop:

On the action page, retrieve the submitted field values individually using a loop and associative array notation:

<cfparam name="form.numOfFields" default="0">
<cfloop from="1" to="#form.numOfFields#" index="counter">
      <cfset firstNameValue = FORM["FirstName"& counter]>
      <cfset lastNameValue = FORM["LastName"& counter]>
      ... use the values in a query ...
</cfloop>

Report · Oct 28, 2009

Okay...I now fully understand the issues surrounding the query to retrieve the ID number. I want to do this right and it looks like @@IDENTITY is the way to go. Problem is I have been unsuccessful finding any examples or documentation of this as it relates to ColdFusion. I have a hard enough time deciphering CF queries much less those written in other programming languages. Is this the correct way to write and use this;

<cfquery name="qGetID" datasource="manna_premier">
SELECT @@IDENTITY AS Identity
FROM Orders
</cfquery>

When I run this, and am entering only one single product it appears to perform correctly. I just want to make sure that this part is correct before I move on to tackle the issue of multiple entries. Thanks for all your help guys, especially '-==cfSearching==-', I'd love to pick your brain for a week or two! LOL!!

Report · Oct 28, 2009

emartek1 wrote:
<cfquery name="qGetID" datasource="manna_premier">
SELECT @@IDENTITY AS Identity
FROM Orders
</cfquery>

@@Identity is an independent variable, so you do not need the FROM clause. Just select the variable and give it whatever column alias you want.

<cfquery name="qGetID" datasource="manna_premier">
SELECT @@IDENTITY AS NewOrderID
</cfquery>

Report · Oct 28, 2009

How does @@IDENTITY know what it is supposed to retrieve? Is it because of its location (sandwiched between my insert queries) that it knows what table to retrieve the record from?

Also, I think I understand the base concept you described for the next step in the process...the input form. The one area I don't quite get is the query for inputting the field data. My current form uses 4 drop-down lists and 1 text box. I want a maximum of 20 entries. Would I repeat the query 20 times to make the input form or wrap all 20 in one query, or is their a way to dynamically generate this?

Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!

1 Correct answer

Photos