Sessions, <cfselect>, and adding new data

[sage703]

I've been working on this intranet data-entry wizard for some time now. Mxstu
has helped me out considerably, as have some other people in the community. I'm
still not done though! : )

To recap once again: There are many tables in the Access database, almost all
of them related to eachother. There is a select all related programs and data
page, using Coldfusion, for each table in the DB. There is one main table
however that requires more complex interactions and I use session variables and
a 'wizard' style format to allow users to enter new data.

I make frequent use of form select fields in the wizard. Each select field
draws distinct values from other, related tables. My current task is to design
a way that users could enter new data into the select field before they select
a value in the drop down box. For example, when selecting a certification from
the certifications table in the Add New Program Wizard, the user sees that the
certification they need for this program hasn't yet been added to that other
table yet (because the value is not available in the select box). What's an
easy way to allow the user to enter a new certification first, so that they can
select it in the select box in the wizard?

I tried just adding a link next to the select box to the 'add new
certifications' entry page but after the user adds the new certification (on a
new browser window), then closes that browser window and returns to the new
program wizard, and hits re-fresh, the select field is still not re-populated
with the new values.

Is there a simpler way to allow users to enter new data into other tables that
populate select boxes, that then update the contents of the select box?

S./

[mxstu]

Assuming the CFSELECT list is populated from a regular database query
(not-cached) and the "add new certification" page actually inserts the new
values into your database table... then I don't see any obvious reason why the
CFSELECT list would reflect the new values in the table after the page is
refreshed. I did a simple test and it seemed to work fine. Can you post the
code (just the parts that pertain to the certification list and adding a new
certification)?

There are ways to add items to a select list on-the-fly, either through
javascript or something like an activeX control. Of course using that method,
you would still have deal with adding the new values into the database at some
later point. A downside of using the javascript method is that since javascript
it is client-side you have less control over its behavior. If javascript is
disabled in the user's browser or something goes wrong, then your form breaks.

[sage703]

Ok, here's the code for the Certification select form element in the add a new
program wizard:

<!--- step eight: enter certification information --->
<cfcase value="8">
<!--- get list of all certifications from database --->
<cfquery name="cert" datasource="#data_source#">
SELECT DISTINCT Certification
FROM Certification
ORDER BY Certification
</cfquery>
<!--- show all certs in SELECT list --->
<!--- pre-select if user has chosen one --->
Select a certification:<br />
<i>(you may make multiple selections)</i><br />
<cfselect query="cert" name="Certification" value="Certification"
multiple="yes" size="5"
selected="#SESSION.ProgWiz.Certification#"/>

And here's the code for the adding a new certification page (not part of the
session wizard btw):

<cfinsert datasource="#data_source#" tablename="Certification"> (this is the
actual insert code - the page previous to it is where the actual data is
entered).

S./

[mxstu]

I'll take a look. Are you sure the values are getting inserted? If you
execute the SELECT DISTINCT... query right after the CFINSERT .. and CFDUMP the
values, is the new value actually there? Are you sure the browser isn't just
caching the main page?

[mxstu]

The test code below worked for me. I would verify on your popup page that the
new value was actually inserted into your db table. If the value is not being
inserted:

1) Try specifying the "FORMFIELDS" in the CFINSERT statement
2) Or as an alternative use a regular CFQUERY with an INSERT statement instead

There were a few
[url]http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=tn_18952[/url] with
cfinsert/cfupdate in MX6.1so you may want to check the knowledgebase for your
version of MX.




<!--- main form page --->
<!--- note: Certification is a text field --->
<cfquery name="cert" datasource="myAccessDSN">
SELECT Certification
FROM Certification
ORDER BY Certification
</cfquery>
<script>
function showAddPage() {
window.open('addNewCertification.cfm');
}
</script>
<cfform action="somePage.cfm" method="post">
<cfselect query="cert" name="Certification" value="Certification"
multiple="yes" size="5"/>
<a href="javascript:showAddPage();">Add New</a>
</cfform>

<!--- popup page: addNewCertification.cfm --->
<form action="addNewCertification.cfm" method="post">
Certification code: <input type="text" name="Certification">
<input type="submit">
</form>
<cfif IsDefined("form.Certification")>
<cfinsert datasource="#data_source#" tablename="Certification">
<script>
if (window.opener) {
window.opener.location.reload();
window.close();
}
</script>
</cfif>

[sage703]

That worked gloriously - I forgot how useful JavaScript could be. Thanks again mxstu!

S./