International character in alphabetical order 1

[SitesMasstec]

Dear colleagues:

I need the above listing of the contents of a table in alphabetical order.

Of course, the character "A´" is different from "A", but is there a trick to put it in order without considering accent?


Thank you,
SitesMasstec

 

[Mike Lewis]

Are these values contained in a table? If so, you can use SET COLLATE to establish the sequence. For most western European languages, SET COLLATE TO GENERAL will usually be appropriate. Or you might get good results with SET COLLATE TO SPANISH, for examole.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Mike is right, of course. It helps to search the help for international applications or "understanding sort order".

One thing to keep several things in mind. An index will be created with the current collation sequence and keeps it even if you change SET COLLATION. Which also means SET ORDER to an index tag in the usual MACHINE collation men's SET COLLATION TO "SPANISH" does not sort by Spanish sorting rules but by the MACHINE index collation.

Even with several indexes in several collating sequences, the data only sorts by the collation of the currently set index and SQL only takes into account indexes in the current collation for sorting.

To showcase this:

Create Cursor Test (letter c(2))
Set Collate To "MACHINE"
Index on letter tag c1
Set Collate To "GENERAL"
Index on letter tag c2
Set Collate To "SPANISH"
Index on letter tag c3
Insert into Test values ('A')
Insert into Test values ('Ch')
Insert into Test values ('Cz')
Insert into Test values ('Á')
Insert into Test values ('ñ')
Insert into Test values ('z')
Browse

You'll see data in Spanish sort order, not mainly because it is the currently SET COLLATION, but because the last index was created in that collation and is the active index.

Try to set other orders by other indexes by doing one of the following SET ORDER, even when set collate stays "SPANISH".
Set order to c1
Set order to c2
Set order to c3.

So you have to keep track of which indexes to use besides which collation you set. It works flawless to once set the collation to SPANISH for everything if you only need the Spanish sort order. If your application supports multiple languages, it becomes more complex than just switching collation by SET COLLATION.

Chriss

 

[SitesMasstec]

Hello, colleagues!

I was afraid the implementation of your suggestions could disrupt other programs which depends on the file (CODEPAGE=850)
So I tested and implemented the following solution:

SELECT 1
USE ESTOQUE          && original file    (CODEPAGE=850)
COPY TO TMPESTOQUE   && new, temporary file  (CODEPAGE=1252)
USE
SELECT 1
USE TMPESTOQUE
INDEX ON CHRTRAN(ENOME,'ÁÂÃáâãÉÊéêÍíÓÔÕóôõÚúÇç','AAAaaaEEeeIiOOOoooUuCc') TO INDTMPNOME
USE
SELECT 1
USE TMPESTOQUE INDEX INDTMPNOME
GOTO TOP
...

Thank you,
SitesMasstec

 

[Chris Miller]

Note collation sequenes don't just sort single letters, also letter combinations like Cz as in Czech are sorted in to Ch, you don't get that with your approach.

You were afraid collations don't work with codepae 850, but have you even tried them? VFP still supports many codepages AND collation and so don't assume collations only work with codepage 1252.

Chriss

 

[SitesMasstec]

Hello colleagues!

Criss: Ok, nice note about languages with double characters.
About Collate, I have many users that have the original file (ESTOQUE.DBF) defined as codepage 850. How can I say programmaticaly in VFP 9 program that the codepage is 850 and use collate?

Something like this?
USE ESTOQUE COLLATE 850



Thank you,
SitesMasstec

 

[Chris Miller]

COLLATE isn't codepage, it's a setting about ordering characters. It's a setting that it considered during the creation (and then the usage) of an INDEX tag, that's what you add to the data for sorting, and that does take into account the codepage the data has, it does not need to change.

SET COLLATE SPANISH is not about using a Spanish codepage, it's about storing index information in index tags to sort with Spanish sorting rules. codepages and sort order are two separate issues.

You could also see and learn this from my example alone, it's the three indexes tags, each one done with a different COLLATION, but all about the same data, which is in the same codepage for all the collations. It's even one column, in the same table. So even when you do this all in Windows it's not three codepages, one for each collation sequence, it's one codepage for all collations.

Chriss

 

[Chris Miller]

Okay, I tried with mixed sucess to correctly sort a DBF in codepage 850. SET COLLATE alone does not make it right. The collation always interprets all the byte values in CPCURRENT(), no matter what codepage data is in.

So you have to run in codepage 850 to get codepage 850. There's CODPAGE=n you can add to a config.fpw.

Windows fonts are all about the system codepage, depending on the Windows language. So DOS software should be either ported including conversion of data to Windows codepages or you run this in a DOS subsystem, DO emulator or similar. But that surely won't support VFP9 executables.



Chriss

 

[SitesMasstec]

Chriss:

As I had told before, I have a Windows application which run old cadepage (850) files (used before in MS-DOS version of the application) in many locations (users). Until yesterday I did NOT know about defined codepage for files.

If I use the config.fpw in many user locations, I will be afraid, if user delete this file for example.

The application is working fine with my workaround, using

INDEX ON CHRTRAN(ENOME,'ÁÂÃáâãÉÊéêÍíÓÔÕóôõÚúÇç','AAAaaaEEeeIiOOOoooUuCc') TO INDTMPNOME

It is not a smart solution, but I think it is a more secure in the app environment.

If I was the only user, I woud create another codepage 1252 with COLLATE.


Thank you,
SitesMasstec