Subject Master-Detail dillemas with TIBO*
Author Eyal

I'm trying to figure out what is the best approach to implement an
input/edit form which has to handle master-detail data. I used the
TIBO* components for compatability with 3rd party components. Here is
a brief (as can be) description of the situation.

I need to process financial transactions. In order to avoid confusion
with DB transactions I will use the term "deal". Each deal consists of
one master row (in the HEADER table) and detail rows (in the LINES table).

A master row contains data such as the main date of the deal and a few
more fields, all of which are hidden from the user and filled with
computed values by the application.

A detail row contains a link (foreign key) to the master/hedaer row,
several date fields, several text fields, an account ID (lookup on
ACCOUNT table), and of course the amount. The user inputs all data for
each detail row.

Each deal is made up of exactly 1 master row, and at least detail
rows. The total (balance) of all detail rows in a transaction must be 0.

Questions and Problems:

1. Which should I use TIBOTable or TIBOQuery? TIBOTable has support
for Master-Detail, but Borland docs claim it's for use only for single
table queries (ie. no JOINs). So if I need to use a JOIN in the
future, then I should use TIBOQuery and lose the Master-Detail features.

2. Should I use cached updates? Before posting a deal to the DB I must
check that the data is valid, eg. that the balance of all detail rows
is exactly 0, that the same account doesn't show up in two different
rows, etc.

3. Should I take control of the transaction? I must avoid, for
example, a deal with a master row but no detail rows.

4. When I try to input a new detail row I get an error box that
compains "missing value for MASTER_ID". I guess this is because
MASTER_ID is defined in the DB as "Not Null". But when entering detail
rows for a new deal, there isn't yet any master row. So which value
should go into DETAIL.MASTER_ID?

5. There are additional "missing value" messages for other fields.
Those are calculated fields, which can be calculated only after the
user inputs all data (ie. user click OK). How can I tell TIBODataset
to "be patient" with those fields until I actually try to post?

6. I need to maintain sequential row number withing each deal. I want
this to be automatic without any user intervention. It looks like the
only way is to maintain that column in the dataset using events
(AfterInsert, AfterDelete). Is there a better way?

7. I want to let the user move detail rows relative to each other.
Leaving the GUI drag&drop implementation aside, how do I re-order the
rows in the dataset? For a new deal the rows are not yet in the DB, so
a re-query with ORDER BY is irrelevant. For editing existing deal, I
don't want to post/query for every row move, because the user may play
around with the rows, so it's better to just post once in the end.

I think that's it for now. I guess I'll have more when I try different
solutions. So far I only dealt with reading queries and mostly it went
straighforward, even the most complex queries. But now that I have to
do inserts/edits for a master-detail data, which also has many twists
and conditions, I feel that I'm losing my way.

Many Thanks for any help,