Using dbase plus 2.5, a weird behavior has developed with a field which has
a lookupsql statement. A combobox datalinked to this field, or the same
field in a grid, will only accept one of the lookupsql values...if you
choose any of the others from the drop down list (4 items total), the field
goes blank as soon as you leave it.

This has been working normally for quite a while and I can't figure out what
has changed that would cause this behavior. I have tried reindexing both
tables, copying them to new tables, packing them, etc to no avail.

I have tested now with a new form with only the one table on it, with a
datalinked grid, and with a datalinked combobox, and the behavior persists.

Any help or direction would be appreciated.

David Madison

From:	Todd Kreuter [dBVIPS]
Subject:	Re: Odd LookupSql behavior
Date:	Sat, 15 Jan 2005 08:34:09 -0500

David Madison wrote:
>
> This has been working normally for quite a while and I can't figure out what
> has changed that would cause this behavior. I have tried reindexing both
> tables, copying them to new tables, packing them, etc to no avail.

There was a problem where if you changed the length of the display field
via the table designer the reverse lookup would fail, but that has been
fixed. The value that does work, how is it different than the others, is
it longer in length?

--
Todd Kreuter [dBVIPS]

From:	David Madison
Subject:	Re: Odd LookupSql behavior
Date:	Sat, 15 Jan 2005 07:21:56 -0800

Yes, it is longer in length, ten letters instead of 5,7, or 8 letters (there
are only four choices). The one that sticks starts with the letter "A" but
none of the others do....(Assignment, Stock, General, Research are the
display items; A,S,G,R are the stored values)

David

"Todd Kreuter [dBVIPS]" wrote in message
news:41E91BD1.B8B26D5C@dbvips.usa...
> David Madison wrote:
>>
>> This has been working normally for quite a while and I can't figure out
>> what
>> has changed that would cause this behavior. I have tried reindexing both
>> tables, copying them to new tables, packing them, etc to no avail.
>
> There was a problem where if you changed the length of the display field
> via the table designer the reverse lookup would fail, but that has been
> fixed. The value that does work, how is it different than the others, is
> it longer in length?
>
> --
> Todd Kreuter [dBVIPS]

From:	Todd Kreuter [dBVIPS]
Subject:	Re: Odd LookupSql behavior
Date:	Sat, 15 Jan 2005 10:39:57 -0500

David Madison wrote:
>
> Yes, it is longer in length, ten letters instead of 5,7, or 8 letters (there
> are only four choices). The one that sticks starts with the letter "A" but
> none of the others do....(Assignment, Stock, General, Research are the
> display items; A,S,G,R are the stored values)

Is the length of the field 10?

Try the following from the command window:

USE
replace all with + SPACE(30)
USE

Substitute

with your table name and with the display
field name (the 2nd field in your lookupSql field list)

Then try your form again.

--
Todd Kreuter [dBVIPS]

From:	David Madison
Subject:	Re: Odd LookupSql behavior
Date:	Sat, 15 Jan 2005 08:22:40 -0800

Hi Todd

> Is the length of the field 10?
>
> Try the following from the command window:
>
> USE
> replace all with + SPACE(30)
> USE
>
> Substitute

with your table name and with the display
> field name (the 2nd field in your lookupSql field list)
>
> Then try your form again.
>

Yes, the field length is ten, the length of the longest display word.

I tried your suggestion above and it makes no difference...I did this on
both tables, not just the lookup table even though I think you meant just
the lookup table. I then created a new test form with a grid and a combobox,
the value now sticks in the combobox but is not written back to the table;
in the grid the value does not stick (unless the value is "Assignment"). If
the value is "Assignment" in either control, the correct value "A" is
written into the table.

???

David

From:	Todd Kreuter [dBVIPS]
Subject:	Re: Odd LookupSql behavior
Date:	Sat, 15 Jan 2005 12:13:03 -0500

David Madison wrote:
>
> Yes, the field length is ten, the length of the longest display word.
>
> I tried your suggestion above and it makes no difference.

Just to make sure... you are setting either a lookupSql or lookupRowset
to a field object. The combobox is datalinked to that field object. You
are not setting the combobox dataSource (its automatically getting that
from the lookupSql/Rowset assignment). Running the form and changing the
combobox value, the value is not sticking. Anything else you are doing?

I want to check something using the combobox onChange event.

function combobox1_onChange
? this.value + "|", this.dataLink.lookupRowset.locateOptions

You should see the value padded by some spaces then the "|" for values
with a length less than 10. If the values are trimmed (not padded) then
the locateOptions needs to be 3 inorder for the reverse lookup to work
properly.

