Logical lookups?

[Sandy Garrett]

We started with table 1(fields A, B, and C) and table 2 (fields A, B, and D). A new record in table 2 is created with a lookup linked on A looking up B. Worked a treat.

So I added field E, Logical, to both tables and to the lookup list. Whether E is T or F in table 1, it comes up T in table 2. This happens under A5v5, too. What did I do wrong?

[Tom Cone Jr]

RE: Logical lookups?

Sandy,

Sorry, I don't get the picture. Would need more details about the relationships between the tables, any and all sets involved, and their specifications... and the details of how and what you're 'looking up'. Using field rules? if so, which ? etc. and so forth.

Might be easier to zip a copy and post it here with a trail guide telling us where to look to see the problem.

-- tom

[Sandy Garrett]

RE: Logical lookups?

This one is actually too simple to viualize.

[Tom Cone Jr]

RE: Logical lookups?

Sandy,

the difficulty you're seeing results from the undefined logical values in your First table.

you're assuming an empty logical field is 'false', and 'taint necessarily so.

if you supply 'F' to each logical field in your First table that is currently blank, your lookup field rule on the Number field in Second table works jim dandy.

I see two solutions for this:

a) change data entry rules in First table to assure that the Logic field there is never empty; or

b) change the lookup field rule in Second Table to leave its Logic field blank if the lookup field in First table is also blank, otherwise fill in the lookup field value from First table.

-- tom

[Stan Mathews]

RE: Logical lookups?

Tom,

I'm certain you're aware, but for other interested parties...

Tha attached file is a single table "logcal". One field, logical named "yesno", three records, one with T, one with F, and one "uninitialized" or blank where the record has been created but nothing has been entered.

Consider the following from the interactive editor...

tbl = table.open("logcal")

tbl.fetch_first()
? tbl.recno()
= 1.000000
? tbl.yesno
= .T.

tbl.fetch_next()
? tbl.recno()
= 2.000000
? tbl.yesno
= .F.

tbl.fetch_next()
? tbl.recno()
= 3.000000
? tbl.yesno
= .F.

tbl.fetch_prev()
? tbl.recno()
= 2.000000
? isblank("tbl.yesno")
= .F.

tbl.fetch_next()
? tbl.recno()
= 3.000000
? isblank("tbl.yesno")
= .T.

tbl.fetch_goto(2)
test2 = tbl.YESNO
? test2
= .F.

tbl.fetch_goto(3)
test3 = tbl.YESNO
? test3
= .F.

? test2 = test3
= .T.

Sometimes logical ain't.

[Sandy Garrett]

RE: Logical lookups?

Actually I'm assuming the logical field can be True, False, or empty. The field tells if an item survived our flood. The item might be recovered (True) or trashed (False) or we haven't found it yet. New records default to True, but we just don't know what happened to much of the old stuff.

>> b) change the lookup field rule in Second Table to leave its Logic field blank if the lookup field in First table is also blank, otherwise fill in the lookup field value from First table.

[Tom Cone Jr]

RE: Logical lookups?

Sandy,

You're bending the envelope.

Logicals were probably designed to be meaningful in two conditions. True or False. You're trying to use one to be meaningful in three conditions. True, False, or Unknown. A simple solution is abandon logicals, and use a one char field instead. You could then design your own codes for the three conditions.

I'll take a crack at implementing the earlier suggestion, nevertheless.

-- tom

[Tom Cone Jr]

RE: Logical lookups?

Sandy,

I'm sorry. In the available time I couldn't implement my suggestion either! The lookup field rule won't accept expressions. I tried making the Logic field in Table Second a calculated field, but the lookup() function was stubbornly refusing to help me. I suspect Lookup() won't work when the search key has not yet been saved to the table. The solution would be to do this using form level code if you really really wanted to do it ths way.

A far easier solution would be abandoning the logical, and using codes stored in a char type field:

Yes
No
Unk

-- tom

[Sandy Garrett]

RE: Logical lookups?

Thank you so much for trying - and making me feel like less of an idiot.

I'll ask the data entry lady how she feels about a list lookup. The first table could have a choice between Floats and Sinks, and the second table could just import that.