STRUCTURE -- CAN IT BE PUT IN ALPHA ORDER?

[gfrlaser]

Is there a way to reorder the structure of a database so that it would be in alphabetical order by field name? I have many fields and it would greatly simplify creation of my forms. Many thanks to anyone that knows an answer to this.

 

[GriffMG]

I have a little tool which will output the structure of a .dbf file.

If you run that to a text file piped through sort you should get what you need.

You would need to remove the 'rubbish' before you used the output:

viewdbf myfile.dbf | sort > list.txt

Should result in something like this:

----------- ---- --- ---
-*-
*** Data File : POPIN.DBF ***
CLIENTDRG   C     30
COMMENTS    M     10
DRGSIZE     C      2
DRGTITLE    C    100
Field Name  Type Len Dec
FLAG        L      1
ISSUED      L      1
Last update   : 28/02/108   Record size :      153   No. records :     2730
MADEBY      C      8

You can probably work out how to remove the excess if you compare the output with this:

*** Data File : POPIN.DBF ***
Last update   : 28/02/108   Record size :      153   No. records :     2730

Field Name  Type Len Dec
----------- ---- --- ---
MADEBY      C      8
CLIENTDRG   C     30
DRGTITLE    C    100
COMMENTS    M     10
ISSUED      L      1
DRGSIZE     C      2
FLAG        L      1
-*-

I wrote it, a very long time ago - in TurboC I think, it's mine to sell or give away as I see fit, so there are no copyright problems.




Regards

Griff
Keep [Smile]ing

 

[NasibKalsi]

Hi gfrlaser:

Another option you may try is:

use original
copy structure to smain extended
* This will copy the structure to a smain.dbf file
* then index the smain.dbf on field_name
* copy this to another smain2 file from which you can use
* the following (you may need to modify the structure of
* the new structure file smain)


* if you have foxpro 2.6
create dbfname from smain2 extended

* else (alternate to create dbfname ......)
* In vfp 9.0 you can use
creat table dbfname ......
* and then loop(scan) through your sorted table and use
* the following(SQL statement)

alter table dbfname ADD fieldname .....

* Finally
* Make backup of original first
use the dbfname
append from original
copy to original

*
* if you have fox 2.6, it is much faster to do
* copy extended struture and sort that structure file on
* field name and
* recreate a dbf file from the extended struture file.
*


Nasib

 

[baltman]

Give this a try. Note it does replace the original table.

Brian

CREATE TABLE test (z c(3), y n(4), a d, b t) && note original field order
INSERT INTO test VALUES ("Hi", 12, DATE(), DATETIME())

LOCAL ARRAY aa[1]
LOCAL i, cCopyCmd, cOrigDBF

m.cOrigDBF=DBF()
m.cCopyCmd ="COPY FIELDS "

AFIELDS(aa)

ASORT(aa, 1, ALEN(aa,1), 0)

FOR m.i = 1 TO ALEN(aa,1)
  m.cCopyCmd = m.cCopyCmd + aa[m.i, 1] + ","
ENDFOR 

m.cCopyCmd = LEFT(m.cCopyCmd, LEN(m.cCopyCmd)-1)

&cCopyCmd to tblTemp

CLOSE TABLES ALL
ERASE (m.cOrigDBF)
RENAME tblTemp.dbf TO (m.cOrigDBF)

USE (m.cOrigDBF)
BROWSE NOWAIT

 

[NasibKalsi]

* I just checked again and found a bit better solution.
* However, baltman's solution may just be fine.

*
* Improved version .01
*
use original
copy structure to smain extended
use smain exclusive
alter table smain alter field_name c(12)
index on field_name to smain
copy to smain2
create neworiginal from smain2
use neworiginal
append from original
* at this point you have neworiginal.dbf with sorted field names.
*
* Delete all unwanted files
*

 

[Mike Lewis]

Gfrlaser,

Just out of curiosity, why do you want to do this? It won't make any difference to your code or to the performance of your database.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[TamarGranor]

I want to argue strongly against this idea. I think it's far more important to group fields by function in a table.

I have one client right now who I'm mentoring. His tables are arranged (more or less) alphabetically and I'm having a devil of a time understanding the content because there's no context.

Tamar

 

[baltman]

I think alpha order of field names within a table is OK if your tables are what defines the context. A good data dictionary is also pretty useful.

 

[gfrlaser]

Thanks for the tips.

Having nothing at all to do with code or any other function I simply find it much easier to design forms when the fields are in order and I am using a pick list to choose the correct field for the boxes is all. Just a little time saver when you have as many as 80 or so fields to choose from. Its only served as a structure template for that purpose.

 

[jimstarr]

USE oldfile
=AFIELDS(myarray)
=ASORT(myarray, 1)
CREATE FILE newfile FROM ARRAY myarray


Jim

 

[Mike Lewis]

Gfrlaser,

I simply find it much easier to design forms when the fields are in order and I am using a pick list to choose the correct field

In that case, why not use the project manager to select the fields from?

Drill down into the table within the Data tab of the project manager. Find the field you want, and drag it onto the form. This will automatically set the control source and several other properties of the control.

The technique is similar to dragging from the data environment, with the added advantage that, in the project manager, the fields are in alphabetical order.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[TamarGranor]

when you have as many as 80 or so fields to choose from

That's a lot of fields in a table. While occasionally I've seen the need for large numbers of fields, that makes me wonder whether the data is properly normalized. What's in this table that it needs so many fields?

Tamar

 

[gfrlaser]

Its for a work order form that a printing and mailing company uses. I suppose it could contain less fields, but I have no time to spend on normalizing the data, just need to get the capability of printing the forms rolling. Time is money as they say.

 

[TamarGranor]

It's my experience that time spent normalizing data pays off in the long run in both cleaner data and easier-to-maintain code. But only you know your situation.

OTOH, since you're finding the table hard to work with, some normalization might help.

Tamar

 

[Koen Piller]

Hi,

80 fields in one table? That's take a look at Outlook: > 100 fields. Just to proof not all tables are small and easy to handle.

Regards,

Jockey(2)

 

[gfrlaser]

80 fields in outlook? And they say I should be normalizing this data. When I have time I am going to take a peek at outlook, thats interesting, but then... its Microsoft right? Big is better??

 

[SnyAc]

IMHO... the application that I am working with at this time uses many tables with 100+ fields... describing manufacturing items requires many details about the item being manufactured....

Personally I do try to keep the field count lower but every application has it's own needs and requirements.

Andy Snyder
SnyAc Software Services

http://www.snysoft.com

 

[TamarGranor]

I wasn't suggesting that 80 fields was automatically too many, just that it was enough to make me question whether it was normalized.

As for Outlook, I don't think we know what the underlying data structure is, only what's presented in the object model.

Tamar

 

[Imaginecorp]

Grflaser;

Mike Lewis gave you the best advice, drag the fields from the Project Manager, its simple and easier... 80+ fields is nothing... Normilazation in certain instances is over rated... For Print programs where speed is essential its the way to go... IMO