--
Todd Kreuter [dBVIPS]

From:	David Madison
Subject:	Re: Odd LookupSql behavior
Date:	Sat, 15 Jan 2005 16:30:29 -0800

Hi Todd,

> Just to make sure... you are setting either a lookupSql or lookupRowset
> to a field object. The combobox is datalinked to that field object. You
> are not setting the combobox dataSource (its automatically getting that
> from the lookupSql/Rowset assignment). Running the form and changing the
> combobox value, the value is not sticking. Anything else you are doing?

I think I have this right, here's the code for the test form:

** END HEADER -- do not remove this line
//
// Generated on 01/15/2005
//
parameter bModal
local f
f = new Test_InvForm()
if (bModal)
f.mdi = false // ensure not MDI
f.readModal()
else
f.open()
endif

class Test_InvForm of FORM
set procedure to :classes:custbutt.cc additive
with (this)
height = 42.3182
left = 12.7143
top = -0.8636
width = 183.4286
text = ""
endwith

this.STOCKDBFS1 = new DATABASE()
this.STOCKDBFS1.parent = this
with (this.STOCKDBFS1)
left = 100.0
top = 10.5
databaseName = "STOCKDBFS"
active = true
endwith

this.INVOICE1 = new QUERY()
this.INVOICE1.parent = this
with (this.INVOICE1)
left = 106.0
top = 14.0
database = form.stockdbfs1
sql = "select * from invoice.dbf"
active = true
endwith

with (this.INVOICE1.rowset)
fields["INV_TYPE"].lookupSQL = 'select INV_TYPE, DISPLAY as
INVOICETYPE from "INV_TYPE.dbf"'
endwith

this.GRID1 = new GRID(this)
with (this.GRID1)
dataLink = form.invoice1.rowset
height = 4.0
left = 12.0
top = 4.0
width = 99.0
endwith

this.COMBOBOX1 = new COMBOBOX(this)
with (this.COMBOBOX1)
onChange = class::COMBOBOX1_ONCHANGE
dataLink = form.invoice1.rowset.fields["inv_type"]
height = 1.0
left = 25.0
top = 13.5
width = 49.0
style = 1 // DropDown
endwith

this.KENSTOOLBAR1 = new KENSTOOLBAR(this)
with (this.KENSTOOLBAR1)
left = 18.0
top = 18.0
width = 61.1429
height = 1.3636
endwith

this.rowset = this.invoice1.rowset

function COMBOBOX1_onChange
? this.value+"|",this.dataLink.lookupRowset.locateOptions
return

endclass

> I want to check something using the combobox onChange event.
>
> function combobox1_onChange
> ? this.value + "|", this.dataLink.lookupRowset.locateOptions
>
> You should see the value padded by some spaces then the "|" for values
> with a length less than 10. If the values are trimmed (not padded) then
> the locateOptions needs to be 3 inorder for the reverse lookup to work
> properly.
>

The values appear to be padded as there is some space to the right of the
shorter values, though the "|" isn't visible.

I tried changing the locateOptions for the rowset to 3, and to 0, but no
success.

Still baffled....

David

From:	Rafael Carrazco
Subject:	Re: Odd LookupSql behavior
Date:	Sat, 15 Jan 2005 23:10:22 -0700

Hi David,
could you please list the Invoice type? I think this problem is because of
the descriptions (display field) , try changing the description of the
invoice type that its not being displayed , for example the first
characters. If it does not work I can help you if you post your tables
(invoice.dbf and INV_TYPE.dbf) in the binaries.
Rafa

