how to 'set relation' when field names not the same?

[joebloeonthego]

I'm not much of a db-wiz, and have no experience with foxpro, but I'm using a program built on foxpro and I'm trying to alter some of the reports.
I just want to take an existing report and add a couple of items to each detail from another table. I thought I could just open the other table and set a relation on a few fields. But the fields are not the same name (even though they are for the same data).
Surely there is a painfully obvious simple solution?
(feel free to not waste time answering me and point me to some repository of information. I've just been reading the microsoft language references and stuff)
thanx!

 

[xBaseDude]

Surely there is a painfully obvious simple solution?

Yeah, rehire the guy who wrote the original programforyou.

If you do change the header on one dbf, you risk damaging all the coding that will be looking for the original headers to drop the data into.

You could use a cursor, but implementing it without a thorough knowledge of foxpro would be difficult.

Sorry...

...all this and tap dancing too!

 

[wgcs]

Relations in VFP are not set by drag-and-drop as they are in Access; VFP is more flexible than that... You probably have already found the database editor or the report designer data environment (and I'm inferring that you're trying to drag-and-drop the fields there to create the relationships).

In VFP, fields are not related to other fields; Rather, a field in the parent table is related to an index in the child table, so, as long as an index in the child table is of a compatible 'type' as the parent table's field(character, numeric, date, etc) you can create a relation to it; However, that doesn't mean that anything sensible will happen (if the data is in a different domain, though it has the same type, for example, if the parent table field is a Day of the Week, and the index in the child table is on the Month, they're both Character, so you can relate them, but nothing sensible will happen).

However, if you create relations in the Database editor, they don't automatically happen anywhere except when creating a new report.. (or a new form with a data environment, but that's outside this subject..) Relations in the database editor are basically for documentation, and many vfp programmers don't even put them there.

Relations can also be created in code, through the "SET RELATION TO" command.

Not knowing what methods were used in the application you're looking at, we can't tell you how to do what you want, because there are so many ways it could be set up.

 

[joebloeonthego]

so when you say use a cursor, are you meaning make a cursor of the table in question with different field names, and then do 'set relation' to the cursor?

As for the rehire the guy, I'm trying to figure out how much I'm capable of so we're doing minimal rehiring Some of the stuff, yes, we have to have them do it.
I just started working on the system 2 weeks ago, and so far the only report mods have been minor, or I've copied all the pertinent file opening/relations code from other similar reports or what have you, just sort of hacked it out.
I just figured this problem was a super-simple one, I just didn't know the command or whatever. I thought it would have been super-common to have tables that don't use the exact same field name for similar data and figured there'd be something similar to 'set relation' for that.

Can you recommend a good book for foxpro? I'm going to break out some db texts from school (two semesters of db in my diploma program) and brush up a little. Even the simple SQL is a little rusty now.
thanx for the help!

 

[joebloeonthego]

wgcs:
I'm just working in a little Memo field where you can insert some code for opening tables/setting relations sort of stuff.
All the code they've been using consists of
USE, SET RELATION, ORDER TO, SEEK, a couple of what I think are user-defined functions (syfopen, sycurnew, etc.), and that's about it (I think those are the only commands I've seen). Nothing too fancy.

 

[joebloeonthego]

wgcs: upon re-read...
It's a report that already features table A with the two fields I'm interested in being order, and partnum. The table with the info I'd like to add has the fields called dorder, and dpartnum.
So it's the same stuff, just different field name.
And that little Memo code-space I mentioned is for running reports only (each report has it's own Memo field).
thanx!

 

[acerrudo]

SET RELATION does not require that the fields to have the same name, but it is not good database design to make common fields to have differnt names. Problems will emerge
in the future.

Things like this makes our programming life miserable...

So for the guy and others like him, don't ever do this again
hum...

 

[wgcs]

I agree with you, Joe, that it isn't uncommon to have key fields with different names; It is nice to name them the same, but often you're stuck with how an app was originally designed until you can majorly overhaul it...

The only real requirement for setting relationships in code (as in the OpenTables method that you're describing) is that the "child" table have an index on the field you want to control it by. Adding additional indexes shouldn't break any other areas in the program, but there are different ways of managing indexes, so it may not be as simple as:

USE tablename EXCLUSIVE
INDEX ON fieldname TAG indextagname

However, if you have the required index, or you create it, to set the relation all you need to do (as you've noticed) is:

USE mastertablename SHARED
USE childtablename IN 0 SHARED ORDER keyindextagname
SET RELATION TO masterfieldname IN childtablename

Again, there are many variations on this, even in code: Maybe tables are always opened exclusively instead of shared; maybe tables are usually opened with a different alias; etc.

The important thing is that the index expression ( KEY() ) of the child table evaluates to a value that matches the "masterfieldname" in the master table.