> I am new to SQL and IBO so I a sure I am doing something wrong but I
> set up as a join as follows:

Hi slsolutions :-)

Sorry if my first response was a bit short, I see you have started a
new thread about joins in which the correct join syntax has been
explained to you. However I am responding to this thread because one
thing that was not obvious in your new thread was that the join was
setup to replace what had been an embedded select - and this is
important!

Joins by default, do an "INNER" join and you may not get the same
result as using an embedded select. To get the same results as an
embedded select you must use a LEFT OUTER join - which many people
will tell you is not idea, but it is what is required to achieve what
you want. So...

SELECT C.COSTCATEGORYNO,
C.COSTCATEGORYNAME,
C.COSTCENTERNO,
/* replace this with a join
(SELECT COSTCENTERNAME
FROM COSTCENTERDEFINITIONS D
WHERE D.COSTCENTERNO=C.COSTCENTERNO) AS COSTCENTERNAME,
*/
D.COSTCENTERNAME,
C.OVERHEADPERCENT,
C.REQUIRED,
C.USED,
C.POSTED
FROM COSTCATEGORYDEFINITIONS C
LEFT OUTER JOIN COSTCENTERDEFINITIONS D
ON D.COSTCENTERNO=C.COSTCENTERNO

Set the KeyRelation to COSTCATEGORYDEFINITIONS
Set the KeyLinks to COSTCATEGORYDEFINITIONS.COSTCATEGORYNO
(I think that must be your primary key)

You dont expect COSTCENTERNAME to be editable, it was not editable in
the embedded select.

You can now setup COSTCENTERNAME in your orderingitem and
orderinglinks. It may not be very efficient, but if the dataset is
not too large you should be OK.

hth

--
Geoff Worboys - TeamIBO
Telesis Computing