can we change sequence of fields in table by code?

[Nifrabar]

Hi!

When I browse a table it shows data like:
Field1, Field2, Fieldn.

Can we change (by code) the sequence of showing columns e.g. to Field2, Field1,Fieldn.
I need this as I am converting tables.
Changing of sequence makes life during this conversion more easy.

I looked at the help file but only found how to change the columns using alter table myTable column myColumn etc...

TIA
-Bart

 

[alvechurchdata]

You can specify the fields you want to see with the browse command:

Browse fields Field2, Field1, Field4

For a permanent solution you could use SQL to select all the fields in a specified order and then output them to a new table. You would then have to rename that table and recreate indexes and relationships and the like.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[JRB-Bldr]

Additionally if you are looking at a table in a Grid within a Form, you can separately specify the ControlSource for each column in the Grid.

This would allow you to see only those fields which you wish to see and in any sequence that that you desire.

Good Luck,
JRB-Bldr

 

[Nifrabar]

Well,
thanks for both solutions.
I hoped there was an instruction which could do the modification same as can be done manual with the modi struc command.
Anyway I feel most easy is using the SQL-solution which gives a fast result.
Thanks for your assistance!
-Bart

 

[Olaf Doschke]

Well, modi struc (table designer) does create a whole new dbf file, if you switch column positions, as then every record needs to change physically (there is no such thing as column position in the dbf header only). Therefore the performance of an SQL doing a full SELECT of all the data in a certain field order is even a little better than doing this via table designer or ALTER TABLE. You then already have the data in a cursor to append to the new table.

Besides that the order of fields doesn't matter with APPEND or GATHER, the values are added to another table by matching fieldnames. So you don't need to switch any order of field names in a table conversion process.

The order only plays a role when doing APPEND FROM ARRAY, as array columns have no name, just a column number.

Create Cursor curOld (id I, cText C(10), iNumber I)
Insert Into curOld values (1, 'hello', 2)

Create Cursor curNew (id I, iNumber I, cText C(10))
Append From DBF('curOld')
Browse

Values are copied from curOld - representing an old table - into the rearranged fields of cursor curNew - representing a new table. You can even have more or less fields in curNew and Append works with it.

Bye, Olaf.

 

[Mike Lewis]

Bart,

Why exactly do you want to do this? If it's just to get the columns in a specific order within a Browse, you should go with Geoff's suggesion and use the FIELDS clause. It's fast and easy, and won't have any implications for other code that uses the table.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Nifrabar]

Mike,

To answer your question why I want this:
I am converting tables from old application.
In that application there is a column showing address like e.g. "Downingstreet 10".
For the conversion I need fields like :
Street 'Downingstreet'
HouseNr. '10'

Alter table myTable Column HouseNr c(50) does the trick for adding the column. But than that column is last in sequence of a browse.

There are also other columns I have to add and locating those columns on a more logic place within the sequence makes the converted resulttable (holding over 60 fields) more easy to 'read' and thus easier to check for possible errors.

-Bart

 

[Olaf Doschke]

Bart,

take any table, browse it and while browsing it, move the columns in the header of the browse window as you like. Close the browse, then browse last. You'll find fields in the order you left them (and with the width you left them). This is stored in foxuser.dbf. So for that aspect you don't need to change column order, the physical order of fields in a table is irrelevant.

Bye, Olaf.

 

[Koen Piller]

Bart,

An other solution would be to add the extra fields just at the bottom of the table and then make an SQL select into cursor myTable to be used for browsing, the SQL select you can make any sequence of fields you like best, you could even combine separate fields to one.
Take it the browsing is only for you not for your endusers, because I would always use a grid for endusers to browse and never a 'direct' browse since fields tend to have cryptic names like CFIRSTNAME or even worse CFSTNAME and the like.

Groet,

Jockey2

 

[friend01]

If all you want ot do is display the fields in a different order when you browse. All you have to do is:

BROWSE FIELDS fieldname1, fieldname5, fieldname3, fieldname2,fieldname4, etc.. (whatever the order of the fieldnames you want to see them in)

very simple solution as already mentioned by Geoff & Mike.


Try it,
F1