problems joining two tables twice.

rbuser

Dear all,
in the RB_Join table the primary key is made up of the names of
the two tables, which implies that it is only possible to have one
relationship in the join table between any two database tables. There are
many examples where 2 or more relationships are defined, how can this be
accommodated?

regards
--
John Evans
Technical Director
Clear Advantage Ltd

www.Clear-Advantage.co.uk

digitalmetaphors

RB does not support this in the visual interface, where you can pick from
two possible joins relationships between two tables. There can only be one
join relationship between two tables. However, maybe there is a workaround.
Let's assume you have different user access rights and you want to have
different join relationships for between two tables based on the user
rights. If you can determine the different relationships based on a user
level, then you can change this so that if you add another field to the join
table definition for access level. Then allow for multiple joins conditions
between tables. Then based on your new field for the user access level you
can filter the dataset on the form before running the designer. Only one
join definition between each two tables will be shown in visual interface,
but you can toggle between which one is used if you change the column
definition slightly on the database table to support this and filter the
resulting dataset.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Dear Jim,
sorry for the slow response, have been away.
Your possible workaround is not really much use.

My example is very simple.
I have a table called schoolvisits and a table called schoolstaff.
One of the fields in schoolvisits is called visitorpkey and points to the
member of staff being visited ( a record in schoolstaff).
Another field in schoolvisits is called signoffpkey and points to the member
of staff who signs-off the visit( a record in schoolstaff).
There are now two relationships between the two tables. This is all
perfectly normal design for a database and happens all over the place in my
applications.

I have to say that this is the third thing I have found since buying the pro
version, where the implementation of the SQL server version does not match
how SQL server is actually used. I am very disappointed in what I have found
up to now to be an excellent product.

I can see how I can modify the source code to make the user version do many
of the things I want, and this will take a lot of time and money both in
modifying it and then continuing to keep the modifications in further
releases.

I would be very happy to submit a detailed list of the things I think are
lacking in this version if this would be of use.

regards
John Evans


.

digitalmetaphors

You can always create a custom dataview template that has this join defined
in it. Then your users don't have to recreate this by editing the SQL
because they can select the custom dataview template. Custom dataview
template examples are located in our End User demo directory. You also could
create and register a replacement query designer that adds this
functionality in the join dialog. Here is an example of how to customize the
query tools and register them.

http://www.digital-metaphors.com/tips/ReplaceQueryTools.zip

Cheers,

Jim Bennett
Digital Metaphors