Record Order 2

[CDavis]

I have a list box displaying "myField". I'd like to order the list in reverse order of the entry into the table (last in/top of list). I know that I could create a numeric field that increments for each new entry and then index on that in descending order. Is it possible achieve the goal without the additional field or setting an index. Can the table itself be in descending order based for example on record number?

Thanks in advance for your help.

Chuck Davis

 

[Mike Lewis]

Chuck,

Probably the easiest way to achieve this is to set the listbox's RowSourceType to 0, then loop through your table backwards, calling the listbox's AddItem() method for each record.

So, instead of a SCAN loop, you would GO BOTTOM, then SKIP -1 each time round the loop until you hit BOF().

If I've understand your requirement correctly, this should give you what you want.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[CDavis]

Thanks Mike,

Your solution works well. I've included the code snippet that I placed in the init of the listbox in case someone else has need of it.

SELECT myTable
GO Bottom
DO WHILE NOT BOF()
This.AddItem(myTable.myField)
SKIP -1
ENDDO

Chuck Davis

 

[cbasoz]

IMHO best way to handle that is to set the rowsourcetype to 3 and rowsource to SQL statement. ie:

* listbox init

with this
 .RowSourceType = 3
 .RowSource = 'select myField, myIdField'+;
              ' from myTable' +;
              ' order by myField desc' +;
              ' into cursor crsMyList' 
endwith

Cetin Basoz
MS Foxpro MVP, MCP

 

[CDavis]

Cetin,

I appreciate you taking a look at this. I haven't tested your suggestion but on the surface it looks like the resulting cursor would be ordered descending by the contents of myField. In my case the field is character and I would end up with an alphabetized list (in descending order).

What I prefer is a list that has the most recent record at the top and the earliest entry at the bottom. 90% of the time, the end-user will be working on the most recent record.

I'm rusty on SQL so let me know if I've misread your code.

Thanks --

Chuck Davis

 

[cbasoz]

No you read it right, I misinterpreted your requirement. Then:

'select myField, myIdField'+;
' from '+;
' (select sys(2015) as mySorter, * from myTable) tmp' +;
' order by mySorter desc' +;
' into cursor crsMyList'

Cetin Basoz
MS Foxpro MVP, MCP

 

[alan92rttt]

If you have an existing index on that column you could just use the set order command to tell VFP to use that index in descending order.

set order to myTag descending

Copyed from the VFP help

SET ORDER TO [nIndexNumber | IDXIndexFileName | [TAG] TagName
[OF CDXFileName] [IN nWorkArea | cTableAlias]
[ASCENDING | DESCENDING]]

ASCENDING | DESCENDING
Displays and allows access to table records in ascending or descending order. Including ASCENDING or DESCENDING doesn't change the index file or tag in any way.




Alan

 

[CDavis]

Alan,

My goal was not to add another field or index tag which I think would be required to display the table contents in reverse order of how the records were saved. Setting an index on a character field in descending order would not achieve the goal. I think that I would need to add a numeric field and populate it with a number corresponding to the record number.

I might end up doing that as there is some value in tying the record source of the list to the table. When the end user selects an item from the list -- the record pointer moves etc. But at this point the client prefers not to modify the table structure.

I'm currently testing the solution that Mike suggested and although it required some additional coding to move the record pointer, it seems like it will work okay.

 

[cbasoz]

Oh have you tried the SQL I sent?

.RowSource = 3
.RowSourceType = ; 
  'select myField, myIdField'+;
  ' from '+;
  ' (select sys(2015) as mySorter, * from myTable) tmp' +;
  ' order by mySorter desc' +;
  ' into cursor crsMyList'

That would do what you want and return the rows from latest entry to first. No index needed.

Cetin Basoz
MS Foxpro MVP, MCP

 

[CDavis]

Cetin,

Thanks for the additional suggestion. I'm sorry its taken me a while to get back to you.

I put the following code snippet in the init of the listbox. It throws an error: "Data type is invalid for this property"

USE Eval IN 0
SELECT Eval

This.RowSource = 3
This.RowSourceType = ; 
  'select Projname, Id'+;
  ' from '+;
  ' (select sys(2015) as mySorter, * from Eval) tmp' +;
  ' order by mySorter desc' +;
  ' into cursor crsMyList'

The debugger indicates the error is associated with: This.RowSourceType = ;

I haven't worked the problem much because Mike's suggestion is working for me but maybe Cetin's approach would be more elegant.

Let me know if you have additional ideas.

Thanks again.

Chuck Davis

 

[cbasoz]

My Bad, RowSource and RowSourceType would be switched:

This.RowSourceType = 3
This.RowSource     = ; 
  'select Projname, Id'+;
  ' from '+;
  ' (select sys(2015) as mySorter, * from Eval) tmp' +;
  ' order by mySorter desc' +;
  ' into cursor crsMyList'

Cetin Basoz
MS Foxpro MVP, MCP

 

[CDavis]

Thanks,

That solved the error and the listbox does display the information from myField in reverse order to the records in the table. I'm not sure which solution I'll end up using. The coding is approximately the same length and in either case the listbox contents are not tied to the table itself so I'm required to read the value of the listbox choice and locate the value in myTable and move the record pointer to the appropriate record.

As we all know, it would probably be a better design to add a field that could be indexed directly in decending order and set the rowsource to the table.

In any event, Thanks to all of you who looked at this for me.

-- Chuck

 

[cbasoz]

No, IMHO it is not a better design to add an unnecessary field and index on it just to provide some sort of display in a listbox.

Using RowsourceType = 3 (SQL) have advantages like:

