DBF to CDX

Which is it?
  DBF to CDX
     or
   .BDF file to .CDX

I guess that it really does not matter since you cannot convert one of these file types to a CDX file.

A CDX file is an Index file which is created ON a DBF file.  

The various indices which the CDX file may contain are dependent on what Indexes were created ON the DBF file.  
This is generally done in FP/VFP code somewhere.  
And the Indicies can be created in ANY manner as an Expression of any of the fields within the DBF file.

If you have a backup of an existing associated CDX file, I'd recommend restoring it.

If you need to create a new CDX file, then you can create Indicies on the DBF by using FP/VFP.
If the later, and you are not already familiar with it, you might want to look at the Indexing Records video tutorial at:   http://www.garfieldhudson.com/FreeVideos.aspx

Good Luck

That's what I was afraid of based on the searches I've done. So do you recommend buying FoxPro? Never used it before, will it be easy to convert the dbf file to a CDX or IDX file?

Thank you.

will it be easy to convert the dbf file to a CDX

As I said above you CANNOT CONVERT a DBF file to a CDX file.

You can only BUILD an Index on the DBF file which is saved into the CDX file

And the Index that you BUILD is dependent on what you need
The Index has an algorithmic Expression build on the desired data table Fields that you want.

So I could build many, many Indicies, each different, but not necessarily what you need.

Indicies can be built to organize into a particular manner for reporting, etc.
And/or Indicies can be built to Relate 2 or more data tables based on having common field values.

Yes, you will need Foxpro/Visual Foxpro to create the Index.
But just having the Development Language will not, by itself, get you what you need unless you know what Expression you need to build into the Index.

