Home End User
New Blog Posts: Merging Reports - Part 1 and Part 2

problems joining two tables twice.

edited March 2003 in End User
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

Comments

  • edited March 2003
    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


  • edited March 2003
    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


    .

  • edited March 2003
    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


This discussion has been closed.