Create a table with Tag indexes 1

[SitesMasstec]

Hello colleagues!

I know how to create a free table programatically:

CREATE TABLE RESERVAS (LOCALIZA C(12))           && Cod.Localizador
USE

ALTER TABLE RESERVAS ADD COLUMN EMISSAO D        && Data de Emissão da Reserva
ALTER TABLE RESERVAS ADD COLUMN DOLAR N(8,5)     && Cotação do Dolar
ALTER TABLE RESERVAS ADD COLUMN CODAGENTE N(6)   && Código do Agente/Cliente
ALTER TABLE RESERVAS ADD COLUMN NOMAGENTE C(40)  && Nome do Agente/Cliente

Well, I have not found in all VFP help and books, how to, programatically, index a field. In this case, I would like to create an index Tag, ascending, to the first (LOCALIZA) and the fourh (CODAGENTE) fields...

 

[Steve Yu]

Hello colleagues!

I know how to create a free table programatically:

CREATE TABLE RESERVAS (LOCALIZA C(12))           && Cod.Localizador
USE

ALTER TABLE RESERVAS ADD COLUMN EMISSAO D        && Data de Emissão da Reserva
ALTER TABLE RESERVAS ADD COLUMN DOLAR N(8,5)     && Cotação do Dolar
ALTER TABLE RESERVAS ADD COLUMN CODAGENTE N(6)   && Código do Agente/Cliente
ALTER TABLE RESERVAS ADD COLUMN NOMAGENTE C(40)  && Nome do Agente/Cliente

Well, I have not found in all VFP help and books, how to, programatically, index a field. In this case, I would like to create an index Tag, ascending, to the first (LOCALIZA) and the fourh (CODAGENTE) fields...

index on LOCALIZA + str(codagente,6) tag anytagname

 

[SitesMasstec]

Sorry, Steve, I put the question wrongly.

I need to have the field LOCALIZA indexed ascending , and also the field CODAGENTE indexed ascending, so that when I need I can give the command:

USE RESERVAS ORDER LOCALIZA

or

USE RESERVAS ORDER CODAGENTE

 

[Steve Yu]

Sorry, Steve, I put the question wrongly.

I need to have the field LOCALIZA indexed ascending , and also the field CODAGENTE indexed ascending, so that when I need I can give the command:

USE RESERVAS ORDER LOCALIZA

or

USE RESERVAS ORDER CODAGENTE

Then you can do this:
index on localiza tag tag1
index on str(codagente,6) tag tag2
use reservas
set order to tag1 && localiza, default ascending
....
set order to tag2
.....

 

[SitesMasstec]

Yes, I understood, Steve, thank you.

If I use the command index on... I have to name the tag, as you gave the example above (index on localiza tag tag1).
But if I do not use that command I can "design" the tag in the table (in exclusive mode):



So, in the Table Designer I do not have to insert any tag name. How can I programmaticaly create/alter a table without naming a tag?
So, I can write only this command when I need:
USE RESERVAS ORDER LOCALIZA

Or maybe I have to use

index on localiza tag LOCALIZA?

 

[Chris Miller]

So, in the Table Designer I do not have to insert any tag name.

You assume wrong. If you just click the index in the field you create a tag name. It's displayed in the "indexes" tab of the table designer.
You always sort by tag names, not field names. So one way to get what you want is to name the tag as the field, you'd still sort by the index on the field.

 

[Steve Yu]

Yes, I understood, Steve, thank you.

If I use the command index on... I have to name the tag, as you gave the example above (index on localiza tag tag1).
But if I do not use that command I can "design" the tag in the table (in exclusive mode):

View attachment 128

So, in the Table Designer I do not have to insert any tag name. How can I programmaticaly create/alter a table without naming a tag?
So, I can write only this command when I need:
USE RESERVAS ORDER LOCALIZA

Or maybe I have to use

index on localiza tag LOCALIZA?

