Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do I relate two tables with different field names?

Status
Not open for further replies.

vfp4ever

Programmer
Oct 18, 2013
71
East Java
Hello,

I have always, and successfully, related parent and child tables having the same field name as primary and foreign key. For example, customers and orders through their own common CustID field (defined in each table) with a SET RELATION command.

Now, suppose that I wanted to rename the CustID fields (as some have suggested in literature) to CustSID in the parent table (primary key) and CustKey in the child table (foreign key). How am I supposed to do that using the same SET RELATION command?

Thanks,
Dario
 
SELECT CUSTOMERTABLE
SET ORDER TO CustID
SET RELATION TO CustKey INTO CHILDTABLE



Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."[hammer]
 
The actual field names in 2 or more data tables are not important in regards to setting up Related tables.

You need one Parent table
And you need one (or more) Child table(s).

You also need one (or more) fields in the Child table on which you have built an Index (the Index expression can be whatever you need).

Then you select the Parent table and set the relation on an expression that utilizes the values from one (or more) fields into the Child table - the field names are not important

Code:
USE ChildDBF IN 0 EXCL
INDEX ON ThisFld1 + ThisFld2 TAG IndxKey

SELECT ParentDBF
SET RELATION TO Field1+Field2 INTO ChildDBF

As long as the expression values match - those of the Parent to those of the Child (regardless of where these values come from) - the Relation will work.

Don't forget that Field Length comes into play when you use its Value as part of an expression.

So while the field names are not important, the field value (including trailing spaces if appropriate) is the key to working Table Relations.

Good Luck,
JRB-Bldr
 
Scott, you need to set the index in the table, in which the relation ends, not in the table in which the relation starts.
The reason for that is simple, the value you specify from the parent in SET RELATION is lookeup in the index of the child.

Bye, Olaf.
 
Right. The parent can be in ANY order. The child must be ordered by an index that matches the relational expression.

Field names nave nothing to do with it. You're establishing a relation based on the VALUES in the fields.

The child index can actually include additional values in the index expression as long as the index expression matches the relational expression. For example, the invoices controlling index might be custno+invoiceno+Dtos(invoicedate). The parent, Customer, can still set a relation on just custno (or whatever it's called).
 
Also the parent of the relation does not need to be the parent table in the sense of database relations. You can make noth 1:n relations and n:1 relations via SET RELATION, the index in the "child" can be a foreign key index, primary key index or also compound index, as Dan already explained with the full or also partial use of the index expression.

Taking the example of customers and invoices the relation will only point to the first invoice and skipping to the next customer will skip all other invoices of the previous customer, unless you adda SET SKIP TO to make the relation stay on the parent customer and instead skip in the invoices until the next invoice is fro another customer and the skip really skipd to that customer. Look at the sample code in the SET SKIP TO command, it'll show that functionality, only the parent table is SCANNED and though it only has 2 records the scan loop has 8 iterations, as it's automatic SKIP 1 in the parent is instead relayed to the Child1 or Child2 tables.

Bye, Olaf.

 
Right, I was just doing it off the top of my head... was hoping to at least point him in the right direction.


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."[hammer]
 
Thank you all for your hints, and sorry for my silly question.

The probable reason why I have felt somehow confused is that, after using a does-it-all UDF for this task (verifying tags existence, saving the current state, etc...) for years, I must have lost contact with the simple meaning of single commands. Calling this UDF with 3 parameters (I told you that I used the same key field names for both tables), and seeing 3 clauses in SET RELATION (TO, INTO, IN), has mislead me for a little while. Maybe an additional example in the VFP Help would be useful, but now everything is clear. Thanks again.

Regards,
Dario
 
It happens, especially when you don't have a need to look at the code for years (which is why it's good to throw in critical comments from time to time, no matter how well you "understand" it at the time). It's easy to think you'll never have a time when you don't remember what it does, or can't figure it out, but over years it can happen. I'm living that right now, after returning to VFP after some 15 years away from it! My base classes (which I thought were brilliant at the time) have had me on a few occasions now going "What was I thinking!?" :)

So don't beat yourself up about it.

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."[hammer]
 
Thank you all for your hints, and sorry for my silly question.

It's not a silly question at all. SET RELATION vexes a lot of people.

Many years ago, a seasoned FP programmer of some repute came into one of our user group meetings FUMING about the latest beta because "they changed the way SET RELATION behaves! They require the target to be ordered!".

He was met with blank stares and confused looks. "It always worked that way" we explained.

It turns out that for 10 years he'd just been lucky. [thumbsup2]
 
I just want to add that, other than for very complex reports, I can't remember the last time I used SET RELATION in code. I use it in the Command Window sometimes when I need to look at data, but in an application, not so much.

Tamar
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top