"David Madison" wrote in message
news:K8YXYL2%23EHA.876@news-server...
> Hi Todd,
>
> > Just to make sure... you are setting either a lookupSql or lookupRowset
> > to a field object. The combobox is datalinked to that field object. You
> > are not setting the combobox dataSource (its automatically getting that
> > from the lookupSql/Rowset assignment). Running the form and changing the
> > combobox value, the value is not sticking. Anything else you are doing?
>
> I think I have this right, here's the code for the test form:
>
>
> ** END HEADER -- do not remove this line
> //
> // Generated on 01/15/2005
> //
> parameter bModal
> local f
> f = new Test_InvForm()
> if (bModal)
> f.mdi = false // ensure not MDI
> f.readModal()
> else
> f.open()
> endif
>
> class Test_InvForm of FORM
> set procedure to :classes:custbutt.cc additive
> with (this)
> height = 42.3182
> left = 12.7143
> top = -0.8636
> width = 183.4286
> text = ""
> endwith
>
> this.STOCKDBFS1 = new DATABASE()
> this.STOCKDBFS1.parent = this
> with (this.STOCKDBFS1)
> left = 100.0
> top = 10.5
> databaseName = "STOCKDBFS"
> active = true
> endwith
>
> this.INVOICE1 = new QUERY()
> this.INVOICE1.parent = this
> with (this.INVOICE1)
> left = 106.0
> top = 14.0
> database = form.stockdbfs1
> sql = "select * from invoice.dbf"
> active = true
> endwith
>
> with (this.INVOICE1.rowset)
> fields["INV_TYPE"].lookupSQL = 'select INV_TYPE, DISPLAY as
> INVOICETYPE from "INV_TYPE.dbf"'
> endwith
>
> this.GRID1 = new GRID(this)
> with (this.GRID1)
> dataLink = form.invoice1.rowset
> height = 4.0
> left = 12.0
> top = 4.0
> width = 99.0
> endwith
>
> this.COMBOBOX1 = new COMBOBOX(this)
> with (this.COMBOBOX1)
> onChange = class::COMBOBOX1_ONCHANGE
> dataLink = form.invoice1.rowset.fields["inv_type"]
> height = 1.0
> left = 25.0
> top = 13.5
> width = 49.0
> style = 1 // DropDown
> endwith
>
> this.KENSTOOLBAR1 = new KENSTOOLBAR(this)
> with (this.KENSTOOLBAR1)
> left = 18.0
> top = 18.0
> width = 61.1429
> height = 1.3636
> endwith
>
> this.rowset = this.invoice1.rowset
>
> function COMBOBOX1_onChange
> ? this.value+"|",this.dataLink.lookupRowset.locateOptions
> return
>
> endclass
>
>
>
> > I want to check something using the combobox onChange event.
> >
> > function combobox1_onChange
> > ? this.value + "|", this.dataLink.lookupRowset.locateOptions
> >
> > You should see the value padded by some spaces then the "|" for values
> > with a length less than 10. If the values are trimmed (not padded) then
> > the locateOptions needs to be 3 inorder for the reverse lookup to work
> > properly.
> >
>
> The values appear to be padded as there is some space to the right of the
> shorter values, though the "|" isn't visible.
>
> I tried changing the locateOptions for the rowset to 3, and to 0, but no
> success.
>
> Still baffled....
>
> David
>
>
>
>
>

From:	David Madison
Subject:	Re: Odd LookupSql behavior
Date:	Sun, 16 Jan 2005 12:59:24 -0800

Hi Rafael

> could you please list the Invoice type? I think this problem is because of
> the descriptions (display field) , try changing the description of the
> invoice type that its not being displayed , for example the first
> characters.

The invoice type display field has four values:

"Stock"
"Assignment"
"Research"
"General"

The field length is 10, the length of the longest word "Assignment".

The other field has a length of 1, and is populated with the letters "S",
"A" and so on.

This is the field that is supposed to be stored back into the invoice file
in an identical field.

As per your suggestion, I tested changing the first letter in the display
field values but that did not work ("xStock","xAssignment", etc).

What did work is this: I added letters to the end of each value until the
length reached 10, like "StockXXXXX". This works!

When I add only spaces, as Todd suggested, it does not work.

I tried changing the rowset locateOptions to all of the possible choices,
and that did not help with the original data.

So...it has something to do with field value length??????????? Is there a
new bug here, this technique always used to work fine without any
complexities?

David

From:	Rafael Carrazco
Subject:	Re: Odd LookupSql behavior
Date:	Sun, 16 Jan 2005 19:56:43 -0700

Hi Davison,
you are right,Its about lenghts as far as I can see.

Rafa

"David Madison" wrote in message
news:7wiYI6A$EHA.1128@news-server...
> Hi Rafael
>
> > could you please list the Invoice type? I think this problem is because
of
> > the descriptions (display field) , try changing the description of the
> > invoice type that its not being displayed , for example the first
> > characters.
>
> The invoice type display field has four values:
>
> "Stock"
> "Assignment"
> "Research"
> "General"
>
> The field length is 10, the length of the longest word "Assignment".
>
> The other field has a length of 1, and is populated with the letters "S",
> "A" and so on.
>
> This is the field that is supposed to be stored back into the invoice file
> in an identical field.
>
>
> As per your suggestion, I tested changing the first letter in the display
> field values but that did not work ("xStock","xAssignment", etc).
>
> What did work is this: I added letters to the end of each value until the
> length reached 10, like "StockXXXXX". This works!
>
> When I add only spaces, as Todd suggested, it does not work.
>
> I tried changing the rowset locateOptions to all of the possible choices,
> and that did not help with the original data.
>
> So...it has something to do with field value length??????????? Is there a
> new bug here, this technique always used to work fine without any
> complexities?
>
> David
>
>

