how to make a file from a cursor

[allserve]

Hi colleague,

I have following VFP code. This is the end of filter steps. We keep the project keys in cursor crsTemp7. Then we set relations to the other tables to find other fields and call the breport.

Our wish is now to store all the fields that are used on the report (or even all the fields of all the tables used in the relations) in one flat .dbf.

What can we best do?

===========================================================

Select DISTINCT SciMpart.BprojectNr, SciContp.ContPersKl , SciMpart.RelatieNr, SciMpart.RolSoort;
from SciMpart ;
join SciContp on SciMpart.RelatieNr = Val(SciContp.BedrijfsNr) ;
join crsTemp7 on crsTemp7.BprojectNr = SciMpart.BprojectNr ;
into cursor crsReport

Set Order to BedrijfsNr in SciBedri
Set Order To ContPersKl in SciContp
Set Order To BprojectNr in SciBproj

Set Relation To RelatieNr into SciBedri, ContPersKl into SciContp, BprojectNr into SciBproj

* >>> Here we want to save all fields as a .dbf file <<<

Report form breport Preview

===========================================================

TIA / Marcel from AllServe

 

[ramani]

HI

What you need is a view.. assuming you are using a DBC (database container for the tables). You can use the view designer and create a view choosing all the fields you want from different tables and setting the relationship while designing the view. This can be saved as your view with a name. Subsequently, you have to only open the view just as you open a table and run the report. You dont need any separate table for the purpose.

Hope this helps you. ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[allserve]

Ramani Thanks!

So is it a good working method always to create a view first and always make reports only from views?

This also solves another problem we had: To change our report field definition we had to go to diferent tables and when a field was changed we could not see which it was and had to check all the fields in the report. With a view there is one place to look.

Tnx&rgrds
marcel@allserve.nl

 

[BlindPete]

you can use the
COPY TO [path][dbfname].dbf

Copies the current selected alias -Pete

 

[allserve]

Pete,

we have a cursor, not a table and it is related to fields from other tables thru the relations but this is not active earlier than in the report itself.

How can we use the COPY TO in this case?

Marcel AllServe

 

[BlindPete]

Cursor or table makes no difference to the copy command

SELECT * FROM someTable INTO CURSOR myCusrorName
SELECT myCusrorName
COPY TO...

will work just fine

You can establish relations between cursors and tables. To establish the relationships from the COPYied cursor to other files you would have to use SET RELATION command, and depending on the relations you may need to build a CDX on the COPYied cursor file as well.

See the SET RELATION command in VFP help file


When you say &quot;Save all the fields&quot; do you mean from the cursor only? or the cursor and its relations?

If its the later I'd just save the query string instead -Pete

 

[allserve]

It is indeed the last. All Fields also from the other tables because the cursor only contains for the user meaningless keys to be able to retrieve the real data from the other tables.

I'm sorry but when you say:

&quot;I'd just save the query string instead&quot;

what code do you write?
I want to be able to capture the query that is formed in the report from command but I do not know how to or if it is possible.

tia/Marcel

 

[BlindPete]

> I want to be able to capture the query that is formed in the report from command but I do not know how to or if it is possible.

I'm afraid I don't follow you here.

Are you refering to a grouping etc? If so you might consider sending the report output to file instead. -Pete

 

[allserve]

Pete,

you say as alternative:

&quot;sending the report output to file instead. &quot;

Yes that sounds like what we need. What we have found in the Help is that you can save a spool file for later printing. However we are not interested in the report layout, we just want to have all the fields in one .dbf file. Do you know how to do that?

Good Day
Marcel AllServe

 

[BlindPete]

What I would do is wright my SELECT statement in such a way that it accounts for all your relations. You can use multiple joins in a single statement and filter. If you can't get it all in one select statement, then you can reference the 1rst query with a second query. Ending up with one single cursor.. COPY TO and your done.

I have no idea what sort of relationships you have or how many, but if all your relations are key to a central table

SELECT * FROM aTable LEFT JOIN bTable ON aTable.FieldX=bTable.FieldY WHERE aTable.FilterField = SomeFilterValue INTO CURSOR Cursor1

SELECT * FROM aTable LEFT JOIN cTable ON aTable.FieldX=cTable.FieldZ WHERE aTable.FilterField = SomeFilterValue INTO CURSOR Cursor2

Then combine them:

SELECT {propably want to list the fileds to include} FROM Cursor1 LEFT JOIN Cursor2 ON Cursor1.FieldX=Cursor2.FieldX INTO CURSOR Cursor3

Or just a write a single SELECT statement...

SELECT * FROM aTable LEFT JOIN bTable ON aTable.FieldX=bTable.FieldY LEFT JOIN cTable ON aTable.FieldX=cTable.FieldZ WHERE aTable.FilterField = SomeFilterValue INTO CURSOR Cursor1

Thats what I would do to get what you want. It not very efficient programming wise but will get you the snapshot you are looking for. -Pete

 

[wgcs]

Do these two SELECTs:

Select DISTINCT SciMpart.BprojectNr, SciContp.ContPersKl , SciMpart.RelatieNr, SciMpart.RolSoort;
    from SciMpart ;
    join SciContp on SciMpart.RelatieNr = Val(SciContp.BedrijfsNr) ;
    join crsTemp7 on crsTemp7.BprojectNr = SciMpart.BprojectNr ;
    into cursor crsReport

SELECT crsReport.*,SciBedri.*,SciContp.*,SciBproj.* ;
  FROM crsReport,SciBedri,SciContp,SciBproj ;
  WHERE CrsReport.RelatieNr=SciBedri.BedrijfsNr ;
    AND CrsReport.RelatieNr=SciContp.ContPersKl ;
    AND CrsReport.RelatieNr=SciBproj.BProjectNr ;
  INTO TABLE Everything.DBF

 

[allserve]

Error in following code

set fields to ;
fieldname1 , ;
LOOKUP(tab1.descr, tab2.code, tab1.code) as Description, ;
fieldname2, ;
fieldname3

COPY TO xxxxxxxxx TYPE XL5
set fields to

We got every time the following error for the LOOKUP()function
&quot;Invalid use of a Visual Foxpro function as an array&quot;

 

[DEDMOD]

Well, the LOOKUP() function doesn't return a field name, it returns the contents of a field. So if these contents are the field name you're looking for, you'll need to let VFP know that. I've not played with it, but you might try to put the lookup in quotes or parens or with an ampersand in front and see what happens. Dave Dardinger