Using LOV's in query-only mode

[Ian C. Sellers]

I'm really stuck! Please help.

I've made a F45 form with a text item that can call a LOV to populate
itself with a number corresponding to a subdivision name in another
table -- you pick the sub name from the list, and the form populates the
field with a representative number. It's all in query-only mode to this
point. The record group for the LOV is created by

SELECT lu_name, lu_num
INTO :lu_name, :asr_sub_number --(f.k. to lu_num - represents subdiv
name)
FROM lu_subs;

This all works fine - remember it's a query-only form - set at the block
properties level. The problem is, after you retrieve the LOV, pick one,
and execute the query, it populates the all the fields from the first
row of the queried group, then as the form leaves "query-only' mode
after the query it displays "Would you like to Commit to the changes you
have made?" I didn't change anything! Tell it "No" and everything is
fine.

I don't understand why this message appears - if you do tell it "Yes,"
to commit, it returns an error saying it can't insert record -- which is
logical as the form is restricted from inserting, updating, or deleting
in the block properties. After that it will continue to show this
"Would you like to Commit" message until I finally tell it "No."

How can I suppress this message? I'm guessing since I opened an
implicit cursor with the select into, that it assumes something was
changed. I can't rollback because Forms treats that as a clear_form --
and I would lose all my queried info. I don't want to commit to nothing
either - simply out of principle if nothing else!

TIA,
-Ian

[Peter H. Larsen]

Hi Ian,

It sounds like ither the :lu_name or :asr_sub_number is a base-table
field? First of all, a simple tip is to ALWAYS prefix your identifiers
with blocknames - you'll be praised when you later don't have to walk
through hundres of lines of code to find and fix ambigiously defined
items.

Next you'll want to look into your post-query trigger. That might be
populating forriegnkeys into base-table items. Or what is the most
common error -you have an when-validate-item trigger on a non-base-table
item that modifies basetable items when it fires. Non-base-table items
ALWAYS fire when changed - that includes QUERY-MODE. You usually want to
test for :SYSTEM.MODE != 'QUERY' in your when-validate-item when using
it on a non-base-table item.

The above situation is what usually causes updates of base-table items
in a query situation which results in you problem. I don't think it h as
to do with LOV if it happens when you QUERY data. My guess is, if you
query without using the LOV you get the same problem?

- Peter H. Larsen

[Ian C. Sellers]

Peter,

Thanks for the response. I'm clearly not explaining myself well, but
let me try once more and I'll keep it short. The problem as I see it is
with the Forms45 default query-by-example functionality.

Basically, Forms45 changes :system.form_status to 'CHANGED' when you
return an item from a LOV to a base-table item. Therefore, even if the
form is in 'QUERY-ONLY' mode, it executes the query, performs it
properly, and then, after it queries, it tries to commit the
LOV-returned item, although it's not allowed to. If you tell it "no" at
this point, it functions exactly the way I require, but I cannot
suppress this message, nor am I allowed to modify the value of
system.form_status in a post query trigger to prevent it attempting to
commit.

I don't have any funny triggers, and I've tried (by an earlier
suggestion) attaching the LOV to a non-base-table item, then setting a
default where clause for the block. This works, but is messy and would
force my users to include this field in every query, even if they didn't
want to include that as query-criteria.

And re: "My guess is, if you query without using the LOV you get the
same problem?", the answer is no. If you type a number in the
:building_permit.asr_sub_number item, I guess the form realizes that the
field is being populated in 'enter-query' mode solely for purposes of
finding matching data, and not to insert or change a row. It only tries
to commit if you return the number to that item from the LOV record
group.

Thanks for your brain-energy.

-Ian

[Ian C. Sellers]

[Steve Johnson]

When you use the LOV in 'ENTER-QUERY' mode it will not change the
:SYSTEM.FORM_STATUS. What's probably changing the :SYSTEM.FORM_STATUS
is a column getting populated or changed in a trigger. Usually
a POST_QUERY trigger.

Steve

[Ian C. Sellers]

--------------F441645E587DF8EF3CFAE883
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Steve,

It's not the LOV as such, but_it_is_the record group created by the LOV
and the 'select into' statement it generates to return items from the
LOV to text items (for criteria on which to query).

