Forms joined with two fields

[cobweb]

Hi there:
I am a bit flummoxed with VFP forms at present.
I have a 'remakes' table with an order number and line as primary key.
I have an 'orders' table with an order number and line as primary key.
I want to create a form based on the remakes table such that, when entering the order number and line, the details of the order number and line from the orders table are displayed. So the remake collects the attributes of the original.
However the form designer does not allow me to join the two keys - the key representing the order number + line is simply not available. I can join the two in the database designer, not the form designer.
All I can think is that the order/line combination in the orders table is a primary key; the order/line combination in the remeakes table is a regular key. I need a separate primary key in remakes - there may be more than 1 remake required for an order line!
Does anybody have any ideas as to how I can create a form where the join is on two field?
Thanks!

 

[Mike Gagnon]

Have you tried to create a compound index based on the two fields ( order number + line )?

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[cobweb]

Thanks, Mike - that was prompt!
Yes, I think I am using compound keys - I used the expression builder thus:
Str(Order 19,2)+ Str(Line 19,2)
The same for each table.
I have just tried writing a query, joining order and line from each table....and get the same problem!
I can join order to order.
I can join line to line.
I cannot join both!
The Add button where I can add a second join is greyed out.
VFP 8 by the way.
Thanks again
Richard

 

[Mike Gagnon]

I take it that your parent table does not contain the line field? You need a common key fields in both tables, set to primary in the parent table and regular in the child table.
The field value itself are not important, but it is important that all the children associated with the parent record have the same values. Use something like [BLUE]SYS[/BLUE](2015) OR [BLUE]SYS[/BLUE](3) for the value.


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[cobweb]

Thanks, Mike. however maybe I am missing something here because it is still not working.
I have definitely got identical fields in each table; order and line. They are of the same type.
I have created a compound key STR(order)+STR(line). This is a primary key in the orders table but a regular key in the remakes.
I want to create a form that has the remakes as the parent and orders as the child, joined on the order and line fields, or the compound key (note that the compound key is the primary in the child, not the parent - is this the issue? - no, because I cannot even create a form the other way round). However the form designer simply does not permit a join on more than one field - and not on the compound key anyway.
I can write a query that joins on the 2 fields so the fields cannot of themselves be a problem; the problem lies with the form?
Sorry to be a pain.....
Thanks

 

[TamarGranor]

You can set whatever relations (joins) you want in code. In the Data Environment Designer (that is what you're talking about), the only relationships between tables that show up are persistent relations.

Tamar

 

[cobweb]

Thanks to you both.
I think I have sorted it by firstly joining order to order then creating a second link, not mattering to what.Amending the properties so as to make line join to line works.
I got a message saying "remakes is not an object" which I studiously ignored and it works fine.
Thanks again