1) You can select, project, order, group etc in anyway you want w/o affecting the source.
2) Despite showing just a few columns (or simply default 1) you could have as many columns as you want in the listbox source (which is a cursor).
3) Unlike approaches that are not based on alias (additem, addlistitem ...) you don't need to make any conversion to retrieve a 'value' back. You simply get the value with an alias.fieldname and do not need any conversion of data types (if you use listbox's Value then you are tied to a single column plus you have the value as a character).
4) You don't need to be on your toes for VFP's default setting of Boundto = .F.
5) Items 3 and 4 make it a little problematic to get/set something from/to listbox. With alias based sources there is much wider flexibility IMHO.
6) And of course speed.

If we check these with a little (and maybe not so useful) sample:

Public oForm
oForm = Createobject('SampleForm')
oForm.Show()

Define Class SampleForm As Form
  DataSession = 2
  Height=400
  Width=600

  Add Object myList As ListBox With Top = 10, Left=10, Width = 150, Height=380

  Add Object txt_EmpId As TextBox With Left=180,Top=10,Width=100
  Add Object txt_First As TextBox With Left=180,Top=40,Width=100
  Add Object txt_Last As TextBox With Left=180,Top=70,Width=100
  Add Object txt_Bdate As TextBox With Left=180,Top=100,Width=100
  Add Object txt_Hired As TextBox With Left=180,Top=130,Width=100
  Add Object txt_Age As TextBox With Left=180,Top=160,Width=100

  Add Object txtEmpId As TextBox With Left=290,Top=10,ControlSource='Employee.Emp_id'
  Add Object txtFirst As TextBox With Left=290,Top=40,ControlSource='Employee.First_Name'
  Add Object txtLast As TextBox With Left=290,Top=70,ControlSource='Employee.Last_name'
  Add Object txtBdate As TextBox With Left=290,Top=100,ControlSource='Employee.Birth_date'
  Add Object txtHired As TextBox With Left=290,Top=130,ControlSource='Employee.Hire_date'
  Add Object imgPhoto As OleBoundControl With Left=290,Top=160,;
    ControlSource='Employee.Photo', Height=200, Width = 200

  Procedure Load
    Use (_samples+'data\employee')
  Endproc

  Procedure Init
    With This.myList
      .RowSourceType = 3
      .RowSource = "select Last_Name-(', '+First_name),*" +;
        " from employee" +;
        " order by 1"+;
        " into cursor crsEmployees"
    Endwith
  Endproc

  Procedure myList.InteractiveChange
    With Thisform
      =Seek(crsEmployees.emp_id, 'Employee', 'emp_id')

      .txt_EmpId.Value = crsEmployees.emp_id
      .txt_First.Value = crsEmployees.first_Name
      .txt_Last.Value  = crsEmployees.last_Name
      .txt_Bdate.Value = crsEmployees.Birth_Date
      .txt_Hired.Value = crsEmployees.Hire_Date
      .txt_Age.Value   = Year(Date()) - Year(crsEmployees.Birth_Date)
      .Refresh()
    Endwith
  Endproc
Enddefine

If you run the code you get a simple employee form with a listbox navigator. If you examine the code:

-We haven't specified anything other than left,top,height,width, RowSourceType and RowSource for the listbox.

-Although we have single column on the listbox (we might have chosen more) we have access to any field in its source cursor.
-We chose to project lastName-(', '+FirstName) to have a fullName to show and also ordered on that column (why would we ever create a new field for this and index on it)
-Values on left textboxes come from Listbox' cursor.
-Values are not subject to any conversion. They have the data types whatever on the underlying source (a date is a date, an integer is an integer and so on - unlike all being a character with AddItem())
-Since the primary key is part of the cursor, positioning on the correct record in original source table (employee) is a simple seek().

I hope the difference is more clear now.

Cetin Basoz
MS Foxpro MVP, MCP

 

[CDavis]

Thanks Cetin,

I appreciate you taking the time to explain the benefits of your approach and I think in most cases your approach makes good sense. The amount of time you've taken to help me with this is worth a Star.

In this particular case, the table doesn't have a field set as the primary key, in fact it doesn't have any indexes at all. So in order to make changes to the source data, I still need to locate the appropriate record in the table and move the record pointer.

Wouldn't all of the benefits that you note in using a cursor remain if the rowsource of the listbox were the table itself? A listbox selection in that case avoids both "seek" or "locate".

Although I've not tested it, I expect building and setting row source to a cursor is faster than skipping through the table from bottom to top and adding items to the listbox one by one. So from a theoretical point of view, your approach in my case probably makes sense, although the number of records in the table is very low (less than 200) so there is no noticeable difference in the presentation of the data. Having said that, I would think that setting rowsource to the actual table would be faster than either the cursor or additem() approach.

I'm still working with the client and hope to convince him that some minor modificatiosn to the overall structure could help in efficiencies.

--Chuck

 

[cbasoz]

Star is not something worth to think about I have a milky way anyway.

Yes you could use the table itself but that kills the flexibility. If you don't have any key you could still use the recno() as a key. ie:

select *, recno() as rcno from myTable order by rcno desc into cursor myCursor

and even you could set relation to table:

select myTable
set order to 0
slect myCursor
set relation to rcno into myTable

Cetin Basoz
MS Foxpro MVP, MCP

 

[CDavis]

Ah, Now that is what I was looking for in my original post: "Can the table itself be in descending order based for example on record number?"

I can definitely see the advantage here.

Thanks Cetin and even if you have a galaxy, there's probably room for another star.

With that, I'll let you get back to more productive endeavors.

-- Chuck

 

[Bobriver7]

You can create a LOCAL VIEW of the table and set the ODER to RECNO() Decending, then use the view as the source for your list, easy and permanent without the (SET ORDER) confusion.

Bob