How to sort field including the newly added record? 2

[Mandy_crw]

Hi everyone.... ive noticed that everytime i sort my table, only those old record are sorted... then another sort for the newly added records.... how can i sort everthing including those recently added? Thanks and God bless....

 

[Tore Bleken]

Why do you ever need to sort the table in the first place? In my close to 40 years with xBase, I have never needed to sort any table.

 

[Mandy_crw]

Hi tore… i need to put it in the cursor, and put in the combobox alphabetically arranged so that finding is a lot easier… unfortunately sort or order does not do it…

 

[mjcmkrsr]

Hi Mandy,

You don't need to sort your table. Btw SORT should never be used in an application

This is an oldie-but-baddie command. Back in the days before indexes, it was an awfully important command, but it's been superseded so many times, we've lost count.

SORT makes a copy of a table, physically ordering the records in the new table according to specified criteria. SORT is a resource hog—it can require as much as three times the disk space occupied by the table to do the copying. Because you can do the same thing with COPY TO and an index, or SELECT-SQL, there's not much reason to use SORT.

Furthermore we already gave you hints how to handle this issue. Please see e.o. my post - you'll notice that the cursor is in natural order whereas the combobox selection is ordered alphabetically.

thread184-1824310

hth

MarK

 

[Tore Bleken]

Just Select fieldlist from yourtable into cursor yourcursor order by whatever Nofilter.
You may have to add With Buffering = .T.

 

[Mike Lewis]

Mandy, the others are correct when they say you never need to sort a table. In general, there are two things you can do to access the records in a given order:

1. Index the table

2. Use SELECT SQL with an ORDER BY clause to create a cursor in a given order.

