EasyCFM.COM - The ColdFusion Community! About Us!

Tutorials Community Forums Careers

EasyCFM.COM ColdFusion Forums / Coding Help! / Ordering with last updated entry first

Join

Reply to Discussion | New Discussion

<< previous || next >>

Posted By

Discussion Topic: Ordering with last updated entry first

riogrande

01-25-2007 @ 11:04 AM

Edit

Profile

Send P.M.

Senior Member
Posts: 509
Joined: Mar 2003

I´ve been trying to work out how to do this.
I have a form with one button (update/add) which gets sent to two cfqueries one of which updates an entry and the other enters a new one depending on what the entry is which works fine.

What I am trying to do is order my output (in a cfgrid) so that the last entry or last updated entry is first in the list and selected.

I can do by defining the number of the entry and select it by hardcoding it and using onload on the cfform tag:
<cfsavecontent variable="selectedIndex">     var myGrid = _root.UsersGrid;     UsersGrid.selectedIndex = 0;</cfsavecontent>

<cfquery name="qNames" datasource="heel">
    select * from COMPANY2
     ORDER BY ENTID
</cfquery>

<cfform format="flash" skin="haloblue" onload="#selectedIndex#">

But how do I order the results by the last entry? form/session other? Thanks for any pointers

institutions.cfm

<cfsavecontent variable="selectedIndex">     var myGrid = _root.UsersGrid;     UsersGrid.selectedIndex = 0;</cfsavecontent>

<cfquery name="qNames" datasource="heel">
    select * from COMPANY2
     ORDER BY ENTID
</cfquery>

<cfif isDefined("form.updateaddbtn")>
<cfif FORM.ENTID GTE 1>
<cfset session.eng="#FORM.ENTID#">
<CFQUERY name="updatecompany" datasource="heel">
update COMPANY2
SET COMPANY = '#Trim(FORM.COMPANY)#',
    SECTOR = '#Trim(FORM.SECTOR)#',
    RANKING = #Trim(FORM.RANKING)#,
    EMPLOYEES = '#Trim(FORM.EMPLOYEES)#',
    DOCTORS = '#Trim(FORM.DOCTORS)#',
    FIRSTNAME = '#Trim(FORM.FIRSTNAME)#',
    POS = '#Trim(FORM.POS)#',
    PHONE = '#Trim(FORM.PHONE)#',
    ADDRESS = '#Trim(FORM.ADDRESS)#',
    city = '#Trim(FORM.city)#',
    dept = '#Trim(FORM.dept)#',
    EMAIL = '#Trim(FORM.EMAIL)#',
    MOBILE= '#Trim(FORM.MOBILE)#',
    FAX= '#Trim(FORM.FAX)#',
    COMMENTS='#Trim(FORM.COMMENTS)#',
    DTLEAVE='#Trim(FORM.DTLEAVE)#',
    MEETINGTIME='#Trim(FORM.MEETINGTIME)#',
    state='#Trim(FORM.state)#',
    UPDATED='#dateFormat(now(), 'mmm dd, yyyy')#'
    WHERE ENTID = #FORM.ENTID#
</CFQUERY>

