SET RELATION and SET FILTER TO

[Martinius]

Hello everyone,

I have no clue of visual foxpro, I just try to use the examples I found.
Maybe you can recommend a good book?

I have the following code. I'd like to connect two tables with different
field names and I want to limit/filter/select records from the child table.
Do you have some tips?

use fe_fertkop in 0 alias fe_fertkop shared again order tag wanr
use fe_fertarb in 0 alias fe_fertarb shared again order tag wanr
use projkopf in 0 alias projkopf shared again order tag projnr
use projpos in 0 alias projpos shared again order tag posprojnu


* seems to work correctly *
select fe_fertarb
SET RELATION TO wanr INTO Fe_fertkop ADDITIVE

* seems to work correctly *
select fe_fertkop
SET RELATION TO projnr INTO projkopf ADDITIVE

* here i want to connect/relate fe_fertkop.projnr with projpos.posprojnu *
* gives the error: 'the variable posprojnu is not available' *
SELECT fe_fertkop
SET ORDER TO projnr
SET RELATION TO posprojnu INTO projpos ADDITIVE

* here i want to 'limit' the resultset of projpos table *
SELECT projpos
SET FILTER TO posfolgnu = fe_fertkop.pos

Select fe_fertarb

Thank You.

 

[Martinius]

Basicly what I want from projpos is:

SELECT partbez1, partbez2, kstträg FROM projpos WHERE posprojnu = fe_fertkop.projnr AND posfolgnu = fe_fertkop.pos

 

[Olaf Doschke]

The syntax meaning is

SELECT relationsourcetable
SET RELATION TO fieldname INTO relationtargettable

The fieldname can also be an expression, but should address values from the relationsourcetable, the relationargettable needs to have an index set, in which this sourcetable expression or fieldname value must be a value type you can SEEK in the index set on the relationtargettable. It doesn't need to be 1:1 the same name, it needs the same value type and of course nature/meaning. You specified posprojnu as fieldname and also as index tag name of the realtiontargettable. But names can differ, especially since index tag names are limited to 10 chars also for dbfs belonging to a dataase, which means no 10 char limit for field names. More so, even if index tag names match field names, the index is set on relationtargettablebut the field value taken from the current workarea relationsourcetable, so it's likely field names of the starting table of the relation don't match index tag name of the target table.

'The variable posprojnu is not available' points to there being no field named posprojnu in fe_fertkop which relates to the posprojnu index tag set active in projpos. The message is talking of a variable missing, but means a field not found. It's simply a result of VFP looking for field names first, but if no such field is part of the current workarea the name could also mean a variable. And it's likely you know your tables and when you spacify a non existing field name you mean a variable. In the context of SET RELATION that makes least sense, but search priorization is field names first, variable names last, therefore that error message. As you said yourself, table field names don't match, so you've got to understand meanings more than devs relying on same naming making things easier to cope with. It's most likely you mean some other field and thought you need to specify tht index tag name at that position of the SET RELATION command.

I personally like to make a difference for different entities, items, objects, to make clear what name and what type of object I talk of in code, so I tend to have my own naming convention extension to tag names and prefix them depending on index type with p for primary index and x for regular indexes. This also makes me see what names in commands really relate to field names, varriable names or index tag names. They are totally different stuff and thus should not have overlap in names. It rather hides the meaning and understanding of commands, if devs are lazy about this differences and argue with commands being easier when names match. No you lack an understanding about what clauses of commands really address field names or tag names, for example. That aside is personal taste and your data doesn't seem to even be in the nature of the normal VFP naming conventions.

Also it helps I'm german, because I recognise kopf as head data, it seems to me you do one relation from head to child table and one from child to head table. In itself that's possible and nothing seems wrong, but if you don't get the desired result in browsing the data when workareas are connected via relations, then it's a sign you made the wrong relations. One possible extension is to SET SKIP TO in case of the relation set from head to detail data. It's a detail making browse windows work different via the relation and inserting non existing rows with all ***** in the browse of head data, so the rows rather mean a copy --"-- of previous row and row numbers of head and detail browse match, so you can display them side by side. Same happens within grids in a form.

