At 03:24 AM 19-04-02 +0000, E Handbury wrote:

> Sorry for being confusing. This is not a master-detail. Here's the
>SQL below.
>
>select
> S.DESCRIPTION,S.STEP,S.DURATION,S.AUTOCOMPLETE,A.ACTIVITYALERTKEY,
> A.MASTERITEMKEY,A.ACTIVITYKEY,A.CLIENTKEY,A.USERKEY,A.ATYPE,
> A.ADATE,A.ATIME,A.STATUS,A.DUEDATE,A.COMPDATE,A.COMPTIME,
> A.COMPUSERKEY,A.NOTES
>from MBACTIVITYALERT A LEFT JOIN MBSTEPITEM S ON
> S.STEP=A.MASTERITEMKEY where ATYPE=1
> ORDER BY ACTIVITYALERTKEY DESC
>
> The problem is that MASTERITEMKEY is set in the OnInsert event, and
>therefore the S.DESCRIPTION column (and all S.* columns) don't get
>refreshed until the MBACTIVITYALERT dataset is refreshed/posted. I
>need the MBSTEPITEM columns to be refreshed while MBACTIVITYALERT is
>still in dssInsert. I am using TIB_Query. Thanks.

There is no possible way that the client app can do this. ...
1. Until the new MasterItemKey is committed to the database, there is no
way that any table on the server can know about it, either.
2. The client is looking at a set that represents a relationship between
existing rows in two table. When you create a new row in one table, as
long as the dataset is in dssInsert state you won't even be able to *form*
a relationship between it and existing rows in the other table. The reason
is, those new data for table A exist nowhere except in the client
buffer. AFTER you have posted and committed that new row, the set can be
refreshed and, if there is a correspondent S.Step to this new
MasterKeyItem, it will show up in the refreshed set. By this time, of
course, the dataset state will have passed from dssInsert to dssBrowse and
thence to whatever state you stipulate for it to open in (a Refresh for a
joined set is inevitably a close followed by an open because there is no
way the client can get hold of an "all-new" output row except by getting
the server to produce it. "Close" and "Open" are client-side activities
that don't have any sort of equivalent action on the server...they cause
the server to replace the output set by running the same query again
following the committing of new data.

It isn't clear what you are expecting to happen in the database as a result
of this insert operation on the joined dataset. The most that will happen
here is that a new row gets created in A (assuming that you set A as the
KeyRelation). If you want other things to happen (such as creation of a
corresponding row in S) you will have to do all these things in a stored
procedure.

So, without knowing what your expectations/requirements are, you could work
this in a way similar to how Jason manages master/detail: have your
Insert() call do the following:
1) get the new MasterItemKey (an unsafe thing if this is being created on
the client, but that's another chapter)
2) call a SP (through the InsertSQL property of the query) that accepts the
new key as input, goes to the server, creates a new row for A and, if
needed, a new row for S.
3) Post the Insert so that the SP gets executed; and commit it with
CommitRetaining
4) Refresh the query with RefreshAction set to raKeepRowNum and place it in
dssEdit - which will land you back on the new set, provided your keylinks
are adequate to that task
5) Take user inputs into cols from both tables and pass them as input
parameters to a SP in your query's EditSQL property that will update both
tables.

Still, this is only a projection based on guesswork as to what you need to
do...


regards,
Helen Borrie (TeamIBO Support)

** Please don't email your support questions privately **
Ask on the list and everyone benefits
Don't forget the IB Objects online FAQ - link from any page at
www.ibobjects.com