Re: Master-Detail form problem

Damir Dezeljin wrote:

> Hi.
>
> > If you don't know what you are doing don't do it. If you want to make a
> mess
> > don't ask others to clean it up for you. The sentences above that I quoted
> are
> > the root of the entire problem. If you didn't keep a back up from before
> you
> > destroyed the functionality you seemingly want ... start from scratch.
> Ok ... I started from scratch. Now I know why I tried to implement automatic
> update manualy ;)
>
> To describe my problem, I have first to describe what I want to achive:
> - I'm developing a 'bill' form as a homework at my faculty;
> - the bill form should have two components which are directly using two
> Oracle tables (bill, article_bill):
> * bill table: bill_id, storehouse_id, buyer_id, issued_date,
> expiration_date;
> * article_bill table: bill_id, article_id, quantity, price, discount
> - from the above described tables, it is obvious that the relation is
> one-to-many, so I created a 'master-detail' form;
> - I would like to achive that the user can just enter the bill_id in a field
> to display a past bill, on the other side I would like that the bill_id for
> a new bill is automaticaly generated from a sequence:
> * the first one can be achived with 'Query->Enter' and 'Query->Execute',
> but I don't want that the user has to press first one button, then enter the
> bill_id and then press another button to display the bill. It will be nice
> if the user can just enter the bill_id and press <tab> to get the paste bill
> on the screen and if the bill doesn't exists he should get an alert;
> * it is also trivial to get a next sequence, but I don't want that the
> sequence increse every time the user travel with <up> and <down> cursor keys
> to and from a new bill. For this reason I can't use :SEQUENCE.nextval as a
> default value for a field. So this can be solved by disabling the field and
> in 'pre-insert' triger write the code to get a new id from a sequence.
> - it will be also nice if a user can just navigate trough fields with <tab>
> and <shift><tab>. If data integrity is on, this isn't posible, because of
> restrictions in the database (the user always get 'field XX can not be
> null'.
> - when entering a new field the user should have the ability to just a
> 'buyer_id' or the 'buyer_name'. If he enters a 'buyer_name', 'buyer_id'
> should be automaticaly retrivied from the database from 'buyer' table which
> isn't on the described form. For this I can use a 'post-text-item' triger.
>
> For mentioned reasons I removed the checkbox on 'Part of database' property.
> So how can I fill all records of my form whey I just enter the bill_id? Is
> there any other solution for:
> * permit navigation between fields;
> * querying the entire form by just entering bill_id;
> * increse the bill_id only on insert?
>
> Thank you for your patience ;)
>
> Regards,
> Dezo

First off, don't break the existing functinality. Only if you are at expert level should you even think about doing it yourself.

To query records you must use the form's built-in query capability so when it comes to entering an ID in the master table and selecting the detail record automatically ... the form already does that and you can not improve upon it.

To create a new record one approach would be to build the new record using a stored procedure int the database. Let the procedure select from the sequence and create the new record. Then pass back to the form the ID# as an OUT parameter. Then you can dynamically change the WHERE clause on the form to show that record after a query.

Hope this helps.

Daniel Morgan Received on Sun Jan 26 2003 - 19:15:42 CET