From:	Todd Kreuter [dBVIPS]
Subject:	Re: Odd LookupSql behavior
Date:	Sun, 16 Jan 2005 20:30:25 -0500

"David Madison" wrote in message
news:K8YXYL2%23EHA.876@news-server...
>>
> > You should see the value padded by some spaces then the "|" for values
> > with a length less than 10. If the values are trimmed (not padded) then
> > the locateOptions needs to be 3 inorder for the reverse lookup to work
> > properly.
>
> The values appear to be padded as there is some space to the right of the
> shorter values, though the "|" isn't visible.

That is odd. Have you tried creating a new table from scratch?

Todd Kreuter [dBVIPS]

From:	David Madison
Subject:	Re: Odd LookupSql behavior
Date:	Mon, 17 Jan 2005 08:03:37 -0800

HI Todd,

I just tried creating new tables, but no difference.

What does work is when the display field values are all made to be the same
length as the field width by padding the ends with something:

"Stockxxxxx"
"Researchxx"
"Assignment"

Not sure what else to do for now!

David

From:	Todd Kreuter [dBVIPS]
Subject:	Re: Odd LookupSql behavior
Date:	Mon, 17 Jan 2005 12:37:37 -0500

David Madison wrote:
>
> I just tried creating new tables, but no difference.

Can you zip up the 2 tables (or test tables) and post them to the
binaries ng?

--
Todd Kreuter [dBVIPS]

From:	David Madison
Subject:	Re: Odd LookupSql behavior
Date:	Mon, 17 Jan 2005 11:32:45 -0800

Will do, posting them as "Files for Todd Kreuter"

Thanks very much

David

"Todd Kreuter [dBVIPS]" wrote in message
news:41EBF7E1.D5DE9325@dbvips.usa...
> David Madison wrote:
>>
>> I just tried creating new tables, but no difference.
>
> Can you zip up the 2 tables (or test tables) and post them to the
> binaries ng?
>
>
> --
> Todd Kreuter [dBVIPS]

From:	Todd Kreuter [dBVIPS]
Subject:	Re: Odd LookupSql behavior
Date:	Mon, 17 Jan 2005 15:10:13 -0500

David Madison wrote:
>
> Will do, posting them as "Files for Todd Kreuter"

It does not appear to be the tables. Works fine for me doing the
following:

Drop the invoice table on the form.
Set the inv_type field lookupsql to "Select Inv_type, Display from
Inv_type".
Drop a stock combobox control, datalink the inv_type field.
Running the form and changing the value to any option, the change is
saved to the rowset.

Are you by chance using some custom combobox? I don't know what else it
could be?

--
Todd Kreuter [dBVIPS]

From:	David Madison
Subject:	Re: Odd LookupSql behavior
Date:	Mon, 17 Jan 2005 16:05:10 -0800

Todd,

the only difference I can find is in the lookupSql statements:

yours:

> Set the inv_type field lookupsql to "Select Inv_type, Display from
> Inv_type".

here's what I was using

fields["INV_TYPE"].lookupSQL = "select INV_TYPE, DISPLAY as
INVOICE_TYPE from INV_TYPE.dbf"

the difference is I was adding the "as INVOICE_TYPE" part to change the
displayed field title. When I remove this, things are fine.
When I put in "as XXX" it fails again....so there must be something about
the "as" clause.

This is puzzling since I have been using this type of "AS" clause for
several years without problems. Is this no longer supported by the current
version?

Thanks very much for helping me!

David

From:	Todd Kreuter [dBVIPS]
Subject:	Re: Odd LookupSql behavior
Date:	Tue, 18 Jan 2005 09:35:11 -0500

David Madison wrote:
>
> the difference is I was adding the "as INVOICE_TYPE" part to change the
> displayed field title. When I remove this, things are fine.
> When I put in "as XXX" it fails again....so there must be something about
> the "as" clause.
>
> This is puzzling since I have been using this type of "AS" clause for
> several years without problems. Is this no longer supported by the current
> version?

Oh, I see.

I would guess that where it works, the sql statement is generating a
temporary table and the AS field name becomes a real field in that
table. In this case, no temporary table is created and the AS field name
is not a real field in the table. For whatever reason this is causing
the process to fail when the string length <> the field length.