Nevertheless - let me speak for others here, too - we can't judge what's really the correct way to do things without knowing tables, fields and their meanings and types and sample values and also indexes defined and theoretical relations between the data to make the technical SET RELATIONs for your query. On the other hand VFP can do SQL, unless you're at legacy foxpro, when you should also consider posting to forum182

Bye, Olaf.

 

[Mike Lewis]

Welcome to the forum, Martinius - and to Visual FoxPro.

From your second post, it looks like you might already be familiar with SQL. Is that right? If so, did you know that VFP fully supports the SQL SELECT statement (with a few minor exceptions) - as well as INSERT, UPDATE, DELETE and seveal others?

If you are comfortable using SELECT, then you probably don't need to worry about SET FILTER or SET RELATION.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Martinius]

Hi, I got it to work. From what OLAF wrote I suspected I mixed up the field name in SET RELATION.
So I wrote:

SET RELATION TO projnr INTO projpos ADDITIVE

The SET FILTER TO statement then seemed to work as well. I'm very happy


@Mike

How could I have written this in SQL with the correct syntax? Just curious
because I'm way more comfortable writing SQL.

 

[Olaf Doschke]

If your own sql [tt]SELECT partbez1, partbez2, kstträg FROM projpos WHERE posprojnu = fe_fertkop.projnr AND posfolgnu = fe_fertkop.pos [/tt] does not work, then you may not have fe_fertkop open currently. As you address projpos and fe_fertkop fields, I'd also expect both tables to be part of the query, so I'd expect a left/right/inner/outer join of fe_fertkop, the join corresponding to the relation you set in the original code. It's very special of VFP you can also address fields of a table neither mentioned in the FROM clause or via a JOIN, because a query can address any workarea alias names in scope of the current datasession but would then only read the current row of fe_fertkop in this case. So you are sure this is the SQL you want?

What's your VFP version?

? Version()
_cliptext = Version() && and then paste it here

Bye, Olaf.

 

[Martinius]

I didn't test the SQL because I don't know how to embed it correctly in FoxPro code.
As I said I don't have a clue what I'm doing I'd like to ask again if you could
recommend a good book.

 

[Olaf Doschke]

SQL simply is part of the foxpro language. You can simply execute it as is. Especially if you have a DBC open via OPEN DATABASE your.dbc you can query on table names without specifinyg the .dbf file name extension, you simply exxecute your sql query and the query will use the DBFs, no preparation needed. Also without DBC you can query DBFs from the current directory or whatever can be found from SET('PATH'). And the query engine will make automatic use of indexes for joins and where clause optimisations.

F1 already is a nice starting point, the help has a good reference on things. If you look around a bit here you see several recommendations of hentzenwerke books, also garfield hudson video tutorials at

http://www.garfieldhudson.com/FreeVideos.aspx

Bye, Olaf.

 

[Mike Lewis]

How could I have written this in SQL with the correct syntax? Just curious
because I'm way more comfortable writing SQL.

