Set a TAG for a field programmaticaly 2

[SitesMasstec]

Hello colleagues!

I have a free table named ETIQUETA.DBF:



Now I want to create a TAG (ascending) for the field CCIDA, but programmaticaly.

I tried many commands like

ALTER TABLE ETIQUETA ... TAG CCIDA

, but I failed.

Is it possible?

In fact, I want this for another table, derived from another table. The above table is just to simplify the problem.

 

[Paul Gibson]

Look at the INDEX command, for example:

INDEX ON CCIDA TAG CCIDA

The default order is ASCENDING so you don't need to specify that but as the help says:

By default, Visual FoxPro displays and accesses records in ascending order. However, you can include ASCENDING as a reminder of how records are displayed.

 

[GriffMG]

It's probably not related, but your example has the table opened read only?

 

[EinTerraner]

If you use this index temporary for a view and/or printing issue, you also can use the SQL SELECT.

SELECT * from "ETIQUETA" ;
  INTO CURSOR CursorName ;
  [WHERE any filters ;] && optional
  ORDER BY CCIDA

As your picture schown is the table readonly, then you can't any index-tags

 

[SitesMasstec]

Griff: the table is opened as EXCLUSIVE (not Read Only).

 

[Premal Vala]

Hi,

Tag is not part of table structure in VFP, you need to use INDEX ON Command for any Tag you want to create. So, for your example above, once you open the table (exclusive) you need to add following command:

INDEX ON CCIDA TAG CCIDA

(as Paul has already mentioned)

This will create you index and going forward you can use it with your table as

USE ETIQUETA ORDER CCIDA

Thanks,
PREMAL

 

[Mike Lewis]

You mentioned that this is a free table. Although you can use ALTER TABLE to create an index and assign it a tag, you can only do that if the table is part of a database.

From the VFP Help:

ALTER TABLE - SQL Command
You can use ALTER TABLE to modify the structure of a table that has not been added to a database. However, Visual FoxPro generates an error if you include the DEFAULT, FOREIGN KEY, PRIMARY KEY, REFERENCES, or SET clauses when modifying a free table.

For free tables, you would do this:

INDEX ON  <some expression> TAG <tag name>

Mike

 

[Chris Miller]

Do you know how you can revisit your old questions? In the top right you see your name. there's a dropdown when you click on that, and there you find "Your content", which is all your posts.
You have been told how to create indexes programmatically in previous threads:

Create a table with Tag indexes - Microsoft: FoxPro

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...

www.tek-tips.com

INDEX versus TAG - Microsoft: FoxPro

Hello colleagues! I would like to have your opinion about INDEX clause when opening a file (free table only). I have the following in a PRG file to add a new record, update or delete records: SELECT 1 USE CLIENTES INDEX...

www.tek-tips.com

 

[SitesMasstec]

Premal & Mike:

ETIQUETA.DBF is a free table (I have not used VFP Database in this life, yet )

In a some parts of the program I have:

INDEX ON NOVOCNOME TAG NOVOCNOME
...
IF XQualOrdem="A"
     Ordem="NOVOCNOME"
ENDIF
...
USE ETIQUETA ORDER &Ordem

Chris: Yes, in the first post you mentioned ("Create a table with Tag indexes") Steve Yu had answered:

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

I will pay more attention next time. Thanks.

 

[Mike Lewis]

One further point ...

You say you have the INDEX IN ... in "some parts of the program". Do you know that you only need to create a given index tag once (for a given table). Once it has been created, it stays in existence until explicitly deleted, even after you close the table or terminate the program.

Mike

 

[SitesMasstec]

Oh, yes Mike, I know.

I let you dear colleagues misunderstood me. I would mean:

In one part of the program I have the command:

INDEX ON NOVOCNOME TAG NOVOCNOME

In another part of the program I have the command:

IF XQualOrdem="A"
     Ordem="NOVOCNOME"
ENDIF

And in another part I have:

USE ETIQUETA ORDER &Ordem

Just once each command.

Sorry.

 

[Chris Miller]

Your question was how to create an index tag programmatically, that's done with the INDEX command. Even if you didn't revisit your old threads that should be clear by now, but your listing of the three program code lines almost shouts out you have the code but don't kno what lines does what.

The importance of what Mike Lewis points out is that by redoing INDEX ON you're wasting time, energy, effort, patience of users. Once an index is created it's created and grows and shrinks with the data. The only reason Mike points that out is that legacy index types in IDX files don't have that behavior, but it's still in legacy developers habits to repeteadly do INDEX data, though that's just a waste of time once it's done in the major CDX file of a table.

It's also no virtue to only have one place in all your code to USE a table ordered by a tag nor to only have one place in code that decides which order tag to use. Regarding that, you could even spare to first set a variable but use the table ordered by the tag you know you want to use or SET ORDER to it.

It's not even a virtue to only have one line of INDEX ON... in your code, because that doesn't tell anything about how many times it's executed. You usually generate tables at design time, during development and include them in a setup that just puts them on the end users computer, there's not even the necessity to have any INDEX ON line of code, therefore. There are good reasons to programmatically create new tables including their indexes and not only use table files you generate during development, but the number of search results will never tell you anyhthing about the quality of code regarding that.

