How to sort Alphabetically In Combobox? 1

[mikeisvfp]

Hello Experts

How do I sort Alpabetically In my comobox.

rowsourcetype must be fields

I tried..

SELECT ALLTRIM(customers.firstname)+' '+ALLTRIM(customers.lastname) FROM customers ORDER by firstname INTO CURSOR MyComboCursor

 

[GriffMG]

Can you not use a SQL Select as the source for your rows?

I think you might be anyway... is the rowsourcetype 3

If it is, then a statement like

SELECT FULLNAME(FIRSTNAME,LASTNAME) FROM CUSTOMERS ORDER BY FIRSTNAME,LASTNAME INTO CURSOR MYCOMBOCURSOR

Should work, you would need a function like this :

function FullName
  parameters m.FirstName, M.LastName
  private m.FirstName, m.Lastname, m.String
  m.String = Alltrim(Alltrim(m.FirstName)+" "+alltrim(m.LastName))
  return(m.string)

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Mike Lewis]

You can't use the built-in sorting if your Rowsourcetype is 6 (Fields). The only way to do it would be to index the table based on the required order before you populate the combo.

Alternatively, do as Griff suggested: create a cursor in the desired order. But you would still use Rowsourcetype = 6 (Fields); just base the Rowsource on the cursor rather than the table.

Another option would be to loop through the table, adding each row in turn via AddItem(), and then set the combo's Sorted property to .T. But that would be more work than the other methods.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

As you select ORDER BY firstname the result is ordered. Do you also create an index afterwards, otherwise the combobox should simply display records in firstname order.

I don't see a problem here, so the problem must be somewhere else.

Bye, Olaf.

 

[JRB-Bldr]

rowsourcetype must be fields

NO
Based on what you show above, your RowSourceType is Alias
Where the RowSource is MyComboCursor

And since you are sorting your MyComboCursor by FirstName, it will be alphabetically sorted as you want.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

spot a small problem: You display firstname+lastname, but order by firstname only. Add lastname to the order by and the order will be as you want. Or ORDER BY 1 to order by the first field of the resultset, then changing the expression does not make a difference in the ORDER BY clause needed, if you later change to lastname+firstname for example, as ORDER BY 1 always orders by the 1 result field.

Bye, Olaf.

 

[mikeisvfp]

Ok, Maybe Im going wrong somewhere because its not working

this is what I have

controlsource = MyComboCursor

RowSource = +ALLTRIM(customers.firstname)+' '+ALLTRIM(customers.lastname), customerid

RowSourceType = Alias


in the valid event of the combobox

DODEFAULT()
SELECT ALLTRIM(customers.firstname)+' '+ALLTRIM(customers.lastname) FROM customers ORDER by 1 INTO CURSOR MyComboCursor

 

[Olaf Doschke]

The controlsource never is an alias, the controlsource always has to be a single value/field.

The rowsource and rowsourcetype is fully ok as is, but then you don't need your SQL and you are not at all making use of the resultset. Your Rowsource should be MyComboCursor.

You should give a field name in the SQL:

SELECT ALLTRIM(customers.firstname)+' '+ALLTRIM(customers.lastname) as fullname, customerid FROM customers ORDER by 1 INTO CURSOR MyComboCursor

Then set Rowsource to MyComboCursor.fullname,customerid and RowsourceType to fields.

A ControlSource could be Orders.cusomterid or appointments.customerid or something else being a customer ID, to pick one from the Rowsource. BoundColumn then needs to be 2 to relate to the customerid as the second column of the Rowsource.

Or you don't have any ControlSource at all.

I always remember the meaning of the controlsource as the source of some single value, controlling what is displayed. So in the first place it needs to be a single value, that is a field, not an alias alone.

What is displayed depends on the control. In a textbox it is the controlsource field or variable itself, but in a list or combobox the controlsource controls, which row is picked for display and the rowsource (and of course the rowsourcetype) determines what that row is (array row or table/cursor row for example) and what parts of that row are displayed.

Bye, Olaf.

 

[Mike Lewis]

You've got your Controlsource mixed up with your Rowsource. Also, you're referencing two separate data sources - the original table and the cursor.

Also, I can't see why you have that code in the Valid event.

Going back to square one: If your data is in the Customers table, and your aim is to display customers in alphabetical order, do one of the following:

1. EITHER: For the customer's tablec create an index on lastname + firstname. Set the RowSourceType to 6. Set the RowSource to customers.firstname, lastname - exactly as I hav written it here. Set ColumnCount to 2.

2. OR: Set the RowSourceType to 3. Set the Rowsource to the SQL SELECT statement (the one you have at present in your Valid).

Either way, remove the code from the Valid. And probably also remove the ControlSource.

You should now see the names in alphabetical order (either first name then last name, or vice versa).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Oh, and the SQL select is needed in the init of the combobox or form, not in the valid event. The valid event happens, if the user makes a choice, so it's totally wrong and too late to generate the choices there, isn't it? The valid event of another control maybe ok to generate that list, too, but then I would expect the SQL to have a dependency of the value picked in that control. Your SQL does not filter the customers at all, so generating it once is enough, isn't it?

Bye, Olaf.

 

[mikeisvfp]

Thank You all so much, It took me a while to respond as I had so many suggestions to go from. Anyway I finally got it to work with Olaf's suggestion

SELECT ALLTRIM(customers.firstname)+' '+ALLTRIM(customers.lastname) as fullname, customerid FROM customers ORDER by 1 INTO CURSOR MyComboCursor

Thanks Once Again