<CFLOCATION URL="institutions.cfm">
<cfelse>
<cfquery name="getID" datasource="heel">
select max(ENTID) as maxID
from COMPANY2
</cfquery>
<cfset maxID3="#getID.maxID+1#">
<CFQUERY name="addcompany" datasource="heel">
INSERT INTO COMPANY2 (ENTID, COMPANY, SECTOR, RANKING, EMPLOYEES, DOCTORS, FIRSTNAME, POS, PHONE, ADDRESS, city, dept, EMAIL, MOBILE, FAX, COMMENTS, DTLEAVE, MEETINGTIME, state, UPDATED)
VALUES (#maxID3#,'#Trim(FORM.COMPANY)#', '#Trim(FORM.SECTOR)#', <cfqueryparam value="#form.ranking#" cfsqltype="cf_sql_integer" null="#yesNoFormat(NOT len(trim(form.ranking)))#" />, '#Trim(FORM.EMPLOYEES)#', '#Trim(FORM.DOCTORS)#', '#Trim(FORM.FIRSTNAME)#', '#Trim(FORM.POS)#', '#Trim(FORM.PHONE)#', '#Trim(FORM.ADDRESS)#', '#Trim(FORM.city)#', '#Trim(FORM.dept)#', '#Trim(FORM.EMAIL)#', '#Trim(FORM.MOBILE)#', '#Trim(FORM.FAX)#', '#Trim(FORM.COMMENTS)#','#Trim(FORM.DTLEAVE)#', '#Trim(FORM.MEETINGTIME)#', '#Trim(FORM.state)#', '#dateFormat(now(), 'mmm dd, yyyy')#')
</CFQUERY>
<CFLOCATION URL="institutions.cfm">
</cfif>
</cfif>

<cfform format="flash" skin="haloblue" onload="#selectedIndex#">
   <cfgrid name="UsersGrid" format="flash"
         query="qNames" rowheaders="No"
onchange="for (var i:Number = 0; i<state.length; i++) {if (state.getItemAt().data == UsersGrid.selectedItem.state) state.selectedIndex = i} for (var i:Number = 0; i<meetingtime.length; i++) {if (meetingtime.getItemAt().data == UsersGrid.selectedItem.meetingtime) meetingtime.selectedIndex = i}
for (var i:Number = 0; i<dept.length; i++) {if (dept.getItemAt().data == UsersGrid.selectedItem.dept) dept.selectedIndex = i}
for (var i:Number = 0; i<city.length; i++) {if (city.getItemAt().data == UsersGrid.selectedItem.city) city.selectedIndex = i}">

<cfgridcolumn name="FIRSTNAME" header="Contact Name">
<cfgridcolumn name="COMPANY" header="Company">
<cfgridcolumn name="PHONE" header="Telephone">
<cfgridcolumn name="POS" header="Position" display="FALSE">
<cfgridcolumn name="SECTOR" header="SECTOR" display="TRUE">
<cfgridcolumn name="ADDRESS" header="Address" display="FALSE">
<cfgridcolumn name="DOCTORS" header="Doctors" display="FALSE">
<cfgridcolumn name="EMPLOYEES" header=">Employees" display="false">
<cfgridcolumn name="FAX" header="Fax" display="FALSE">
<cfgridcolumn name="ENTID" header="ID" display="FALSE">
<cfgridcolumn name="EMAIL" header="EMAIL" display="FALSE">
<cfgridcolumn name="DEPT" header="Dept." display="false">
<cfgridcolumn name="city" header="City" display="true">
<cfgridcolumn name="DTLEAVE" header="ACTION" display="false">
<cfgridcolumn name="state" header="Next Step" display="false">
<cfgridcolumn name="meetingtime" header="Meeting Time" display="false">
<cfgridcolumn name="UPDATED" header="updated" display="FALSE">
<cfgridcolumn name="STATE" header="Action" display="FALSE">
   </cfgrid>

<cfformgroup type="tabnavigator">
<cfformgroup type="page" label="Details: #dateFormat(now(), 'mmm dd, yyyy')# : #qNames.RecordCount# Companies">
    <cfformgroup type="horizontal">
<cfinput type="text" name="COMPANY" label="Company" required="yes"
        bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['COMPANY']}"
        onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'COMPANY', COMPANY.text);">

<cfinput type="TEXT" name="SECTOR" label="Sector"  bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['SECTOR']}"
        onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'SECTOR', SECTOR.text);">

<cfinput type="TEXT" name="RANKING" width="95" label="Ranking"  bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['RANKING']}"
        onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'RANKING', RANKING.text);">

<cfinput type="TEXT" name="EMPLOYEES" width="95" label="No.Employees"  bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['EMPLOYEES']}"
        onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'EMPLOYEES', EMPLOYEES.text);">

<cfinput type="TEXT" name="DOCTORS" width="95" label="No.Doctors"  bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['DOCTORS']}"
        onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'DOCTORS', DOCTORS.text);">

</cfformgroup>



<cfformgroup type="horizontal">
     <cfinput type="text" name="FIRSTNAME" label="Full Name"
      bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['FIRSTNAME']}"
      onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'FIRSTNAME', FIRSTNAME.text);">

<cfinput type="text" name="POS" label="Position"
        bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['POS']}"
        onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'POS', POS.text);">

<cfinput type="text" name="PHONE" label="Telephone"
        bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['PHONE']}"
        onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'PHONE', PHONE.text);">

</cfformgroup>



<cfformgroup type="horizontal">
<cfinput type="text" name="ADDRESS" label="Address"
        bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['ADDRESS']}"
        onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'ADDRESS', ADDRESS.text);">

<cfselect name="city" width="200" size="1" label="City" onchange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'city', city.selectedItem.data);">
<option value="None">None</OPTION>
<option value="Bogotá">Bogotá</OPTION>
<option value="Samacá">Samacá</OPTION>
<option value="Zipaquirá">Zipaquirá</OPTION>
<option value="Medellín">Medellín</OPTION>
<option value="Bucaramanga">Bucaramanga</OPTION>
</cfselect>