USE MyDBFFile  EXCLUSIVE ALIAS MyDBF
SELECT MyDBF
INDEX ON Field1 + Field2 TAG Indx1  && An example of the INDEX command
INDEX ON Field2  TAG Indx2  && Another example of the INDEX command
INDEX ON DTOS(DateField  TAG DateIndx  && Yet another example of the INDEX command
USE

Review the video tutorial I mentioned above and determine what you need in the way of an Index Expression.

Once you know what you need, come back and we will gladly help you with the appropriate INDEX command expression.

Good Luck

A CDX always can only acompany a DBF, it's not a standalone files, as it is an index pointing to the records of a DBF.

You may have something completely different at hand, that even has nothing to do with dbase or clipper or foxpro.

So let me ask a counter question: For what application or component do you need a CDX file? And what would a CDX file be from  your knowledge or point of view?

Bye, Olaf.  

Thank you jrbbldr, the information you provided are very valuable - not sure if " I " can do any of that. I will let you know.

Olaf and all -

The HR dept. uses Report Writer to open three DBF files imported from various DBs. They need to link these DB to an index file that reference one of the colums for instance (common on all 3 DB such as Emloyee ID) so they can pull the data needed.

Hope this helps.

Best Regards,

They need to link these DB to an index file

Lets correct the terminology - they would not link these DB to an index file

Instead they will link (or in our FP/VFP terminology RELATE) these data tables by using the appropriate Indicies in each data table's separate Index file.

So at some point in time Indicies would be created on the 3 data tables - something like
* --- Build An Index on EmployeeID For Each Separate Data Table ---
* --- This Will Create or Modify CDX Files For Each Separate Data Table ---

USE MyDBFile1 EXCLUSIVE
MyDBFile1
INDEX ON EmployeeID TAG EmplID  && Create Index Named 'EmplID" For Table 1
USE

USE MyDBFile2 EXCLUSIVE
MyDBFile2
INDEX ON EmployeeID TAG EmplID  && Create Index Named 'EmplID" For Table 2
USE

USE MyDBFile3 EXCLUSIVE
SELECT MyDBFile3
INDEX ON EmployeeID TAG EmplID  && Create Index Named 'EmplID" For Table 3
USE

Then somewhere else these now-existing Indicies can be used to RELATE the data tables from a "Parent" table to the multiple "Child" tables- something like:

USE MyDBFFile3 IN 0
SELECT MyDBFFile3
SET ORDER TO EmplID    && Activate the existing Index named "EmplID"

USE MyDBFFile2 IN 0
SELECT MyDBFFile2
SET ORDER TO EmplID    && Activate the existing Index named "EmplID"

USE MyDBFFile1  IN 0   && For This Example This Is The "Parent" Table
SELECT MyDBFFile1
SET ORDER TO EmplID  && Activate the existing Index named "EmplID"
* --- Establish the Relation Between Parent and Child Tables ---
SET RELATION TO EmployeeID INTO MyDBFFile2 ADDITIVE  && Establish Relation To table 2 - Child #1
SET RELATION TO EmployeeID INTO MyDBFFile3 ADDITIVE  && Establish Relation To table 3 - Child #2
<do whatever>

Now as the record pointer is moved through the Parent table, the routine can 'see' the field values in the 2 Related Child tables.

NOTE - for some reports when you want all of the various Child record displayed for a single Parent record, it is sometimes easier to set up a Reverse Relationship between the tables.

OR - often even easier, run a separate SQL Query to accumulate all of the records meeting the report criteria from all of the associated data tables into a single table/cursor (a.k.a "record set") which would then be used instead of the 'original' tables for the Reporting purposes.

Good Luck

Which report writer do they use? Crystal Reports? You don't need a CDX to link tables, you need SQL with a JOIN. to link data. And a CDX can accelrate that. Queries on the unindexed DBFs can indeed make the query performance quite intolerable, but they are optional.

They always are an addition to the DBFs, and they don't contain all the data, they just make referencing, relating or joining data faster.

The Report Writer and it's capabilities is the interesting part. Could you be more specific? AFAIK Crystal or List&Label are capable to execute SQL, that would perhaps also include SQL to index the tables, that is to create CDX or IDX.

Bye, Olaf.

The program is R&R Report Writer. When we try to link a file (an index) it looks for .cdx, .mdx, .ndx, idx. The index file we're looking for should have the same tables that one of the BDF files - and that's why I was asking if we can just convert the BDF to CDX file.

Best Regards,

When we try to link a file

I also do not know R&R Report Writer, but when you say that you want to "Link a file", do you mean that you are attempting to associate one or more Secondary ('Child') table(s) to a Primary ("Parent") table?

 * Which table is the Report Writer 'complaining' about?   The Parent or the Child table?
* And is the Report Writer telling you what Field(s) or complex Expression it wants to use as an Index 'link' to the other table?

Good Luck

Thank you both for all your help.

This process needs to run periodically – and for reporting purposes, needs to be run in R & R –  Is it possible to create an index file in SQL? Then save that query as .CDX, IDX, MDX or NDX? I am not a DB guy and all the stuff mentioned above "Use My...." is way over my head. This is a very simple DB with 6 tables line items so I didn't think it should be this complicated...


Thank you,

Thanks, jrbbdr, that's service!

So it turns out as I already said, you don't need a CDX, a CDX would NOT contain the data, just pointers to data, the data stays in the DBF and there is the SQL language to join that data. Indexes are just optimisiing performance.

Crest_Admin, we already understod you're not a "DB guy" or developer, but by now you should have understood that a CDX is NOT a conversion of a DBF, you've been said so by both me and jrbbldr.

jrbbldrs code would generate CDX files, you (or your IT people!) just need foxpro to execute this, and I already also mentioned you can also create CDX with the OLEDB Provider. Get develeoper staff here and let them talk to us, there is no way to put code in more human readable format, so you could understand it. The only thing we could do is provide a program for you to index data, but that's beyond what a forum is for. And we would need specifications you not seem able to give to us.

In short: You already got several solutions, and it's understandable you don't see the wood for the trees, so forward the advices to your staff or get them to us. This would speed up the communication process.

Bye, Olaf.

I've used R&R Report Writer quite a bit in the past.  As cited in the bit of Flex Link documentation above, its later versions can build an index on the fly, but you do have to have an understanding of your data structures and relationships to use it.  On the other hand, you don't have to be a programmer to build reports with R&R.

Geof

Well,

you don't have to be a developer to create an index, but you need foxpro obviously. Once you have created a cdx it's permanent. You can also use visual designers, in this case the table designer and go into the index section and choose a field to index, it's quite simple.

Bye, Olaf.

Hello All,

I thank you all for all your support.

I will go ahead and purchase Foxpro to create the index file, I just hope it will be easy to do in Foxpro.

I will be off for couple weeks and will get back to you as I think I still need some help.

Again, thank you all.

I just hope it will be easy to do in Foxpro.

Creating the Index for the various data tables in Foxpro is easy (see example code above).

The 'unknown' (to us) will be - do you know what field(s) to build the Index on?
If that is known then no problem.
If that is uncertain, then you can successfully build index after index and not get what you need.

Good Luck