In any case, when using AS you can set lookupRowset locateOptions to 3
and it should work (did for me).

function form_onOpen
this.combobox1.datalink.lookupRowset.locateOptions = 3

--
Todd Kreuter [dBVIPS]

From:	David Madison
Subject:	Re: Odd LookupSql behavior
Date:	Tue, 18 Jan 2005 15:04:12 -0800

I guess I was probably mis-using the AS statement in this case...looking at
my other uses of it, sometimes it is to create a new field (Whole Name from
First Name and Last Name) and sometime I adopted that method simply to
display a new field title on the existing field, with the unintentional
results that I encountered.

Do you think there is a bug here when the string length <> the field length,
or is this just not an appropriate use of "as"?

David

From:	Todd Kreuter [dBVIPS]
Subject:	Re: Odd LookupSql behavior
Date:	Tue, 18 Jan 2005 21:27:54 -0500

"David Madison" wrote in message
news:uapNDJb$EHA.1428@news-server...
>
> Do you think there is a bug here when the string length <> the field
length,
> or is this just not an appropriate use of "as"?

I would think it a bug before anything else.

Did see my comment about setting locateOptions to get it working?

Todd Kreuter [dBVIPS]

From:	David Madison
Subject:	Re: Odd LookupSql behavior
Date:	Wed, 19 Jan 2005 09:42:34 -0800

Todd,

One more question on this...how to use the locateOptions --

When I tested it, I set the locateOptions in the datamodule for the primary
rowset.

Your code that works was:

function form_onOpen
this.combobox1.datalink.lookupRowset.locateOptions = 3

Is this doing the same thing as setting it in the dmd, or is this setting
locateOptions on the lookup rowset rather than the primary rowset? If so
that is why my testing was failing.

David

From:	Todd Kreuter [dBVIPS]
Subject:	Re: Odd LookupSql behavior
Date:	Wed, 19 Jan 2005 13:22:03 -0500

David Madison wrote:
>
> One more question on this...how to use the locateOptions --
>
> When I tested it, I set the locateOptions in the datamodule for the primary
> rowset.
>
> Your code that works was:
>
> function form_onOpen
> this.combobox1.datalink.lookupRowset.locateOptions = 3
>
> Is this doing the same thing as setting it in the dmd, or is this setting
> locateOptions on the lookup rowset rather than the primary rowset? If so
> that is why my testing was failing.

The setting should be for the property of the lookupRowset. Sorry if I
was not clear on that before ;-(

--
Todd Kreuter [dBVIPS]

From:	David Madison
Subject:	Re: Odd LookupSql behavior
Date:	Wed, 19 Jan 2005 08:34:38 -0800

Hi Todd,

> Did see my comment about setting locateOptions to get it working?

Yes, I just tested this setting as you showed how and it does fix the
problem. I had thought I tested it earlier, but perhaps wasn't doing it
correcfly.

Thanks again for all your help on this.

David Madison

From:	Jim Sare [dBVIPS]
Subject:	Re: Odd LookupSql behavior
Date:	Sun, 16 Jan 2005 16:39:47 -0500

David,

> This has been working normally for quite a while and I can't figure out what
> has changed that would cause this behavior. I have tried reindexing both
> tables, copying them to new tables, packing them, etc to no avail.
>
> I have tested now with a new form with only the one table on it, with a
> datalinked grid, and with a datalinked combobox, and the behavior persists.

Any chance that the tables were level 3, 4, or 5 when it worked, and
then you opened them in the Table Designer to make structure changes,
and now they are level 7 tables and that's when the problem started to
occur?

In level 7 tables, the extra unused characters in a character field
don't automatically get padded with spaces to fill the width of the
field as they do in level 3, 4, and 5.

Hope it helps.
--
Jim Sare - [dBVIPS]
http://www.jimsare.com

From:	David Madison
Subject:	Re: Odd LookupSql behavior
Date:	Sun, 16 Jan 2005 16:51:01 -0800

Jim,

> Any chance that the tables were level 3, 4, or 5 when it worked, and then
> you opened them in the Table Designer to make structure changes, and now
> they are level 7 tables and that's when the problem started to occur?

I'll have to consider this, it is possible since the original tables are
from my dbase DOS app that I am slowly migrating over.

Is there a way to tell what level a table is?

> In level 7 tables, the extra unused characters in a character field don't
> automatically get padded with spaces to fill the width of the field as
> they do in level 3, 4, and 5.
>

Then in level 7, are there extra steps necessary to make the lookupSQL work
correctly?

David