Getting Started with dBASE

Author

lookup sql

Keith Colombo

2006-01-25, 3:26 am

I have been working way too long on trying to get an entryfield to update when using a lookup sql. Thank you in advance for the help.

Using a single table with fields SesNo and StartDate. SesNoEntryfield on form is datalinked to table fiield SesNo. Lookup SQL for the SesNoCombobox = select SesNo, SesNo, StartDate from Sessions. StartDateEntryfield datalink = form.sessions1.rowset.fi
elds["StartDate"].

I don't know how to get the StartDateEntryfield to reflect the rowset's StartDate when SesNo is changed in the lookup drop down combobox. Can you help?

I know that the lookupRowset is a property of the lookup query field. I think there are two rowsets at work here; one for the form.session1 and the other the lookup query. I think I need to assign the form.StartDateEntryfield.value property to the lookup

rowset.field["StartDate"].value property in the onChange event of the SesNoCombobox.

I started with dBase III in 1987 and then dBase iV in '88. This is my first attempt at OODML with 2.6. I was amazed at the capabilities of dBase then and am blown away by them now, i.e. Contax example.

I noticed in the tips.zip file, lkup.wfm addressed this issue, however, I no longer have the mugs database files. There used to be an archive newsgroup that had alot of these questions. Where can I get the mugs database files?

Thanks again.

Keith

Michael Nuwer [dBVIPS]

2006-01-26, 4:54 pm

I'm not altogether sure I understand what you trying to do. Assuming
that the combo box and the entry field are both datalinked to the same
field in the same query object, then a change in the combo box will not
be updated in the entry field until the rowset.save() method is
executed. If you do not want to execute the save() method you could
manually set the entry field value:

form.entryfield1.value=this.value

The following form is a model of what I understand you are asking and a
possible answer.

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

class demotestForm of FORM
with (this)
height = 16.0
left = 44.7143
top = 4.8636
width = 40.0
text = ""
endwith

this.DBASETUTORIAL1 = new DATABASE()
this.DBASETUTORIAL1.parent = this
with (this.DBASETUTORIAL1)
left = 9.0
top = 9.5
databaseName = "DBASETUTORIAL"
active = true
endwith

this.CUSTOMER1 = new QUERY()
this.CUSTOMER1.parent = this
with (this.CUSTOMER1)
left = 7.0
top = 6.5
database = form.dbasetutorial1
sql = "select * from customer.dbf"
active = true
endwith

with (this.CUSTOMER1.rowset)
fields["State ID"].lookupSQL = "select * from state order by state"
endwith

this.COMBOBOX1 = new COMBOBOX(this)
with (this.COMBOBOX1)
onChange = class::COMBOBOX1_ONC
HANGE
dataLink = form.customer1.rowset.fields["state id"]
height = 1.0
left = 8.0
top = 3.0
width = 26.0
style = 2 // DropDownList
endwith

this.ENTRYFIELD1 = new ENTRYFIELD(this)
with (this.ENTRYFIELD1)
dataLink = form.customer1.rowset.fields["state id"]
height = 1.0
left = 8.0
top = 1.0
width = 20.0
endwith

this.rowset = this.customer1.rowset

function COMBOBOX1_onChange
form.entryfield1.value=this.value
// OR
form.rowset.save()
return

endclass

Keith Colombo wrote:
> I have been working way too long on trying to get an entryfield to update when using a lookup sql. Thank you in advance for the help.
>
> Using a single table with fields SesNo and StartDate. SesNoEntryfield on form is datalinked to table fiield SesNo. Lookup SQL for the SesNoCombobox = select SesNo, SesNo, StartDate from Sessions. StartDateEntryfield datalink = form.sessions1.rowset.
fields["StartDate"].
>
> I don't know how to get the StartDateEntryfield to reflect the rowset's StartDate when SesNo is changed in the lookup drop down combobox. Can you help?
>
> I know that the lookupRowset is a property of the lookup query field. I think there are two rowsets at work here; one for the form.session1 and the other the lookup query. I think I need to assign the form.StartDateEntryfield.value property to the look
up
> rowset.field["StartDate"].value property in the onChange event of the SesNoCombobox.
>
> I started with dBase III in 1987 and then dBase iV in '88. This is my first attempt at OODML with 2.6. I was amazed at the capabilities of dBase then and am blown away by them now, i.e. Contax example.
>
> I noticed in the tips.zip file, lkup.wfm addressed this issue, however, I no longer have the mugs database files. There used to be an archive newsgroup that had alot of these questions. Where can I get the mugs database files?
>
> Thanks again.
>
> Keith
>

