Append Text to Notes field, including User, Date & Time

[jlig]

Any help on creating this User/Date/TimeStamp this would be much appreciated..

I have the following objects on my page:

Two Text boxes:

• txtNewNote (this is a textarea field to type in the new notes)
• wkRptOrdStaNts (this is the actual Database field where notes are stored)

One Submit button:

• btnAppend (this is to run the Javascript code that takes the content in the txtNewNote box & appends it to the wkRptOrdStaNts field contents, along with a User, Date & Time stamp)

Here is an example of what a final "appended text" entry should look like:

[02/22/2010 1:17 pm by: joe_user] New notes, blah, blah, etc..

I found this javascript code : http://www.mediacollege.com/internet/javascript/form/add-text.html, but not sure how to make it work with date/time/user.

btw: I can pull the logged in user from a Session variable: #Session.kt_login_user#

Thanks,

jlig

[Dan_Bracuk]

I suggest using coldfusion instead of javascript.  To do exactly what you say you want to do, you can use dateformat(), timeformat(), and now() to get the date and time stuff you need.  You already have the user stuff.

You might want to look at your database design.  If your table has a date_entered field, or something like that, defaulted to the current date and time, everything will look after itself.  You could also store use info in a different column.  That will give you more flexibility for querys.

[jlig]

Dan, Thanks for the reply..

Good idea..

I do have access to the Logged-in User via the {Session.kt_login_user} and the Date & Time via {now_dt()}, so this makes sense to just "Append" those values to my Text field before posting.

What would be the general code to place behind the Submit Button to make sure my text string is "Appended" to my existing Notes field, and that it doesn't "Overwrite" the previous notes?

Thanks again,

jlig

[jlig]

Using the following code in my existing Query, I was able to create the User-Date-Time-Stamp text string portion:

-------------------------------------------------------------------------------

<cfquery name="rsStamp" datasource="salesPipeline">
SELECT tblWeeklyRpt.wkRptID, tblWeeklyRpt.wkRptCustName, user_usr.name_usr, tblWeeklyRpt.wkRptOrdStaNts,
CONCAT(date_format(NOW(), '%a, %b %D, %Y. %l:%i %p'), " by: ",(user_usr.name_usr), "] ") AS stamp
FROM (tblWeeklyRpt INNER JOIN user_usr ON user_usr.id_usr=tblWeeklyRpt.wkRptUser)
WHERE wkRptID = <cfqueryparam value="#URL.id_com#" cfsqltype="cf_sql_numeric">
</cfquery>

-------------------------------------------------------------------------------

This produces the following value: Wed, Mar 3rd, 2010. 11:34 AM by: mgoodman]

Question: How do I take this value, along with the "text" from my TextBox named "inputtext", and PRE-APPEND it to my existing Notes field called "outputtext"?

Here is a sample of what the page looks like: The first box is named: "inputtext", the second is "outputtext"

[Dan_Bracuk]

Regarding:

so this makes sense to just "Append" those values to my Text field before posting.

To me, it makes more sense to store the user and timestamp information separately.  You never know when you might have to run a query based on what happened between Date A and Date B, or it you want display those timestamps in another format.

Regarding:

What would be the general code to place behind the Submit Button to make sure my text string is "Appended" to my existing Notes field, and that it doesn't "Overwrite" the previous notes?

None.  You have the user info in a session variable and you can get the timestamp on the action page when the form is submitted.   If your app doesn't time out, a user could browse to the form, ignore it for a few days, and then submit it.

[jlig]

Dan, Thanks again..

I may be misunderstanding what you are saying, but I'm not sure this is clear..

- The user will type in a "new note" into the first textbox..

- Then click the "Add New Text" button

- And the "Notes History" textbox will now show "timestamp" + "new note" + "previous notes history"

My idea was to use a javascript function behind the Add New Text Button that does this without refreshing the page..?

Thanks again,

jlig

[Dan_Bracuk]

Other than displaying a string, what else would the javascript function do without refreshing the page?

[jlig]

Here is an example that I found at: http://www.mediacollege.com/internet/javascript/form/add-text.html:

--------------------------------------------------------------------------

<script language="javascript" type="text/javascript">
function addtext() {
var newtext = document.form1.inputtext.value;
document.form1.outputtext.value += newtext;
}
</script>

--------------------------------------------------------------------------

Currently the code behind my button is: <input type="button" value="Add New Text" onClick="addtext();">

---------------------------------------------------------------------------