Besides, most of all these things have been told to you in the threads I quoted. It's watering down a knowledge base if you add the same answers to the same questions into a forum.

 

[SitesMasstec]

Dear Chris: you are right about the comands I presented here, but... as I said in my question post in this thread:

In fact, I want this for another table, derived from another table. The above table is just to simplify the problem.

The real problem is more complex:

* Abertura do arquivo com a indexação escolhida
SELECT 1   
USE CLIENTES       && The file with data to be processed

* ---------------------------------------------------------------------------

ARQUIVO55=SYS(2015)      && Tempory file to store selected records from CLIENTES.DBF

COPY STRUCTURE TO &ARQUIVO55

SELECT 55
USE &ARQUIVO55

* ==========================================================================
*  Creation of new fields in the temporary file to store names without accentuation
*  For example, if the field CNOME stores ÉPICO the new field NOVOCNOME in the temporary file will store EPICO
* ==========================================================================
SELECT 55
USE &ARQUIVO55 EXCLUSIVE
ALTER TABLE &ARQUIVO55 ADD COLUMN NOVOCNPOP  C(22)   
ALTER TABLE &ARQUIVO55 ADD COLUMN NOVOCNOME  C(44)   
ALTER TABLE &ARQUIVO55 ADD COLUMN NOVOCCIDA  C(20)  


SELECT 55
USE &ARQUIVO55
GOTO TOP

DO WHILE NOT EOF()
    YNOVOCNPOP= CHRTRAN(NOVOCNPOP,'ÁÂÃáâãÉÊéêÍíÓÔÕóôõÚúÇç','AAAaaaEEeeIiOOOoooUuCc')
    YNOVOCNOME= CHRTRAN(NOVOCNOME,'ÁÂÃáâãÉÊéêÍíÓÔÕóôõÚúÇç','AAAaaaEEeeIiOOOoooUuCc') 
    YNOVOCCIDA= CHRTRAN(NOVOCCIDA,'ÁÂÃáâãÉÊéêÍíÓÔÕóôõÚúÇç','AAAaaaEEeeIiOOOoooUuCc') 

    REPLACE NOVOCNPOP WITH YNOVOCNPOP
    REPLACE NOVOCNOME WITH YNOVOCNOME
    REPLACE NOVOCCIDA WITH YNOVOCCIDA

    SKIP
ENDDO
USE

Then, based on what the option user chooses (by Code, by Name, etc) I have to use the commands:
To avoid for example the record with the data ÉPICO the field CNOME to appear after the Z letter...

    IF XQualOrdem="N"
        Ordem="CCODI"
    ENDIF
    IF XQualOrdem="A"               && If the user chooses Alphabetical order, by Name
        Ordem="NOVOCNOME"       && the record is ordered without special characters 
    ENDIF                            && (the name ÉPICO will be ordered as EPICO)
    IF XQualOrdem="S"
        Ordem="CSTAT"
    ENDIF

 

[TamarGranor]

A few comments to make your code stronger.

1) Don't refer to work areas by number. To find an available work area, use:

SELECT 0

2) Once you have a table open, always refer to it by its alias. For example:

SELECT Clientes

When you do this, you never have to know the work area number for a table.

3) In your situation, you're opening tables with random names. Rather than using the random name as the alias, assign an alias in the USE command. I'll combine the example for this with the next item because it's too painful to write a use with the macro operator (&).

