At 07:56 PM 9/03/2005 +0000, you wrote:

>Hi,
>
>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).

Correct, that's because the TTable implements a table structure, not a
query specification (even though, for SQL databases, it's a SELECT * query!!)

>So if I need to use a JOIN in the
>future, then I should use TIBOQuery and lose the Master-Detail features.

Why? TIBOQuery inherits the TQuery implementation of master-detail. Thus,
like TQuery, it doesn't expose explicit MasterFields linking. This means
that the linking columns in the two output sets must be of exactly the same
type and have the same field names in the database; and the Details set
must be designed so that the column[s] that link[s] to the Deal set's
KeyLinks (for the masterlinking) are parameters.

Or perhaps you were unaware that you link your Details set to your Deal set
through the Datasource property of the Details set?

>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.

That sounds like an occasion when cached updates could be used.....However,
since all posting is done at the level of the current row, you can always
intervene in the BeforePost event of either (or both) dataset[s] to do this
kind validation. The key thing here, where you have inter-row
dependencies, is NOT to use an AutoCommit transaction.

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

Certainly. That should always be your rule in cases where you are relying
on the client code to validate the input set. Mind you, IMO, relying on
client code to do this is not a good design practice, since it totally
binds the integrity of your data to the application....and, if you have
another application (maybe even in another language, which can't share your
classes) you have to repeat the same validation.

>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?

You don't have the linking set up.
1. Set the detail's Datasource property to the datasource of the deal
structure.
2. Parameterise the detail set's SQL - ..."where master_id =
:master_id". The hidden implicit linking will take care of writing the
master_id to the detail set when you call Insert on the detail set.

>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?

This needs to be explained more clearly. A calculated field on the client
side doesn't exist in the database. It sounds as though you need some help
constructing the detail query so you can get your head around the binding
between the output set and the underlying table.

>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?

Keep it firmly in mind that any DML methods of the detail set pertain to
the current row. Put your validation code in the BeforePost event, because
that is the last place where your code can intervene in what is posted to
the database for that row. Write a set of methods that each performs a
validation check and roll these into a "master method" that you call from
BeforePost.

The methods that have to check the whole set will be pretty ugly, because
they will have to iterate through the rows of the detail set....maintain a
structure (TStrings usually) to hold the linkages between the detail set's
Keylinks and the sequence number. If the logic permits, call the set-level
methods after the row-level validations are done.

>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.

Then you certainly want to cache the detail set. I think you will have to
search for a third-party grid or tree control to get the UI you want, since
the VCL's TDBGrid doesn't have the capability you want, AFAIR.

>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.

Try to keep it simple, at least to begin with. For example, get the
updating and caching working before you start trying to do dramatic things
with the UI....be clear in your head what has to happen in the database
once you post a detail row (or call ApplyUpdates for the cache).

Be aware that you can't bind client-side calculated fields to database
columns. If either of the sets has a column that depends on the outcome of
client-side calculations then you'll need to design a way to bind the
calculated values to the value that is posted for the database column --
often a custom XxxxSQL method that invokes an executable SP with
parameterised arguments will be the right way to do this.

Helen