In addition, in the case of a combo box, you can set its Sorted property to.T. (but only if its RowSourceType property is 0 or 1.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Mandy,

the direct answer to your question is using an index to sort, that also sorts new records. Physically, new records always go to the end of the list, but set order to some index, and that lists data in index order, always.

Whether the combobox list reacts to that depends on what rowsourcetype you use. (see Mikes remark on the Sorted property only working for some types)

I don't know if we talked about the same combobox in several threads and you changed from rowsourcetype alias to fields to array to none and instead using additem(), you can't combine all advantages of each type of data binding, that's why the different types exist and you pick the best one for the use case.

If you want new records in a dbf you queried into a cursor to show up, you have to redo the query every time you change the dbf. If you bind to the dbf directly, you have to live with bad performance using a filter to only show items in the combobox, which matter. If you therefore use a cursor and add records to it, that won't add the same record to the dbf, etc. etc. So you always have some advantages and some disadvantages anyway and have to navigate to a solution that fits all your needs.

Anytime you only address one need, we can solve that and thereby may not know what else you need at the same time. That can lead to a flip-flop situation, where you swap problems. Every time you solve one, you get back to the other.

Chriss

 

[Chris Miller]

Just to straighten your idea of how things work, perhaps:

An index sorts data and accelerates queries and even both at the same time, in case of an ORDER BY query.

So if you SELECT FIELDLIST FROM sometable ORDER BY somefield INTO CURSOR XYZ an index sorting somefield is used for the query. The resulting cursor has data in order, but the cursor has no index. So to permanently sort the cursor xyz, including records you add to it, later, you need to define the same index on xyz as is defined on the table.

A query always only gives you a result from the current data in a dbf, it does copy the data which is matching where and join conditions, etc. and creates a new table from that, which in case of INTO CURSOR is not a permanent dbf file, but even if it was, that's not the problem. That cursor (or new table) does not inherit anything else from the dbf table, no index, no filter, only the data that the query got. The indexes of the underlying dbf table have helped to optimize the query performance during the query, they are not copied themselves.

Well, knowing all that, you can see defining an index on xyz can solve sorting new records in the cursor, when the combobox is bound to that. But those new records added to the cursor are not added to the table you originally queried the initial list from, so that's only a solution when that doesn't matter.

In case it would mattter to have new records in the underlying table and not just in the cursor of the combobox, it would be simpler to redo the query. That was a solution at least two times for you and I wonder why you still don't get that idea yourself. It's meaning the data has to be copied again and again and again. Does that feel bad to you? Then you could also go the route of adding the same record to the table and the cursor, that way they also stay in sync, but you won't get records added by other users in the meantime. See? nYou always have advantages and disadvantages.

Simply plainly think about what you have, if you have a cursor and what you don't have in it and what you need to do to get what you want. You have to know what FoxPro actually does when you do code. Only a clear picture of what really is where can lead to correct programming. Many questions here arise simply originating from a wrong idea of how things work, not only your questions. You can get a better idea of what you really have at hand if you dig into the documentation and learn what code you got actually does. I can and do give code and explanations, but if you never ask about what still isn't clear, you don't get forward.

Chriss

 

[Mandy_crw]

I think I found a solution in the sorting problem i have encountered... ive noticed that when its all capital letters it sort all data that are all in uppercase, and sort another with all Capital in the first letter.... so i made all my surname PROPER and when i sort, its all sorted now... Thanks everyone... Godbless....

 

[mjcmkrsr]

Hi Mandy,

The PROPER() function is not the way to go - you may want to use UPPER() or LOWER().

From the Hacker's Guide

Lower(), Proper(), Upper()

It's a funny thing. These three functions do very similar things, yet UPPER() and LOWER() are among the most important functions in FoxPro, while PROPER() is one of the most useless. All three take a character string and return a string with the case of the letters modified — UPPER() converts them all to uppercase, LOWER() makes them all lowercase, and PROPER() supposedly makes proper names out of them (with a capital letter at the beginning of each word).

Usage
cResult = LOWER( cString )
cResult = PROPER( cString )
cResult = UPPER( cString )

In many comparisons and searches, using either UPPER() or LOWER() is essential, so the operation is case-insensitive. You don't want to worry about whether the user has entered "Smith", "smith", "SMITH", or "sMiTH". So you apply UPPER() or LOWER() to both sides of the comparison or to both the search string and the target of the search. For example, if a user has filled in variables cLastName and cFirstName, you could search for a matching record like this:

LOCATE FOR UPPER(LastName) = UPPER(cLastName) AND ;
UPPER(FirstName) = UPPER(cFirstName)

The same logic applies when creating indexes based on character strings. If case is irrelevant (which is true more often than not), apply UPPER() to the data being indexed and then always be sure to SEEK using UPPER(the search string). For example, to re-create the company name index for TasTrade's Customer table, you use:

INDEX ON UPPER(Company_Name) TAG Company_Na

Then, to search for QUICK-Stop, you'd:

SET ORDER TO TAG Company_na
SEEK "QUICK-STOP"

To search for the company whose name is contained in m.cCompany, use:

SEEK UPPER(m.cCompany)

Actually, if m.cCompany comes from user input, you should probably trim it first to get rid of any leading or trailing blanks that might have crept in:

SEEK UPPER(ALLTRIM(m.cCompany))

You can use LOWER() exactly the same way as UPPER() in searches and comparisons. Just pick one or the other and always use it, so you know what to expect. Harking back to our mainframe days, we tend to use UPPER(), but LOWER() is more readable, the ergonomists tell us; we just haven't lowered ourselves to using LOWER() yet.

PROPER() is one of those great ideas that just didn't work out. Its purpose is to let folks enter names without worrying about capitalization. Then, you can come along later and apply PROPER() to fix it up. Unfortunately, names just aren't that simple. While PROPER() is great for run-of-the-mill names (like Granor or Roche), it falls apart when you hand it stuff like O'Hara or MacNeill.

Fundamentally, PROPER() is too simple-minded to do the job. It takes whatever you hand it and returns it with the first character of each word capitalized. If that's not appropriate, too bad.

hth

MarK

 

[Chris Miller]

Mandy,

that's a problem you already had in thread184-1826083
We pointed out you can make use of collations. It seems you haven't understood a thing we said. I wonder, why you didn't ask, then.

Proper() is no good soluion as Mark said, because you now actually changed data for it to sort. That's unnecessary. You can ORDER BY LOWER(field) and also INDEX ON LOWER(field) Tag cifield, for example, to let ORDER BY be optimized by the index.

Collations solve that problem, too, as me and Mike stated in the old thread. Seems you haven't taken that in at all.

What you describe as unsorted is indeed sorted, just not as you expect it. So nobody here had the idea that this is your real problem. We got the wrong impression that you actually lacked sorting at all, while you do have sorted data, just not sorted case insensitive.

Case sensitive sorting is normal in VFP as the default collation is MACHINE, which means anything is sorted by the ASCII or ANSI code of characters. And in ASCII or any ANSI codepage 'A' has a lower number than 'a', even 'Z' has a lower number than 'a', because all upper case letters come first. Take a look at the usual codepage 1252 for the USA and most of Europe (Western languages), for example in Wikipedia:

https://en.wikipedia.org/wiki/Windows-1252

It doesn't differ in other codepages, too, or in ASCII. All uppercase letters come before all lowercase letters and that's how they are sorted.

If you had read up on collations you would know that with collations you tell a database to become aware of the position of letter variations like accents within the alphabet and then also collations make sorting case insensitive, i.e. make the letter position more important than the code number of the character and whether it is upper or lower case. Using them also makes comparisons case insensitive, so 'A'='a' becomes true with any collation other than MACHINE. That could be welcome, that could point out you better make use of the LOWER() and UPPER() functions when case insensitive sorting or comparison are wanted and use the field value when case sensitivity is wanted instead of using a collation sequence.

On the other hand, data stored with a collation like GREEK or ARABIC would be better with such languages using a completely different alphabet (even though that name stems freom Greek). And it also would let ORDER BY sort data in these languages alphabets without needing LOWER() or UPPER(). I don't think the LOWER/UPPER trick could solve the arabic sorting, for example, so usng LOWER() or UPPER() is a trick that only works well in Latin alphabets and their slight variations including French, Czech or Spanish. If you plan for international users, it becomes easier to use a database server like MSSQL, which has more capabilities alswo in terms of Unicode support.

Chriss

 

[Mandy_crw]

Hi mjcmkrsr… thank you for explaining it to me… im already using UPPER in my searches… godbless…

Hi chriss… you are always giving me a very gud explanation, i think i tried set collate before but i dont know for some reasons it did not work… but then again you always provide a very comprehensive answer, thanks for being so generous and kind… godbless you…

 

[Chris Miller]

i tried set collate before but i dont know for some reasons it did not work

When you realize that it's a reason to ask. Indeed SET COLLALION alone does not change something. It has to be set before you create a table or an index, which then uses the currently set collation. And later, when using the table, the collation is automatic, "baked into" the table. Which makes it something that needs a table redefinition.

Here's the effect of collations on Cursors created while a specific collation is set (which works the same way for DBF tables):

Clear
Set Collate To "MACHINE" 

Create Cursor collationdemomachine (cText C(1))
Index on cText tag cText

Insert into collationdemomachine values ('ñ')
Insert into collationdemomachine values ('n')
Insert into collationdemomachine values ('N')
Locate
Browse name loMachine nowait
loMachine.left = 200
? 'In MACHINE collation...'
Scan
   ? 'Are ',cText, ' and n equal?', cText = 'n'
   ? 'Are ',cText, ' and ñ equal?', cText = 'ñ'
EndScan

Set Collate To "SPANISH"
Create Cursor collationdemospanish (cText C(1))
Index on cText tag cText

Insert into collationdemospanish values ('ñ')
Insert into collationdemospanish values ('n')
Insert into collationdemospanish values ('N')
Locate
Browse name loSpanish nowait
loSpanish.left = 400

? 'In SPANISH collation...'
Scan
   ? 'Are ',cText, ' and n equal?', cText = 'n'
   ? 'Are ',cText, ' and ñ equal?', cText = 'ñ'
EndScan

last not least: If you now query such a table with another collation than MACHINE with the usual ORDER BY field clause, it does not need LOWER(field) or UPPER(field) to sort case insensitive, just the normal ORDER BY and so also a normal INDEX ON field is sufficient to optimize the query. But also querying WHERE field = 'n' would yield both records starting with 'n' and starting with 'N', case insensitive.

Chriss

 

[mjcmkrsr]

Hi Mandy,

Indeed SET COLLALION alone does not change something. It has to be set before you create a table or an index, which then uses the currently set collation. And later, when using the table, the collation is automatic, "baked into" the table. Which makes it something that needs a table redefinition.

Hence you may want to check your tables' settings of COLLATE and choose the one you want/need and save the changes (see picture).

hth

MarK

 

[Chris Miller]

Indeed SET COLLALION alone does not change something. It has to be set before you create a table or an index, which then uses the currently set collation

Actually I have to correct myself here. Once you set a collation the next query you do and use an ORDER BY will sort the data by that collation. Not only when it was created in the same collation or has indexes with that collation or no index at all. When no index of that collation exists, that just won't be index optimized. On the other side, SET COLLATE does not sort an existing result, just like changing data of a dbf does not change a query result previously done. As always, such things influence what you do afterwards, not what you did before.

There are very few things that influence an already existing result, SET ORDER is one of them, as setting an order by an index effects how the data of the current workarea is sorted, even if you already have a browse winodow open or have this alias set as the recordsource of a grid, just a refresh resorts the data. The only condition is the index is defined in that workarea. Of course, setting an index to sort the source DBF of a query does not alter the result, so it is important and also normal to index query result cursors to make them sortable.

And that's also a reason and an application of indexes: To use indexes to sort data. You can keep a result as is and show it in defferent ordering by just setting or creating an index. SET COLLATE then has no influence, as the order is determined by the index and the baked in collation as MarKs screenshot shows, but when creatng the index the current collation determines how exactly the sorting is done, also in and with that index.

So it could even play a role to have several indexes on the same field with different collation sequences to so they are usable for sorting by different collation sequences/rules.

Chriss

 

[Chris Miller]

By the way, don't take it too serious when I stress the point about anything only affecting the future and not the past. That's a simple truth anbout reality, nothing special to computers or programming languages.

But people often make the impression they don't think about implications and this is the root of wrong expactations. You can even have wrong expactations based on experience that makes you think you have the concept right. I said SET ORDER has an effect on an already given result without the need to requery it, and that's true, but it also only affects the time from the SET ORDER onwards into the future. And a SET COLLATE only affects future INDEX ON creating indexes or future ORDER BY clauses, it has no meaning as SET ORDER to "now order by that collation sequence".

Let me give you another example that might even astonish people knowing VFP well. If you set a filter condition, that also takes effect from then on, obviously it can#t change the past, but it also doesn't act as immediate as you might expect.

If you open up any table, say the customers from the tastrade or northwind sample databases, you'll be at record 1, that's just a default when opening a table with USE. If you now SET FILTER TO RECNO()>1, that filter only allows access to records after record 1, but you are not immmediately pushed to record 2 because of the filter. The filter only starts acting when you first access the workarea.

People tend to use BROWSE as the normal concept of "what you see is what you get", ignoring that BROWSE itself also already acts on the workarea you browse and only thereby the filter is applied. So to show this do:

USE foxcode
? Recno() && 1, as expected
SET FILTER TO RECNO()>1
? Recno() && 1, likely not as you expect filter to work, because if you verify the effect of a filter by doing
BROWSE nowait
? Recno() && 2, now the filter has been applied the first time (and many more times for all the rows showing in the browse window)

The core trivial thing is, that anything can only affect the future.

I don't think anybody has that idea, when directly asked, that code has a retrofitting ability, but some expectations are equivalent to that.

I only stress this because everyone can verify his expectations in multiple ways, thinking about corner cases is always a good idea. And I know it's hard to get the idea of having wrong expectations and which expectations are wrong, but getting unexpected results or behavior is pointing out you need to start thinking in that direction. In the direction of having wrong concepts and therefore wrong expectations.

By the way, now that a browse of foxcode.dbf is open (or if you use VFP6 or earlier replace that by any other dbf. If you now turn off the filter again by SET FILTER TO, the browse window still will show record 2 at the top, you have to scroll up one record (and you can scroll up now) to see recno 1. Again, this just shows SET FILTER itself does not act on the record pointer, it explicitly does nothing itslf, it neither posisiotns on the first record fulfilling the filter condition, nor does it move away from the current record, even if it doesn't fulfill the filter. It's a LOCATE that locates on the first record fulfilling a filter, it's a BROWSE that only lists record fulfilling the filter, likewise a form.refresh() acts the same way on grid controls. But a filter in itself is neither a query nor is browse a query result of a filter, the supressed records still are part of the workarea, unlike the result of a query which really does not include records unless they fulfill a where and any other conditions by joins, etc.

I hope that's some food for thought. If something doesn't work as expected you can often enough find out why with just a little bit of thinking.

Chriss