4) Don't use & as you're using it. It can cause problems in some situations (in particular, if there's a path involved). Instead, when VFP expects a name from you, use what's called a name expression, where you wrap the name in parentheses. So, in your code above, you might use:

SELECT 0
  USE (ARQUIVO55) ALIAS ARQUIVO55

And from then, on, you can:

SELECT ARQUIVO55

Tamar

 

[SitesMasstec]

Hello Tamar!

Following your advice, probably I misunderstood it, I got an error:

 

[EinTerraner]

As much as i undrstand you create a new, empty table with an random-filename and you will use this (empty) copy in workarea 55.

ARQUIVO55=SYS(2015)
** open the source table (why in 1?)
USE clients IN 0 && next free number of workarea
** make new temporary TABLE (why not cursor) with the structure from clients
** your code above copys only the structure without content
SELECT * FROM clients INTO TABLE (ARQUIVO55) WHERE .f.
** ARQUIVO55 has now random workarea-number. therefore close it
USE IN SELECT(ARQUIVO55)
** and reopen in Workarea 55
USE (ARQUIVO55) IN 55 EXCLUSIVE

** Here do your code

** Close the temp-table
USE IN SELECT(ARQUIVO55)

1st: You use a real file with an temporyry name. so you need to delete this file after usage. if you use an cursor instead a table, the physical file will not exist after closing the cursor

2nd: Where do you update your still empty table with data?

the REPLACE UNTIL EOF()-Loop can be done much easier: Here you do not need the YNOVO....
Your ALTER TABLE looks like missing the leading "Y" in the fieldname

Lc_Xchar = 'ÁÂÃáâãÉÊéêÍíÓÔÕóôõÚúÇç'
Lc_Nchar = 'AAAaaaEEeeIiOOOoooUuCc'

REPLACE NOVOCNPOP WITH CHRTRAN(NOVOCNPOP, Lc_Xchar, Lc_Xchar) ;
        NOVOCNOME WITH CHRTRAN(NOVOCNOME, Lc_Xchar, Lc_Xchar) ;
        NOVOCCIDA WITH CHRTRAN(NOVOCCIDA, Lc_Xchar, Lc_Xchar) ;
    FOR .t. ;
    IN (ARQUIVO55)

also the setting of index

DO CASE
CASE XQualOrdem="N"
    Ordem="CCODI"
CASE XQualOrdem="S"
    Ordem="CSTAT"
CASE XQualOrdem="A"
    Ordem="NOVOCNOME"
OTHERWISE
    Ordem=""    && not sorted
ENDCASE

if you post the whole programm it wil be much easier to understand exactly what you try to do

 

[TamarGranor]

I think EinTerraner has given you what you need. I agree with his question about why you are creating a temporary table rather than just using a cursor.

Tamar

 

[Chris Miller]

The real problem is more complex:...

That doesn't matter, INDEX ON is always the command to create indexes.
What you posted doesn't make use of an index in itself, though your last code setting a variable named Ordem suggests you want to use that for setting the order. Well, you always first have to create an index before sorting by it with the means of SET ORDER TO tagname or USE ... ORDER TAG tagname. And all that should be clear from previous threads, too. The threads all cover multiple questions related to your problems.

Regarding your posted code overall, you got some advice already, I spare to add to that, maybe just one thing: INDEX ON also works on cursors, it's not requiring a DBF file to create index tags. INDEX ON always works on the currently selected workarea. Your code mainly raises two questions: What's your goal and what does not work?

 

[SitesMasstec]

Hello colleagues!

"Ein - 1st: " Yes, I have commands to erase these temporary files. I will try to use Cursor, instead, following Ein, Tamar & Chris advice

"Ein - 2nd:" I had not shown in my post, but the code to fill the table with data is:

    SELECT 55
    APPEND BLANK

    REPLACE CCODI WITH YCCODI, CSTAT WITH YCSTAT, CSTA2 WITH YCSTA2, CVEND WITH YCVEND
    REPLACE CNPOP WITH YCNPOP
    ... (etc)

As there are many conditional statements before filling the file with data, it would be very long to use the command, as Ein suggested:

SELECT * FROM clients INTO TABLE (ARQUIVO55) WHERE .f.

Well, it is working fine now after the use of INDEX ON to create TAGs. The data from &ARQUIVO55 are used in another file ETIQUETA.DBF, to print labels.

 

[EinTerraner]

As there are many conditional statements before filling the file with data, it would be very long to use the command, as Ein suggested:

SELECT * FROM clients INTO TABLE (ARQUIVO55) WHERE .f.

Did You ever try the SELECT... WHERE .F.?
even if the FROM-Table (290MB) accessed via internet (VPN) with the WHERE .f. clause it takes ZERO-time.

ans also i assume you scan your sourcetable with a specific SET FILTER TO... and/or SET ORDER TO
and let me guess. You use this temprarty table for something like "REPORT FORM YourFRX-File TO YourOutPutDestination

Where you get the Yfieldnames to replace in the same name without the Y
let me guess once more: You read your "clients"-table store each field in a similar Yvariable berfore you make tzhe replace-commands
so adapt and check following code

LOSE DATABASES
CLEAR

LOCAL SRC as String, DST as String, Lo_Data as Object, Lc_Secs as Number

** set Aliasnames as NAME and as STRING
** so you can access the alias direct or indirect with "()"
SRC = "SRC"
DST = "DST"
**SET STEP ON
** Here I open my daily journal, in which all orders and resulting invoices are saved (since 2016).
** This Ttable contains about 150000 records and is about 90GB in size and has also MEMO-fields (50GB)
USE "Y:\DATA\B003_JOU" IN 0 ALIAS SRC NOUPDATE
?"Records Source = ",RECCOUNT(SRC)

** creating a empty cursor with same structure
** direct usage from alias named "DST"
SELECT * FROM SRC INTO CURSOR DST READWRITE WHERE .f.
** indirect usage from alias named "DST"
SELECT * FROM (SRC) INTO CURSOR (DST) READWRITE WHERE .f.

** if required so do
** SET FILTER TO {filtercondition} IN (SRC)
** SET ORDER TO {Sortcondition} IN (SRC)
Lc_Secs = SECONDS()
GO TOP IN SRC
DO WHILE !EOF(SRC)
    SELECT SRC
** Here I only take the data records that have a valid invoice number
** and no article number (invoice total)
    IF !EMPTY(ORDBELG) ;
    .and. EMPTY(ORDNUMM)
        SCATTER NAME Lo_Data MEMO
        INSERT INTO DST FROM NAME Lo_Data
    ENDIF
    SKIP IN SRC
ENDDO
?"Records Destination = ",RECCOUNT(DST)
?"Time scan full table in Seconds:",SECONDS()-Lc_Secs

here the screenshoot with elapsed time result it's just a blink more as one second