Table designer implicitly creates the tag for you, localiza and codagente in your case (you can do a 'Display Status' in command window to see the structure.}
If you create the table programmatically, I believe you'll have to run the 'Index on localiza tag localiza' command to create the index tag(s).
After that, you can either:
1. use reservas order localiza, or
2. use reservas
set order to localiza

 

[Piotr Czekalski]

@SitesMasstec, is this what you mean ?

Sample table data:

RECORD NUMBER​	LOCALIZA​	CODAGENTE​	OTHER DATA​
1​	A​	2​	D​
2​	C​	6​	H​
3​	G​	1​	K​

Results:
KEY OTHER DATA
1.....................K
2....................D
6....................H
A...................D
C...................H
G...................K

Key contains values of fields localiza and codagente

 

[ggreen61]

I would suggest the following:

CREATE TABLE RESERVAS (LOCALIZA C(12), EMISSAO D, DOLAR N(8,5), CODAGENTE N(6), NOMAGENTE C(40))
INDEX ON LOCALIZA TAG LOCALIZA
INDEX ON BINTOC(CODAGENTE) TAG CODAGENTE

If you index CODAGENTE (a numeric value) you will not get a numerical order if converted to text; i.e., you will get the record order of 1,10,100,2,20... Why not define all the fields at one time instead of using the ALTER command which takes longer?

 

[SitesMasstec]

Steve: yes, I used INDEX on LOCALIZA tag LOCALIZA and it is ok.
Pietr: I just was in doubt if I could use the same name of the field for the Tag name. Yes, I can.

Greg: Why not define all the fields at one time instead of using the ALTER command which takes longer?

Because the table has more than 20 fields and it would be scrambled if I put all in one command.

 

[Piotr Czekalski]

Pietr: I just was in doubt if I could use the same name of the field for the Tag name. Yes, I can.

@SitesMasstec, Ok

 

[tomk3]

Hello,

CDX (TAG) are compound index.

A long, long time ago in a project far, far away we used "index on field to nameofidxfile" to index every column in a grid on demand.
So we kept the "main index" (cdx) small.

Regards
tom

 

[Chris Miller]

Because the table has more than 20 fields and it would be scrambled if I put all in one command.

You can format one CREATE TABLE nicely:

CREATE TABLE RESERVAS (LOCALIZA C(12)   ; && Cod.Localizador
                      ,EMISSAO D        ; && Data de Emissão da Reserva
                      ,DOLAR N(8,5)     ; && Cotação do Dolar
                      ,CODAGENTE N(6)   ; && Código do Agente/Cliente
                      ,NOMAGENTE C(40))   && Nome do Agente/Cliente

or perhaps even better

CREATE TABLE RESERVAS ;
   (LOCALIZA C(12)    ; && Cod.Localizador
   ,EMISSAO D         ; && Data de Emissão da Reserva
   ,DOLAR N(8,5)      ; && Cotação do Dolar
   ,CODAGENTE N(6)    ; && Código do Agente/Cliente
   ,NOMAGENTE C(40)   ; && Nome do Agente/Cliente
   )

It's slightly simpler to add in fields with this construct. You can also put the commas at the end if you don't like them to be at the start, but they nicely connect the opening and closing bracket and allow the usual code beautify to have the same small indentation in all lines: 1 tab or 3-4 spaces, whatever is your preference. Most important (for you, I guess at lleast) is the comment per field you have. It's a well known construct you can put a comment on every line in a multi line command, if you just put the semicolon line continuaiton before the && line comment.

 

[TamarGranor]

Hello,

CDX (TAG) are compound index.

A long, long time ago in a project far, far away we used "index on field to nameofidxfile" to index every column in a grid on demand.
So we kept the "main index" (cdx) small.

Regards
tom

I do NOT recommend using separate IDX files to keep the CDX small. IDXs are not updated when you modify the data unless you explicitly open them beforehand. The addition of the CDX (in FoxPro 2.0, I think) was a great change, as it reduced the chance of indexes getting out of sync dramatically.

Tamar