Keith Colombo

2006-01-26, 4:54 pm

Mike,

I guess I wasn't very clear. I apoligize.

Attached are two small files that represent what I am trying to do. The StartDate Entry is linked to the Sessions Table Query field startDate. The Sessiion number combobox is linked to the same table but to field Sesion number. The Session Number combox
lookupSql calls the same Sessions table field Session number.

I am trying to get the startDate Entry field to reflect the corresponding session number when selected in the drop down session number combobox.

I left some notes in the combobox onChange method.

Thank you.

Keith

Michael Nuwer [dBVIPS] Wrote:
[color=darkred]
>
> I\\'m not altogether sure I understand what you trying to do. Assuming
> that the combo box and the entry field are both datalinked to the same
> field in the same query object, then a change in the combo box will not
> be updated in the entry field until the rowset.save() method is
> executed. If you do not want to execute the save() method you could
> manually set the entry field value:
>
> form.entryfield1.value=this.value
>
> The following form is a model of what I understand you are asking and a
> possible answer.
>
>
> ** END HEADER -- do not remove this line
> //
> // Generated on 01/25/2006
> //
> parameter bModal
> local f
> f = new demotestForm()
> if (bModal)
> f.mdi = false // ensure not MDI
> f.readModal()
> else
> f.open()
> endif
>
> class demotestForm of FORM
> with (this)
> height = 16.0
> left = 44.7143
> top = 4.8636
> width = 40.0
> text = \\\"\\\"
> endwith
>
> this.DBASETUTORIAL1 = new DATABASE()
> this.DBASETUTORIAL1.parent = this
> with (this.DBASETUTORIAL1)
> left = 9.0
> top = 9.5
> databaseName = \\\"DBASETUTORIAL\\\"
> active = true
> endwith
>
> this.CUSTOMER1 = new QUERY()
> this.CUSTOMER1.parent = this
> with (this.CUSTOMER1)
> left = 7.0
> top = 6.5
> database = form.dbasetutorial1
> sql = \\\"select * from customer.dbf\\\"
> active = true
> endwith
>
> with (this.CUSTOMER1.rowset)
> fields[\\\"State ID\\\"].lookupSQL = \\\"select * from state order by state\\\"
> endwith
>
> this.COMBOBOX1 = new COMBOBOX(this)
> with (this.COMBOBOX1)
> onChange = class::COMBOBOX1_ONC
HANGE
> dataLink = form.customer1.rowset.fields[\\\"state id\\\"]
> height = 1.0
> left = 8.0
> top = 3.0
> width = 26.0
> style = 2 // DropDownList
> endwith
>
> this.ENTRYFIELD1 = new ENTRYFIELD(this)
> with (this.ENTRYFIELD1)
> dataLink = form.customer1.rowset.fields[\\\"state id\\\"]
> height = 1.0
> left = 8.0
> top = 1.0
> width = 20.0
> endwith
>
> this.rowset = this.customer1.rowset
>
> function COMBOBOX1_onChange
> form.entryfield1.value=this.value
> // OR
> form.rowset.save()
> return
>
> endclass
>
>
>
>
> Keith Colombo wrote:
t.fields[\\\"StartDate\\\"].[color=darkred]
okup[color=darkred]

Keith Colombo

2006-01-26, 4:54 pm

John Marshall

2006-01-26, 4:54 pm

I think there is a simpler way. I struggled early on with the bells and whistles and finally came around to using methods that were predictable and reliable. Hope it helps.

SomeField_On_Change(
)

// called by change of sessions
// you could similarly have a lookup fn set to
// seek on a changed date as well and just load
// the other combobox value

mFound = form.premp1.rowset.findkey(val(form.id.value))
// ** seek a match in the rowset (make sure it's indexed)
if Mfound=True

form.entryfield1.value = form.premp1.rowset.fields["recip"].value
form.entryfield2.value = form.premp1.rowset.fields["Last"].value

else

// Perhaps set to most recent, show a msgbox, etc

endif

Return

JM

Keith Colombo Wrote:

> Mike,
>
> I guess I wasn't very clear. I apoligize.
>
> Attached are two small files that represent what I am trying to do. The StartDate Entry is linked to the Sessions Table Query field startDate. The Sessiion number combobox is linked to the same table but to field Sesion number. The Session Number comb
ox lookupSql calls the same Sessions table field Session number.
>
> I am trying to get the startDate Entry field to reflect the corresponding session number when selected in the drop down session number combobox.
>
> I left some notes in the combobox onChange method.
>
> Thank you.
>
> Keith
>
> Michael Nuwer [dBVIPS] Wrote:
>
set.fields[\\\"StartDate\\\"].[color=darkred]
lookup[color=darkred
]
>
>

Michael Nuwer [dBVIPS]

2006-01-26, 4:54 pm

Hello Keith,

I'm still having trouble understanding your ultimate goal. Let me make
a few comments and perhaps a suggestion.

1. A lookupSQL/lookuprowset is intended for preforming a lookup into a
second query object (in your case, a second instance of your table);
finding a value and replacing that value with a corresponding
description. This feature does not cause navigation in the first table.
Therefore, the entryfield on your form will not change.

2. an aside. Your select statement for the lookuprowset is miss-specified:

lookupSQL = "select Sesno, Sesno, StartDate from Sessions"

The internal lookup procedure uses only the first two field in a query.
The first field *must* be the lookup value; the second must be the
morphed value. So in your statement a number is looked-up (field 1) and
it is morphed into the same value (field 2).

3. It is true that there are two querys taking place; lookup & session1.
These two queries are not linked so navigation in the lookup does not
trigger navigation in session1.

4. I made some modifications to your form (see the form attached to this
message). I'm not sure this is what you are looking to do, however.
Note that there is no navigation taking place in the Session1 rowset.
This modification simply finds the value in the session2 query and
inserts it into the the current row of the session1 query. (I didn't
have indexes for your table, so I used the SQL where clause to filter
the lookup query. If you have indexes you could use the findKey() or
setRange() methods in stead.)

The thing that's not clear to me is whether you want to use the combobox
to cause navigation in the session1 query or whether you want to use the
combobox to lookup a value and insert it into the session1 query. My
form does the latter.

Keith Colombo wrote:
> Mike,
>
> I guess I wasn't very clear. I apoligize.
>
> Attached are two small files that represent what I am trying to do. The StartDate Entry is linked to the Sessions Table Query field startDate. The Sessiion number combobox is linked to the same table but to field Sesion number. The Session Number comb
ox lookupSql calls the same Sessions table field Session number.
>
> I am trying to get the startDate Entry field to reflect the corresponding session number when selected in the drop down session number combobox.
>
> I left some notes in the combobox onChange method.
>
> Thank you.
>
> Keith
>

Keith Colombo

2006-01-26, 4:54 pm

Mike & John,

From the Language Reference:

// "SELECT <lookup field>, <display field> [,...] FROM <lookup table> [<options>]"

/* The first two fields must be the lookup field and the display field, respectively. The display field may be a calculated field. You may include other fields so that you can get information about the chosen row. The SQL SELECT statement may include the
usual options; in particular, you may want the table to be ordered on the lookup field (or use a table where such an index is available) for faster lookups. The SQL statement is executed in the same database as the query (or stored procedure) that contain
s field’s rowset"*/

The above, as I read it, says the lookup sql can include other fields so you can get information about the chosen row. I had the impression that I could get the value of "other fields" from the lookup sql rowset directly if other fields were included in
the lookup sql statement

"Select sesno, sesno, startdate from sessions" --- with this lookup sql, I was including the field startdate so I could get its value directly from the lookup sql when the sesno selection took place. I would expect that this approach would be the most eff
icient as no other data searches would be necessary. Your response, as well as John's, make me think that I can't.

I appreciate very much being shown alternatives. I was stuck and didn't even think of alternatives. I now know that there are indeed two separate querys taking place and only the lookup rowset pointer is being changed. I now know there is a separate lo
okup rowset (thank you). I don't know how to access the data in the lookup rowset, however.

My intent was to use the session number combobox selection as a query filter value for a different table query. Each session number in the table has a unique start date value and I simply wanted to display the start date value which corresponded to the s
elected session number value.

The team number combo box will also be a query filter.

I again apologize for my being unclear. It comes from my unfamiliarity with the software. Thank you for being patient with me. In six months i'll be able to look back at my postings and embarrass myself.

Keith

Michael Nuwer [dBVIPS] Wrote:

>
> Hello Keith,
>
> I'm still having trouble understanding your ultimate goal. Let me make
> a few comments and perhaps a suggestion.
>
> 1. A lookupSQL/lookuprowset is intended for preforming a lookup into a
> second query object (in your case, a second instance of your table);
> finding a value and replacing that value with a corresponding
> description. This feature does not cause navigation in the first table.
> Therefore, the entryfield on your form will not change.
>
> 2. an aside. Your select statement for the lookuprowset is miss-specified:
>
> lookupSQL = "select Sesno, Sesno, StartDate from Sessions"
>
> The internal lookup procedure uses only the first two field in a query.
> The first field *must* be the lookup value; the second must be the
> morphed value. So in your statement a number is looked-up (field 1) and
> it is morphed into the same value (field 2).
>
> 3. It is true that there are two querys taking place; lookup & session1.
> These two queries are not linked so navigation in the lookup does not
> trigger navigation in session1.
>
> 4. I made some modifications to your form (see the form attached to this
> message). I'm not sure this is what you are looking to do, however.
> Note that there is no navigation taking place in the Session1 rowset.
> This modification simply finds the value in the session2 query and
> inserts it into the the current row of the session1 query. (I didn't
> have indexes for your table, so I used the SQL where clause to filter
> the lookup query. If you have indexes you could use the findKey() or
> setRange() methods in stead.)
>
> The thing that's not clear to me is whether you want to use the combobox
> to cause navigation in the session1 query or whether you want to use the
> combobox to lookup a value and insert it into the session1 query. My
> form does the latter.
>
>
>
>
>
>
>
> Keith Colombo wrote:
mbox lookupSql calls the same Sessions table field Session number.[color=darkred]
>
> ** END HEADER -- do not remove this line
> //
> // Generated on 01/25/2006
> //
> parameter bModal
> local f
> f = new ScoreEntryForm()
> if (bModal)
> f.mdi = false // ensure not MDI
> f.readModal()
> else
> f.open()
> endif
>
> class ScoreEntryForm of FORM
> with (this)
> onOpen = class::FORM_ONOPEN
> height = 7.8182
> left = 9.4286
> top = 8.3182
> width = 81.1429
> text = ""
> endwith
>
> this.SESSIONS1 = new QUERY()
> this.SESSIONS1.parent = this
> with (this.SESSIONS1)
> left = 6.0
> top = 4.5
> sql = 'select * from "Sessions.DBF"'
> active = true
> endwith
>
> this.SESSIONS2 = new QUERY()
> this.SESSIONS2.parent = this
> with (this.SESSIONS2)
> left = 6.0
> top = 4.5
> sql = 'select * from "Sessions.DBF"'
> active = true
> endwith
>
> with (this.SESSIONS1.rowset)
> //fields["SesNo"].lookupSQL = "select Sesno, StartDate from Sessions"
> endwith
>
> this.TEXTLABEL1 = new TEXTLABEL(this)
> with (this.TEXTLABEL1)
> height = 1.5
> left = 23.0
> top = 0.5
> width = 31.0
> text = "Enter Player's Score"
> fontSize = 14.0
> alignHorizontal = 1 // Center
> endwith
>
> this.TEXTLABEL3 = new TEXTLABEL(this)
> with (this.TEXTLABEL3)
> height = 1.0
> left = 2.0
> top = 2.5
> width = 12.0
> text = "Session No.:"
> endwith
>
> this.TEXTLABEL4 = new TEXTLABEL(this)
> with (this.TEXTLABEL4)
> height = 1.0
> left = 54.0
> top = 2.5
> width = 10.0
> text = "Start Date:"
> endwith
>
> this.STARTENTRY = new ENTRYFIELD(this)
> with (this.STARTENTRY)
> dataLink = form.sessions1.rowset.fields["startdate"]
> height = 1.0
> left = 66.0
> top = 2.5
> width = 11.0
> endwith
>
> this.TEXTLABEL10 = new TEXTLABEL(this)
> with (this.TEXTLABEL10)
> height = 1.0
> left = 30.0
> top = 2.5
> width = 9.0
> text = "Team No:"
> endwith
>
> this.SESNOBOX = new COMBOBOX(this)
> with (this.SESNOBOX)
> onChange = class::SESNOBOX_ONCH
ANGE
> //dataLink = form.sessions1.rowset.fields["sesno"]
> height = 1.0
> left = 16.0
> top = 2.5
> width = 11.0
> dataSource = form.sessions2.rowset.fields["sesno"]
> style = 1 // DropDown
> endwith
>
> this.TEAMNOBOX = new COMBOBOX(this)
> with (this.TEAMNOBOX)
> onChange = class::sesnobox_onCh
ange
> height = 1.0
> left = 39.0
> top = 2.5
> width = 12.0
> style = 1 // DropDown
> endwith
>
> this.rowset = this.sessions1.rowset
>
> function SESNOBOX_onChange
> /* I think the row pointer changes in the lookup query but i'm not sure how to access the values.
> Are there two querys taking place; lookup & session1? */
> cSQL = 'select * from sessions where sesNo='+this.value
> form.sessions2.sql= cSQL
> ? form.sessions2.rowset.fields["StartDate"].value
> form.startentry.value = form.sessions2.rowset.fields["StartDate"].value // ------ doesn't work
> return
>
> function form_onOpen
> form.sessions1.rowset.last()
> form.SesNobox.value = form.sessions1.rowset.fields["SesNo"].value // defalt to the last session
>
> return
>
> endclass
>
>

Michael Nuwer [dBVIPS]

2006-01-26, 4:54 pm

Keith Colombo wrote:
> Mike & John,
>
> The above, as I read it, says the lookup sql can include other fields so you can get information about the chosen row. I had the impression that I could get the value of "other fields" from the lookup sql rowset directly if other fields were included i
n the lookup sql statement
>
> "Select sesno, sesno, startdate from sessions" --- with this lookup sql, I was including the field startdate so I could get its value directly from the lookup sql when the sesno selection took place. I would expect that this approach would be the most e
fficient as no other data searches would be necessary. Your response, as well as John's, make me think that I can't.
>

Well, yes it can be done (see the form below), but this is not what the
lookuprowset was intended for :-). So, be sure to test it alot.

> I appreciate very much being shown alternatives. I was stuck and didn't even think of alternatives. I now know that there are indeed two separate querys taking place and only the lookup rowset pointer is being changed. I now know there is a separate
lookup rowset (thank you). I don't know how to access the data in the lookup rowset, however.
>
> My intent was to use the session number combobox selection as a query filter value for a different table query. Each session number in the table has a unique start date value and I simply wanted to display the start date value which corresponded to the
selected session number value.

I think the following form does this.

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

class demo2Form of FORM
with (this)
height = 16.0
left = 36.8571
top = 1.6818
width = 40.0
text = ""
endwith

this.SESSIONS1 = new QUERY()
this.SESSIONS1.parent = this
with (this.SESSIONS1)
left = 13.0
top = 7.5
sql = 'select * from "Sessions.DBF"'
active = true
endwith

with (this.SESSIONS1.rowset)
fields["SesNo"].lookupSQL = "select Sesno, Sesno, StartDate from
Sessions"
endwith

this.ENTRYFIELD1 = new ENTRYFIELD(this)
with (this.ENTRYFIELD1)
height = 1.0
left = 8.0
top = 1.0
width = 24.0
value = "Entryfield1"
endwith

this.COMBOBOX1 = new COMBOBOX(this)
with (this.COMBOBOX1)
onChange = class::COMBOBOX1_ONC
HANGE
dataLink = form.sessions1.rowset.fields["sesno"]
height = 1.0
left = 8.0
top = 3.5
width = 23.0
style = 1 // DropDown
endwith

this.rowset = this.sessions1.rowset

function COMBOBOX1_onChange
form.entryField1.value = ;
form.rowset.fields['SesNo'].lookupRowset.fields[3].value
return

endclass

Keith Colombo

2006-01-26, 4:54 pm

Mike,

I got it now. You have shown me the light.

Thank you very very much.

Keith

Michael Nuwer [dBVIPS] Wrote:

> Keith Colombo wrote:
in the lookup sql statement[color=dark
red]
efficient as no other data searches would be necessary. Your response, as well as John's, make me think that I can't.[color=darkred]
>
> Well, yes it can be done (see the form below), but this is not what the
> lookuprowset was intended for :-). So, be sure to test it alot.
>
>
e lookup rowset (thank you). I don't know how to access the data in the lookup rowset, however.[color=darkred]
he selected session number value.[color=darkred]
>
> I think the following form does this.
>
> ** END HEADER -- do not remove this line
> //
> // Generated on 01/26/2006
> //
> parameter bModal
> local f
> f = new demo2Form()
> if (bModal)
> f.mdi = false // ensure not MDI
> f.readModal()
> else
> f.open()
> endif
>
> class demo2Form of FORM
> with (this)
> height = 16.0
> left = 36.8571
> top = 1.6818
> width = 40.0
> text = ""
> endwith
>
> this.SESSIONS1 = new QUERY()
> this.SESSIONS1.parent = this
> with (this.SESSIONS1)
> left = 13.0
> top = 7.5
> sql = 'select * from "Sessions.DBF"'
> active = true
> endwith
>
> with (this.SESSIONS1.rowset)
> fields["SesNo"].lookupSQL = "select Sesno, Sesno, StartDate from
> Sessions"
> endwith
>
> this.ENTRYFIELD1 = new ENTRYFIELD(this)
> with (this.ENTRYFIELD1)
> height = 1.0
> left = 8.0
> top = 1.0
> width = 24.0
> value = "Entryfield1"
> endwith
>
> this.COMBOBOX1 = new COMBOBOX(this)
> with (this.COMBOBOX1)
> onChange = class::COMBOBOX1_ONC
HANGE
> dataLink = form.sessions1.rowset.fields["sesno"]
> height = 1.0
> left = 8.0
> top = 3.5
> width = 23.0
> style = 1 // DropDown
> endwith
>
> this.rowset = this.sessions1.rowset
>
> function COMBOBOX1_onChange
> form.entryField1.value = ;
> form.rowset.fields['SesNo'].lookupRowset.fields[3].value
> return
>
> endclass

John Marshall

2006-01-26, 4:54 pm

Keith:

The other option is to preload the comboboxes with an array fillled from your data, rather than linking to the files themselves. Also, I recall that there were two identical session numbers that had different dates. Perhaps you should look at a "see
ker" type filter that limits the results as you specify more details. Since findkey works on partial matches, you could index on session+dtoc(date) and on change of your session refresh the find (placing the resultant list in a grid), and if there are mu
ltiple matches, the date combo would further the find results. In such a case, you'd have to "hide" the date (or restrict its use) until the user provided a valid session to start the find.

Alternatively, you may want to literally use these conbo values as parameters in the where clause of a SQL querry of the main file. Take a look at 'PARAMS' in help. You can specify a query Sele * from x where sessions =:msessions. Set a default session
: form.rowset.params["msessions'] = 1. Your ON_Change function would be form.rowset.params["msessions"] = form.combobox1.value followed by a form.rowset.requery() to requery the new scoped results. This is my favorite solution.

JM

JM

Keith Colombo Wrote:

> Mike,
>
> I got it now. You have shown me the light.
>
> Thank you very very much.
>
> Keith
>
>
> Michael Nuwer [dBVIPS] Wrote:
>
ed in the lookup sql statement[color=dark
red]
st efficient as no other data searches would be necessary. Your response, as well as John's, make me think that I can't.[color=darkred]
ate lookup rowset (thank you). I don't know how to access the data in the lookup rowset, however.[color=darkred]
the selected session number value.[color=darkred]
>

John Marshall

2006-01-26, 4:54 pm

Keith Colombo

2006-01-26, 4:54 pm

John,

I am a real novice and don't yet know how to fill in an array with field data yet.

I am headed to the Params file now though. Thanks.

Keith

John Marshall Wrote:

>
> Keith:
>
> The other option is to preload the comboboxes with an array fillled from your data, rather than linking to the files themselves. Also, I recall that there were two identical session numbers that had different dates. Perhaps you should look at a "s
eeker" type filter that limits the results as you specify more details. Since findkey works on partial matches, you could index on session+dtoc(date) and on change of your session refresh the find (placing the resultant list in a grid), and if there are
multiple matches, the date combo would further the find results. In such a case, you'd have to "hide" the date (or restrict its use) until the user provided a valid session to start the find.
>
> Alternatively, you may want to literally use these conbo values as parameters in the where clause of a SQL querry of the main file. Take a look at 'PARAMS' in help. You can specify a query Sele * from x where sessions =:msessions. Set a default sessi
on: form.rowset.params["msessions'] = 1. Your ON_Change function would be form.rowset.params["msessions"] = form.combobox1.value followed by a form.rowset.requery() to requery the new scoped results. This is my favorite solution.
>
> JM
>
> JM
>
>
>
>
> Keith Colombo Wrote:
>
uded in the lookup sql statement[color=dark
red]
most efficient as no other data searches would be necessary. Your response, as well as John's, make me think that I can't.[color=darkred]
arate lookup rowset (thank you). I don't know how to access the data in the lookup rowset, however.[color=darkred]
to the selected session number value.[color=darkred]
>

Keith Colombo

2006-01-26, 4:54 pm

Mike,

The form works, but, only after the second selection from the SesNo combobox. It actually changes the table data, which I didn't want. It also does not work at all with the rowset autoedit = false.

I'm not sure what's happening here. I am perhaps trying to do something with the lookupSql that it wasn't designed to do. I would like to know what the software is actually trying to do so I can learn the software. I know there are other alternatives as
you and John pointed out, however, can you describe to me what the software is actually doing? Thank you.

For now, I'm going to the PARAMS help file as John suggested.

Keith

Michael Nuwer [dBVIPS] Wrote:

> Keith Colombo wrote:
in the lookup sql statement[color=dark
red]
efficient as no other data searches would be necessary. Your response, as well as John's, make me think that I can't.[color=darkred]
>
> Well, yes it can be done (see the form below), but this is not what the
> lookuprowset was intended for :-). So, be sure to test it alot.
>
>
e lookup rowset (thank you). I don't know how to access the data in the lookup rowset, however.[color=darkred]
he selected session number value.[color=darkred]
>
> I think the following form does this.
>
> ** END HEADER -- do not remove this line
> //
> // Generated on 01/26/2006
> //
> parameter bModal
> local f
> f = new demo2Form()
> if (bModal)
> f.mdi = false // ensure not MDI
> f.readModal()
> else
> f.open()
> endif
>
> class demo2Form of FORM
> with (this)
> height = 16.0
> left = 36.8571
> top = 1.6818
> width = 40.0
> text = ""
> endwith
>
> this.SESSIONS1 = new QUERY()
> this.SESSIONS1.parent = this
> with (this.SESSIONS1)
> left = 13.0
> top = 7.5
> sql = 'select * from "Sessions.DBF"'
> active = true
> endwith
>
> with (this.SESSIONS1.rowset)
> fields["SesNo"].lookupSQL = "select Sesno, Sesno, StartDate from
> Sessions"
> endwith
>
> this.ENTRYFIELD1 = new ENTRYFIELD(this)
> with (this.ENTRYFIELD1)
> height = 1.0
> left = 8.0
> top = 1.0
> width = 24.0
> value = "Entryfield1"
> endwith
>
> this.COMBOBOX1 = new COMBOBOX(this)
> with (this.COMBOBOX1)
> onChange = class::COMBOBOX1_ONC
HANGE
> dataLink = form.sessions1.rowset.fields["sesno"]
> height = 1.0
> left = 8.0
> top = 3.5
> width = 23.0
> style = 1 // DropDown
> endwith
>
> this.rowset = this.sessions1.rowset
>
> function COMBOBOX1_onChange
> form.entryField1.value = ;
> form.rowset.fields['SesNo'].lookupRowset.fields[3].value
> return
>
> endclass

Michael Nuwer [dBVIPS]

2006-01-26, 8:23 pm

Keith Colombo wrote:
> Mike,
>
> The form works, but, only after the second selection from the SesNo combobox.

Try changing the combobox's style property to "2". That might help.

> It actually changes the table data, which I didn't want.

The combobox is datalinked to a field in the Session1 query, so that
field will be modified when the user selects a value from the dropdown
list. This is the way it's designed to work and is a common task for
data entry and data storage.

> It also does not work at all with the rowset autoedit = false.

Right, the rowset (session1) must be in edit mode.

>
> I'm not sure what's happening here. I am perhaps trying to do something with the lookupSql that it wasn't designed to do. I would like to know what the software is actually trying to do so I can learn the software. I know there are other alternatives a
s you and John pointed out, however, can you describe to me what the software is actually doing? Thank you.

Yes, I do think you are trying to do something with the lookupRowset
feature which it was not intended for.

Many database designers store code values in their tables. States in US
are two characters, clients have ID values, products have part numbers,
medical procedures have codes, etc., etc. Often times these code values
are not helpful to the user; the user would rather see the description
and, when editing records, choose information by description rather than
by code value. Because this structure is so common in database systems
(not only dBASE, but other systems too), companys like dBASE build-in
the functionality for "morphing" code values into descriptions, that is,
the descriptions are automatically substituted for the code values, and
the software keeps track of what's what.

As you are discovering, this feature is intended for displaying
"morphed" data on a form (or report), and editing data in a data linked
control. It is not intended as a way for finding or seeking records in a
table.

I suppose the term "lookup" could cause confusion. I might call what you
are looking to do a "pick list" rather than a lookup. There are many
ways that this could be done, but, in my opinion, it is *not* good
design techniques to use a combobox for this type of thing. There are
other controls and techniques that are better.

Consider the form displayed in this link:
http://www.volsoft.com/tour/srvol1.htm

This is a dBASE application. The left side of the form employs a "grid"
object, which, as you can see, is a list of names. The use picks the
appropriate name from the list and, because this grid is linked to the
rowset, navigation to the appropriate record occurs. The attached form
is an example of this type of thing with some code.

There are of course other ways to design pick lists. The dBASE Tutorial
has two options: (1) a two page form with the pick list grid on one page
and the editing controls on another page; and (2) a popup form for
picking items.

Hope this helps.

Keith Colombo

2006-01-27, 3:23 am

Mike,

Thank you very much. I have a much better understanding of whats going on with the lookup SQL and now know how to access the lookup rowset.

I did your tutorial about a year ago, maybe longer. Went through Phase VI. Also have been through many of Ken's How To's (also about a year ago). Actually, I forgot I had printed most of them out and have them in a 3-Ring binder. I'll review them.

Again, I really appreciate your help.

Keith

Michael Nuwer [dBVIPS] Wrote:

> Keith Colombo wrote:
>
> Try changing the combobox's style property to "2". That might help.
>
>
> The combobox is datalinked to a field in the Session1 query, so that
> field will be modified when the user selects a value from the dropdown
> list. This is the way it's designed to work and is a common task for
> data entry and data storage.
>
>
> Right, the rowset (session1) must be in edit mode.
>
as you and John pointed out, however, can you describe to me what the software is actually doing? Thank you.[color=darkred]
>
> Yes, I do think you are trying to do something with the lookupRowset
> feature which it was not intended for.
>
> Many database designers store code values in their tables. States in US
> are two characters, clients have ID values, products have part numbers,
> medical procedures have codes, etc., etc. Often times these code values
> are not helpful to the user; the user would rather see the description
> and, when editing records, choose information by description rather than
> by code value. Because this structure is so common in database systems
> (not only dBASE, but other systems too), companys like dBASE build-in
> the functionality for "morphing" code values into descriptions, that is,
> the descriptions are automatically substituted for the code values, and
> the software keeps track of what's what.
>
> As you are discovering, this feature is intended for displaying
> "morphed" data on a form (or report), and editing data in a data linked
> control. It is not intended as a way for finding or seeking records in a
> table.
>
> I suppose the term "lookup" could cause confusion. I might call what you
> are looking to do a "pick list" rather than a lookup. There are many
> ways that this could be done, but, in my opinion, it is *not* good
> design techniques to use a combobox for this type of thing. There are
> other controls and techniques that are better.
>
> Consider the form displayed in this link:
> http://www.volsoft.com/tour/srvol1.htm
>
> This is a dBASE application. The left side of the form employs a "grid"
> object, which, as you can see, is a list of names. The use picks the
> appropriate name from the list and, because this grid is linked to the
> rowset, navigation to the appropriate record occurs. The attached form
> is an example of this type of thing with some code.
>
> There are of course other ways to design pick lists. The dBASE Tutorial
> has two options: (1) a two page form with the pick list grid on one page
> and the editing controls on another page; and (2) a popup form for
> picking items.
>
> Hope this helps.
>
>
> ** END HEADER -- do not remove this line
> //
> // Generated on 01/26/2006
> //
> parameter bModal
> local f
> f = new abcdeForm()
> if (bModal)
> f.mdi = false // ensure not MDI
> f.readModal()
> else
> f.open()
> endif
>
> class abcdeForm of FORM
> with (this)
> height = 16.0
> left = 53.0
> top = 0.0
> width = 46.7143
> text = ""
> endwith
>
> this.SESSIONS1 = new QUERY()
> this.SESSIONS1.parent = this
> with (this.SESSIONS1)
> left = 20.0
> top = 12.0
> sql = 'select * from "Sessions.DBF"'
> active = true
> endwith
>
> this.SPINBOXSTARTDATE1 = new SPINBOX(this)
> with (this.SPINBOXSTARTDATE1)
> dataLink = form.sessions1.rowset.fields["startdate"]
> height = 1.0
> left = 19.0
> top = 2.0
> width = 23.0
> rangeMax = {06/12/2005}
> rangeMin = {03/04/2005}
> endwith
>
> this.GRID1 = new GRID(this)
> with (this.GRID1)
> dataLink = form.sessions1.rowset
> columns["Column1"] = new GRIDCOLUMN(form.GRID1)
> columns["Column1"].dataLink = form.sessions1.rowset.fields["sesno"]
> columns["Column1"].editorType = 4 // ComboBox
> columns["Column1"].width = 5.2857
> with (columns["Column1"].editorControl)
> dropDownHeight = 6.8182
> endwith
>
> with (columns["Column1"].headingControl)
> value = "sesno_1"
> endwith
>
> hasRowLines = false
> hasIndicator = false
> hasColumnHeadings = false
> rowSelect = true
> height = 8.0
> left = 1.0
> top = 1.0
> width = 8.0
> endwith
>
> this.rowset = this.sessions1.rowset
>
> endclass
>
>

Sponsored Links