If you tell it "Yes", to save the changes, here's the statement it
generates after executing the query (gleaned from 'display message' at
runtime):
INSERT INTO LU_SUBS(LU_NUM) VALUES (:LU_NUM)

I have double and triple checked my triggers, even so far as to include
message('This trigger fired') statements in them so I'd be absolutely
certain when they were firing, and none fire at this point!

It had a POST-QUERY trigger (since removed) only to confirm that
:SYSTEM.FORM_STATUS is being set to "CHANGED" after (or perhaps before)
completion of the query.

Again, thanks.

-Ian

Steve Johnson wrote:

> When you use the LOV in 'ENTER-QUERY' mode it will not change the
> :SYSTEM.FORM_STATUS. What's probably changing the :SYSTEM.FORM_STATUS
>
> is a column getting populated or changed in a trigger. Usually
> a POST_QUERY trigger.
>
> Steve
>

--------------F441645E587DF8EF3CFAE883
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<HTML>
Steve,

<P>It's not the LOV as such, but_it_is_the record group created by the
LOV and the <U>'select into'</U> statement it generates to <U>return items</U>
from the LOV to text items (for criteria on which to query).

<P>If you tell it "Yes", to save the changes, here's the statement it generates
after executing the query (gleaned from 'display message' at runtime):
<BR>INSERT INTO LU_SUBS(LU_NUM) VALUES (:LU_NUM)

<P>I have double and triple checked my triggers, even so far as to include
message('This trigger fired') statements in them so I'd be absolutely certain
when they were firing, and none fire at this point!

<P>It had a POST-QUERY trigger (since removed) only to confirm that :SYSTEM.FORM_STATUS
is being set to "CHANGED" after (or perhaps before) completion of the query.

<P>Again, thanks.

<P>-Ian

<P>Steve Johnson wrote:
<BLOCKQUOTE TYPE=CITE>When you use the LOV in 'ENTER-QUERY' mode it will
not change the
<BR>:SYSTEM.FORM_STATUS.&nbsp; What's probably changing the :SYSTEM.FORM_STATUS
<BR>is a column getting populated or changed in a trigger.&nbsp; Usually
<BR>a POST_QUERY trigger.

<P>Steve
<BR></BLOCKQUOTE>
</HTML>

--------------F441645E587DF8EF3CFAE883--

[Peter H. Larsen]

Ian C. Sellers wrote:

Hi Ian,
Sorry for replying so late, I havn't had access to newsgroups for a few
days. I'm cutting down our thread qoute for simplicity sake.

I'm not sure I understand what it is you're trying to do. What you have
is a code and a text. The base table contains the code and you have a
lookup table that has the text? You then want LOV to return the text?

1. Are you using redundant entry of the text field, so it's represented
both in the lookup table and the base table? If so, there is your
problem.
2. The code is the base-table item and the text is not. Your LOV returns
the code to the base-field and text to the non-base field?
3. LOV has nothing to do with Query. It's to be executed manually by the
user. So I ask again, if you don't use the LOV when you query, do you
have the same problems?
4. Are you refering your childblock from your master block with an
assignment? If so, that's a NO-NO. (the other way around it's okay).

Your usual standard design calls for a post-query and when-validate-item
triggers construct to synchronize your code to the text field, either by
query or by entry. But as you don't use entry (QUERY only form) I'm not
sure what it IS you're doing?

Or is the case like this:

master-block with LOV
detail block that queries depended on master-block

if so, why is the master-block a base-table block? You don't need a
base-table item to condition your details block. You can either user
WHERE or just the COPY VALUE FROM property just as you pointed to a
base-field item. It will join the master and detail block together
without problems.

In short: If your query is controlled by a user-entered value, that is
NOT done in ENTER-QUERY mode you use a non-basetable block. You can
assign values in this block using QUERY-ONLY mode. And you can refer to
non-basetable items in master/detail forms just as easy as if it was an
base-table item. There is no difference in that regard.

If you're not using WHERE clauses now to group your condition LOV with
the details block what are you doing that's not possible with
non-basetable items?

Hope this help - otherwise email me your basic formstructure and let me
have a look at it again (not the fmb file, just a summery of the
construction).

- Peter H. Larsen