<cfselect name="dept" width="200" size="1" label="Dept." onchange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'dept', dept.selectedItem.data);">
<option value="None">None</OPTION>
Santander</OPTION>
<option value="Valle del Cauca">Valle del Cauca</OPTION>
</cfselect>

</cfformgroup>




<cfformgroup type="horizontal">
    <cfinput type="text" name="email" label="Email"
      bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['EMAIL']}"
onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'EMAIL', email.text);">

    <cfinput type="text" name="mobile" label="Mobile"
        bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['MOBILE']}"
        onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'MOBILE', email.text);">

    <cfinput type="text" name="fax" label="Fax"
        bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['FAX']}"
        onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'FAX', email.text);">
</cfformgroup>
</cfformgroup>
</cfformgroup>

<cfformgroup type="tabnavigator">
<cfformgroup type="page" label="Next Step">
        <cfformgroup type="hdividedbox" >
      <cfformgroup type="VBox"  height="130">
         <cfformitem type="text" height="20">
            Comments:
         </cfformitem>
          <cfformgroup type="vertical" height="100" width="350">
<cftextarea name="COMMENTS" height="60" Width="350"
        bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['COMMENTS']}"
        onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'COMMENTS', COMMENTS.text);" />
</cfformgroup>
      </cfformgroup>

      <cfformgroup type="VBox" height="130">
         <cfformitem type="text" height="20">
            Action date:
         </cfformitem>
<cfformgroup type="horizontal">
<cfinput type="DateField" name="DTLEAVE" width="200"
        bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['DTLEAVE']}"
        onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'DTLEAVE', DTLEAVE.text);">

   <cfselect name="meetingtime" width="90" label="Time"onchange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'meetingtime', meetingtime.selectedItem.data);">
                        <option value="None">None</OPTION>
                            <option value="05:30 AM">05:30 AM</option>
              <option value="11:30 PM">11:30 PM</option>
                          </cfselect>
</cfformgroup>
<cfformgroup type="horizontal">
<cfselect name="state" width="180" onchange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'state', state.selectedItem.data);">
<option value="None">Next Step</OPTION>
<option value="SEND STUDY">SEND STUDY</OPTION>
<option value="SEND PROPOSAL">SEND PROPOSAL</OPTION>
<option value="COMNFIRM">CONFIRM MEETING</OPTION>
<option value="CALL AND EMAIL">EMAIL</OPTION>
<option value="CALL">CALL</OPTION>
<option value="SEND PRESENTATION">SEND PRESENTATION</OPTION>
<option value="MEETING">MEETING</OPTION>
</cfselect>

<cfinput type="text" name="UPDATED" width="130" label="UPDATED"
bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['UPDATED']}"on
Change="UsersGrid.dataProvider.editField(USersGrid.selectedIndex,
'UPDATED', UPDATED.text);">

<cfinput type="hidden" name="ENTID" label="ENTID"
bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['ENTID']}"
        onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'ENTID', ENTID.text);">
</cfformgroup>
</cfformgroup>
      </cfformgroup>
   </cfformgroup>
    </cfformgroup>

    <cfformgroup type="horizontal">
  <cfformitem type="spacer"/>
<cfinput type="submit" name="updateaddbtn" value="UPDATE CHANGES or ADD NEW ENTITY" >
<cfformitem type="spacer"/>

</cfformgroup>
</cfform>

01-25-2007 @ 1:38 PM

Edit

Profile

Send P.M.

Administrator
Posts: 4262
Joined: Oct 2002

does the order of all the records matter? or are you just trying to get the last modified (whether inserted or updated) to display at the top?

if it's a matter of just getting the last modified at the top and the rest of them can display however...i don't think you can do that in one query. you'd probably need a QoQ and your database would need a 'lastUpdated' column (date/time).

-CJ-
@ #coldfusion/DALNet
http://charlie.griefer.com

Teachers open the door. You enter by yourself.
—Chinese Proverb

riogrande

01-27-2007 @ 4:06 PM

Edit

Profile

Send P.M.

Senior Member
Posts: 509
Joined: Mar 2003

Thanks for the reply CJ. I have added a new date/time type column to my db called UPDATEDAT which stores the time: #TimeFormat(now(), "HH:mm:ss")# and another text type column called UPDATED which stores the date: #dateFormat(now(), 'mmm dd, yyyy')#.

The query orders by ENTID which works fine so I now I am trying to do a query of a query to be able to output the last entered/changed entry first and then the (next) entries by ENTID as before. So the cfgrid is ordered by ENTID but starts with the last entered/changed entry.


<cfquery name="qNames" datasource="heel">
    select * from COMPANY2
     ORDER BY ENTID
</cfquery>


<cfquery dbtype="query" name="lastupdated">
select UPDATEDAT
from qNames
order by
</cfquery>