VFP's SQL syntax is very similar to standard SQL (specifically, ANSI-92 SQL). If you are familiar with Microsoft's T-SQL or with MySQL, just use the syntax you are used to. As far as I can tell, the code you showed in your second post should work (but I've no way of testing it, of course).

But a SQL SELECT statement in VFP can also include Foxpro variables and functions, including programmer-defined functions. For example, the following is legal:

LOCAL lnLimit
lnLimit = 100
SELECT LEFT(CustName, 1), UPPER(CustAddress) FROM Customer WHERE CustLimit <= lnLimit

Also, be aware that, by default, the output from a SELECT goes to a cursor* named Query, which VFP then displays in a Browse window. Normally, that's not what you want, so you use the INTO CURSOR or INTO TABLE clause to create a new result set.

(*In VFP, a cursor is just a temporary table, which disappears when you close it.)

You might find this article helpful: SQL SELECT in VFP and T-SQL. It summarises the differences between the two versions. As you can see, these are fairly minor.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

One major difference to mention is important, if you expect to be able to edit the result: SQL queries like T-SQL queries only generate a result set (INTO TABLE as mentioned by Mike already is of course an exception in creating a new table, T-SQL has a similar way of creating new tables via a query, which is not the norm of usage). The Query cursor or whatever name you give the result via [tt]INTO CURSOR yourname[/tt] is a separate workarea and when you make this writable by adding READWRITE after the cursor name you don't change the DBFs from which data was read, you only make the result set editable, it's still just a oneway reading of data and changes can be seen in a form, obviously, but are not written back. USE sometable really opens a dbf and you can edit it, also when you SET RELATIONs you get a kind of live view of the data as it is and edits go back to the underlying DBFs. A query rather gives you a portion of data read out from source tables into a new set independent of source, just coming from there, a snapshot copy of data you queried.

You can also have both advantages of sql and writing back to DBFs when you deep dive into updatable views or cursoradapter class or both and learn about the settings to make about specifying keyfieldlist, updatablenamelist etc. to make VFP know the necessary background info to make the backlink to the source dbf files or also remote backend tables with remote views or ODBC or ADODB modes of cursoradapter. It's surely advanced topics, but good to know in advance, before you deep dive into creating queries for all your data access and then also hand craft UPDATES/INSERTS/DELETES of datachanges, when you can instead make use of updatable twoway connections of query results with their source files. This works similar to a ADODB.Recordset with certain Cursortypes being readonly or writing back to the backend in classic VB or ASP or like entity framework allows you to write back in .NET.

Bye, Olaf.

 

[Martinius]

As background information: I have the task to update the reports of our ERP-System
which seems to be programmed in Foxpro. I'm not sure if that means I'm working with
the FoxPro Report Writer. The place I paste the above code is called the 'script
for opening tables' and then I can use the table fields with form fields in the
WYSIWYG editor.

I have knowledge as web developer (JavaScript, php, MySQL) so I just do guessing

@Olaf: Thanks for the books and the videos.

 

[Olaf Doschke]

Not sure what you're working with. Sounds more like a standard set by your company.

The VFP "report writer" would be captioned "Report Designer" and I'd not say it's a WYSIWYG editor, though you design the layout of sections/bands visually in it. How bands position, repeat do page breaks etc. only comes out in the preview or final runs.

Bye, Olaf.

 

[Mike Lewis]

When it comes to working with reports, there are usually two areas to worry about:

- The program or function that extracts the data for the report. This might be what you are referring to as the 'script
for opening tables'. Typically this has one or more SQL SELECT statements which generate cursors, which in turn are used by the report. Or, it might have code which opens the tables (with the USE statement), and sets filters, relations, index orders, etc.

- The Report Designer itself. As Olaf has explained, this is an interactive tool, although not completely WYSIWYG. The report will normally use the data extracted from the underlying tables (see previous paragraph), but can also make use of any other open tables or any variables in scope.

Keep in mind that the report requires a specific table to act as the "driving" table. This is usually the table or cursor that contains the main data for the report. This table must be open in the current work area when VFP starts to print or preview the report. VFP will place the record pointer on the first record, and then send each record in turn to the report. The report will print one instance of the details band for each record.

I know this doesn't directly answer your questions, but I hope it will be useful information.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

I guess your company might be using something else for reporting, there are a few third party reporting tools integrating with many databases, some very closely related to VFP like FoxFire! or FRX2ANY or stonefield query. Another pair of alternatives related to VFP9 report listeners and report apps are foxypreviewer and report sculptor, but also reporting tools usable for multiple databases like Crystal Report or List&Label could be used. You should definitely get an idea of what you're using or are bound to use for getting help and contacts to experts of the fields involved. If such code is used for preparing report data the likelyhood is you're using something VFP related, because third party report writers would most likely address DBFs via ODBC and sql qeries only, not via such a script.

Bye, Olaf.

 

[Mike Lewis]

Martinius, you asked for recommendations for VFP books. I was just browsing Amazon, and I came across a book I've not seen before: Visual FoxPro Made Simple, by Ravikant Taxali. I've no idea if it's any good, but you might want to take a look. Maybe other people here can give you an opinion.

The blurb says: "Designed for beginners as well as experienced programmers, this book provides all features of Visual FoxPro in a step-by-step manner."

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[KALP1]

Martinius, Yes that's good book(Taxali) for beginners. I have gone through it, good stuff for beginners. Infact I have learned VFP with its help.