This example works fine on a plain html page, but gives me javascript error:
Message: 'document.form1.inputtext' is null or not an object" when I add it to my real form?

Question: Do you know how to use ColdFusion variables ( ex.. #stamp#  ) in this Javascript code above?

If I could just insert my "stamp" string field from my CF query into the javascript, it might do what I need?

Example: document.form1.outputtext.value += newtext;

[jlig]

I've been able to use the following query to create the User-Date-Time Stamp, but still unable to get the Button to work that Pre-Appends the Stamp and the New Notes to the Existing Notes?

----------------------------------------------------

<cfquery name="rsStamp" datasource="salesPipeline">
SELECT tblWeeklyRpt.wkRptID, tblWeeklyRpt.wkRptCustName, user_usr.name_usr, tblWeeklyRpt.wkRptOrdStaNts,
CONCAT("[ ", date_format(NOW(), '%a, %b %D, %Y %l:%i %p')
, " by: ",(user_usr.name_usr), "] ") AS stamp
FROM (tblWeeklyRpt INNER JOIN user_usr ON user_usr.id_usr=tblWeeklyRpt.wkRptUser)
WHERE tblWeeklyRpt.wkRptID = <cfqueryparam value="#URL.id_com#" cfsqltype="cf_sql_numeric"> 
</cfquery>

----------------------------------------------------

Here is an example of my User-Date-Time Stamp from the query above: [Fri, Mar 12th, 2010 2:43 PM by: mgoodman]

Thanks,

jlig

[jlig]

After a few other coding projects, I'm back to try and figure this out..

Help on how to Append Text to an existing Notes field would be much appreciated.

Thanks,

jlig

[Owainnorth]

Sorry if I've misunderstood as it's not entirely clear, but it seems you're looking at generating timestamps in Javascript which end up in a database?!

If that's right, I'd consider carefully the ramifications of someone having the incorrect time set on their PC. This all sounds very wrong to me...

[jlig]

Owain, Thanks for the reply..

Actually I would rather not use javascript at all and just do the following:

- The user will type in a "new note" into the first textbox.. "(see image below)

- Then click the "Add New Text" button

- And the "Notes History" textbox will now show "timestamp" + "new note" + "previous notes history"

- I already have a query that can generate the Timestamp from my existing database fields

[Dan_Bracuk]

That being the case, what is it that you are trying to do now and what part are you having difficulty with?

[jlig]

Dan, Thanks for the reply.. It is much appreciated.

First, I still do not know how to Append the New Text field data to the Notes History text field?

Second, while I do have a query to generate the Time/Date/Stamp value, I'm having trouble grabbing the Session.kt_login_user variable and add it to the query?

Question 1: What is the code to put behind the Add New Text button?

Question 2: How do I add the Session variable to the Appeded Text (thru my query)

Here is some of the code behind the Notes fields & New Text Button: (I plan to remove the javascript/onclick as it is not working)

------------------------------------------------------

                  <div class="TabbedPanelsContent">
                    <table width="685" border="1">
                      <tr>
                        <td>New Note:</td>
                        <td><label>
                          <textarea name="inputtext" cols="120" rows="5"><cfoutput>#rsStamp.stamp#</cfoutput></textarea>
                          <input type="button" value="Add New Text" onclick="addtext();" />
                        (Use the Notes History until further notice)</label></td>
                      </tr>
                      <tr>
                        <td class="style4 style5">
                          <label for="label">Notes History:</label>
                        </td>
                        <td class="style5">
                          <textarea name="wkRptOrdStaNts_<cfoutput>#cnt1#</cfoutput>" id="wkRptOrdStaNts_<cfoutput>#cnt1#</cfoutput>" cols="120" rows="15"><cfoutput>#Request.KT_escapeAttribute(rstblWeeklyRpt.wkRptOrdStaNts)#</cfoutput></textarea>
                        <span class="style7 style7"><cfoutput>#tNGs.displayFieldHint("wkRptOrdStaNts")#</cfoutput></span> <span class="style7"><cfoutput>#tNGs.displayFieldError("tblWeeklyRpt", "wkRptOrdStaNts", cnt1)#</cfoutput></span> </td>
                      </tr>
                      <tr>

-------------------------------------------------------------

[wyntermoonwolf]

If you have a log table, why not create a DateStamp column to go with the record?  If you have a log file, simply prepend the date stamp to the log text.

Example:

When writing to a database:

<CFQUERY ...>

INSERT INTO MyTable (DateStamp,wkRptOrdStaNts)

values

(#CreateODBCDateTime(Now())#,'#FORM.wkRptOrdStaNts#')

</CFQUERY>

When outputting the log from a query:

[#DateFormat(MyQuery.DateStamp,"mm/dd/yyyy")# #TimeFormat(MyQuery.DateStamp,"h:mm tt")#] #MyQuery.wkRptOrdStaNts#

If writing to a file:

[#DateFormat(Now(),"mm/dd/yyyy")# #TimeFormat(Now(),"h:mm tt")#] #MyQuery.wkRptOrdStaNts#

Note that you do not need to wrap every variable with a <cfoutput> block.  You can wrap multiples with it.  Much easier reading.

Before:
<textarea name="wkRptOrdStaNts_<cfoutput>#cnt1#</cfoutput>" id="wkRptOrdStaNts_<cfoutput>#cnt1#</cfoutput>" cols="120" rows="15"><cfoutput>#Request.KT_escapeAttribute(rstblWeeklyRpt.wkRptOrdStaNts)#</cfoutput></textarea>

After:
<cfoutput><textarea name="wkRptOrdStaNts_#cnt1#" id="wkRptOrdStaNts_#cnt1#" cols="120" rows="15">#Request.KT_escapeAttribute(rstblWeeklyRpt.wkRptOrdStaNts)#</textarea></CFOUTPUT>

[jlig]

John, I really appreciate the detailed post..

I actually do not have a Log Table or Log File,  just a Main Table called tblWeeklyRpt with all the other fields, including the wkRptOrdStaNts field (LongText type)

Here is the flow of the data:

---------------------------------------

• The Sales Agent will call their Customer, and then record the call details in the wkRptOrdStaNts field.
• If there are previous notes already in that field, they click in front of the text, hit return twice, and type in the new text and Save the record
• This works but is not very user-friendly and sometimes allows part of the old notes to get deleted accidentally

• What I'm trying to do is display two boxes on the form: One for the new Notes, the other for the existing notes
• When they type their new notes in the first box, and click Submit, the TimeStamp plus New Notes is Appended to the wkRptOrdStaNts field.

What do i place behind the Submit button to accomplish this?

Thanks again for the help..

jlig

[jlig]

Still trying to get the Append to work..

John, When you say that you do not need to wrap every variable with a <cfoutput> block.. You can wrap multiples with it.. Much easier reading..

If I do as you mentioned in the "After" line below, what name do i use when the actual variable name is called.. just the actual name of the database field?

- In this case that field is called rstblWeeklyRpt.wkRptOrdStaNts

Before:
<textarea name="wkRptOrdStaNts_<cfoutput>#cnt1#</cfoutput>" id="wkRptOrdStaNts_<cfoutput>#cnt1#</cfoutput>" cols="120" rows="15"><cfoutput>#Request.KT_escapeAttribute(rstblWeeklyRpt.wkRptO rdStaNts)#</cfoutput></textarea>

After:
<cfoutput><textarea name="wkRptOrdStaNts_#cnt1#" id="wkRptOrdStaNts_#cnt1#" cols="120" rows="15">#Request.KT_escapeAttribute(rstblWeeklyRpt.wkRptOrdStaNts)# </textarea></CFOUTPUT>

Also, If I use my current rsStamp (see below) to generate the Timestamp, how do I alter it to actually grab the "Logged-End users" Session variable?

If I use the code below, it uses the default user value I set, but will not grab the actual SESSION.kt_login_user..?

-----------------------------------------------

<cfquery name="rsStamp" datasource="salesPipeline">
SELECT tblWeeklyRpt.wkRptID, tblWeeklyRpt.wkRptCustName, user_usr.name_usr, tblWeeklyRpt.wkRptOrdStaNts,
CONCAT("[ ", date_format(NOW(), '%a, %b %D, %Y %l:%i %p')
, " by: ",(<cfqueryparam value="#SESSION.kt_login_user#" cfsqltype="cf_sql_clob">), "] ") AS stamp
FROM (tblWeeklyRpt INNER JOIN user_usr ON user_usr.id_usr=tblWeeklyRpt.wkRptUser)
WHERE tblWeeklyRpt.wkRptID = <cfqueryparam value="#URL.id_com#" cfsqltype="cf_sql_numeric"> AND tblWeeklyRpt.wkRptUser=<cfqueryparam value="#SESSION.kt_login_id#" cfsqltype="cf_sql_numeric">
</cfquery>

------------------------------------------------