Re: Multiple Master-Details forms problem

>I have a couple of tables which contain up to 5 foreign keys - the keys are
>meaningless numeric sequences, the relevant tables have a meaningful
>descriptive column. So, I have M-D relationships where there are many
>Masters to each Detail.
>
>I'd like to be able to do the following:
>
>1. Create a form for creating this table, using the other tables for easy
>point and click. When I try this I get all sorts of problems with required
>values not being present as I move between the different masters.
>
>2. Display the detail table as a multiple record tabular form with all the
>descriptive values rather than the keys in such a way as to allow easy
>updates. I don't see any easy way of displaying the descriptions along with
>the keys. Possibly a view which is kept in the same order as the detail
>table... yuck!
>
>Any ideas or sources of inspiration?
>
>
>Andy
></PRE></HTML>

If I understand your question, you have a table with 5 columns that reference values in other tables. If this is correct, we do this a lot. here is how we handle it...

1. Create a view joining the lookups that is something like this...

select master.unique_key, key1, descript1, key2, descript2, key3,descript3... from master, lookup1, lookup2, lookup3... where master.key1=lookup1.key and master.key2=lookup2.key and ...

You do not have to worry about the order of the columns in the view compared to the order displayed in the form.

The important part is to include both the key value AND the description in the view definition.

2) In your form, set the base table to be the view. Display only the descrioptions, not the keys.

3) Use LOVs to provide lookups; for each of the descriptions, build a record set that selects the key AND description, and an LOV that uses it. On the column assignments in the LOV, fill the description AND the key; but only display the description.

4) You will need to replace the On-UPDATE, On-Delete and On-insert to actually do the update / delete / insert on the real table (Forms will want to do the view). If you have a unique key on the master, bring it along in the view, and the updates and deletes are real simple.

Hope this gets you started in the right direction.

Dan Hekimian-Williams Received on Sun Jun 21 1998 - 04:51:06 CEST