Dynamic LookUpSQL

[David Hurtado]

I'm having a difficult time trying to make a dynamic lookupSQL:

with (this.REQA1.rowset)
fields["COMCOD"].lookupSQL = 'SELECT "COMCOD",COMCOD FROM "COMMOD.dbf" COMCOD WHERE COMPNY = REQA1.COMPNY'
endwith

I want to pass the COMPNY value from the REQA1 rowset to the SQL statement in.

Any one has any idea how to pass a value the SQL statement for the LOOKUP so that I can filter only the value corresponding to COMPNY.

Thank you much.

David

[Jan Hoelterling]

David,

where is the COMPNO from? Is it something the user can change in the form?

The approach you're taking is correct, I have a feeling you're just not
doing it in the right place.

Jan

[David Hurtado]

COMPNO is company number, a common field between the two tables I'm working with, for every record I'm maintaining resulting from REQA1 query, the whole code is the following:

class reqaDataModule of EDIWHEREDATAMODULE from "ediwhere.cdm"


this.REQA1 = new QUERY()
this.REQA1.parent = this
with (this.REQA1)
left = 2
top = 2.5
database = form.dbediwhere
sql = 'SELECT * FROM "REQA.DBF"'
active = true
endwith


with (this.REQA1.rowset)
fields["COMCOD"].lookupSQL = 'SELECT "COMCOD",COMCOD FROM "COMMOD.dbf" COMCOD'
endwith

endclass

The code is part of a Data Module, where I query a master file and then select different values from tributary tables like states, carriers, containers, customer. All of these tributary values are company specific, that's why i need to dynamically select the available commodity codes based on the current company rowset field, ie.

Please let me know if you know how to filter the lookupSQL in a dynamic fashion. I'm open to suggestions.

Regards,

David.

Post by Jan Hoelterling
David,
where is the COMPNO from? Is it something the user can change in the form?
The approach you're taking is correct, I have a feeling you're just not
doing it in the right place.
Jan

[Jan Hoelterling]

Hi David,

as Bruce and Geoff already alluded to, MasterRowset is the better solution
here. LookupSQL is mainly used to retrieve the description of a code in a
single field (in the process, effectively translating the field value on the
fly). As such, it is dynamic in nature - any time you navigate the rowset,
the current value of the "looked up" field will be updated based on the
lookupSQL.

I hope I've understood your problem correctly.

Jan

[Geoff Wass [dBVIPS]]

Post by David Hurtado
with (this.REQA1.rowset)
fields["COMCOD"].lookupSQL = 'SELECT "COMCOD",COMCOD FROM "COMMOD.dbf" COMCOD WHERE COMPNY = REQA1.COMPNY'
endwith
I want to pass the COMPNY value from the REQA1 rowset to the SQL statement in.
Any one has any idea how to pass a value the SQL statement for the LOOKUP so that I can filter only the value corresponding to COMPNY.
Thank you much.
David

David,

LookupSQL is a property of the FIELD. You are trying to assign it to the
ROWSET. Check the online help for an example of how to use it. Since
this is in a datamodule, you can also use to QUERYs and join them in the
usual way with MasterRowset and MasterFields.

[Bruce Beacham]

Post by Geoff Wass [dBVIPS]

Post by David Hurtado
with (this.REQA1.rowset)
fields["COMCOD"].lookupSQL = 'SELECT "COMCOD",COMCOD FROM "COMMOD.dbf" COMCOD WHERE COMPNY = REQA1.COMPNY'
endwith

LookupSQL is a property of the FIELD. You are trying to assign it to the
ROWSET.

Is he?


Bruce Beacham

[Geoff Wass [dBVIPS]]

In article <***@news-server>, ***@beacham.no-
spam.co.uk says...

Post by Bruce Beacham

Post by Geoff Wass [dBVIPS]

Post by David Hurtado
with (this.REQA1.rowset)
fields["COMCOD"].lookupSQL = 'SELECT "COMCOD",COMCOD FROM "COMMOD.dbf" COMCOD WHERE COMPNY = REQA1.COMPNY'
endwith

LookupSQL is a property of the FIELD. You are trying to assign it to the
ROWSET.

Is he?
Bruce Beacham

Bruce,

You score again! LOL. Yes, Bruce is right to question what I said. I pu
the mouth in gear before the engaging the brain. The code above IS
assigning to the FIELDS object.

However, I would still advise using QUERY objects.

[Bruce Beacham]

David Hurtado wrote:

fields["COMCOD"].lookupSQL = 'SELECT
"COMCOD",COMCOD FROM "COMMOD.dbf" COMCOD WHERE COMPNY = REQA1.COMPNY'

Is this a typo:
"COMCOD",COMCOD

Should it be
"COMCOD".COMCOD

OK, if that is the case, you are requesting the return of only one field
from the looked up table.

However, going back to your explanation, a lookupsql on a field does
return a rowset but is generally used to pick up a value on the single
first row that the lookup points to. So the WHERE clause is not
applicable in that context: what matters is the key used to locate the
row you want, and you let PLUS handle the rest.

You specify two fields in the lookup: the key field, and the return
value. PLUS drives the search for the key value from the current row
in the key field of the lookuprowset.

If you want a whole rowset from the search (for some other reason) you
wouldn't use lookupsql: you'd use a child query with a
masterrowset/masterfields arrangement.


HTH


Bruce Beacham

[david hurtado]

Thank you Bruce,

Do you have code how to accomplish what you described below:

" a child query with a masterrowset/masterfields arrangement"

Regards,

David.

Post by David Hurtado
fields["COMCOD"].lookupSQL = 'SELECT
"COMCOD",COMCOD FROM "COMMOD.dbf" COMCOD WHERE COMPNY = REQA1.COMPNY'
"COMCOD",COMCOD
Should it be
"COMCOD".COMCOD
OK, if that is the case, you are requesting the return of only one field
from the looked up table.
However, going back to your explanation, a lookupsql on a field does
return a rowset but is generally used to pick up a value on the single
first row that the lookup points to. So the WHERE clause is not
applicable in that context: what matters is the key used to locate the
row you want, and you let PLUS handle the rest.
You specify two fields in the lookup: the key field, and the return
value. PLUS drives the search for the key value from the current row
in the key field of the lookuprowset.
If you want a whole rowset from the search (for some other reason) you
wouldn't use lookupsql: you'd use a child query with a
masterrowset/masterfields arrangement.
HTH
Bruce Beacham

[Jan Hoelterling]

Try this:

class reqaDataModule of EDIWHEREDATAMODULE from "ediwhere.cdm"


this.REQA1 = new QUERY()
this.REQA1.parent = this
with (this.REQA1)
left = 2
top = 2.5
database = form.dbediwhere
sql = 'SELECT * FROM "REQA.DBF"'
active = true
endwith

This.COMCOD1 = new query()
this.COMCOD1.parent = this
with (this.COMCOD1)
database = form.dbediwhere
sql = 'SELECT * FROM "COMMOD.DBF"'
active = true
endwith

with (this.COMMOD1.rowset)
indexname = "COMCOD" //<< name of the lookupfield needs an index
masterrowset = form.REQA1.rowset
Masterfields = "COMCOD"
endwith

endclass

Hope this helps,

Jan

[David Hurtado]

Hi Jan,

I tried this process and it does work. But it doesn't work for Combo box. It works for Data Grids and List Box. Do you know why is not populating the combo box even though I connected the data source accordingly? any idea?

Thank you,

David.

Post by David Hurtado
class reqaDataModule of EDIWHEREDATAMODULE from "ediwhere.cdm"
this.REQA1 = new QUERY()
this.REQA1.parent = this
with (this.REQA1)
left = 2
top = 2.5
database = form.dbediwhere
sql = 'SELECT * FROM "REQA.DBF"'
active = true
endwith
This.COMCOD1 = new query()
this.COMCOD1.parent = this
with (this.COMCOD1)
database = form.dbediwhere
sql = 'SELECT * FROM "COMMOD.DBF"'
active = true
endwith
with (this.COMMOD1.rowset)
indexname = "COMCOD" //<< name of the lookupfield needs an index
masterrowset = form.REQA1.rowset
Masterfields = "COMCOD"
endwith
endclass
Hope this helps,
Jan

[Geoff Wass [dBVIPS]]

Post by David Hurtado
Hi Jan,
I tried this process and it does work. But it doesn't work for Combo box. It works for Data Grids and List Box. Do you know why is not populating the combo box even though I connected the data source accordingly? any idea?
Thank you,
David.

David,

Can you show us what you did and describe what the results were?

[Geoff Wass [dBVIPS]]

<snipped>

REQA1 query will display all requirements in my display form. The COMMOD1 query need to display the commodity codes corresponding for each reach according to field COMPNY (By Company). When I try to connect a Combo box in the

form to the Commod1 query the data will be displayed only if the query is not filtered by compny. If Delete the lines in the data module where I filter by company then the data will be populated in the combo box. I just don't
understand why it won't work? any idea?


David,

When and how are you doing this filtering? Off-hand I would be wondering
if you need to .dataLink the combobox again after you have established
the filtering.

[David]

I'm filtering the information at the data module level. All data is filtered at the data module level and the I connected the forms and combo boxes at form level.

[Geoff Wass [dBVIPS]]

Post by David
I'm filtering the information at the data module level. All data is filtered at the data module level and the I connected the forms and combo boxes at form level.

David,

Can you show us what you mean?

[David Hurtado]

Ok, I'll try to explain to the best of my abilities:

I created a data module for my form:

class reqaDataModule of EDIWHEREDATAMODULE from "ediwhere.cdm"


this.REQA1 = new QUERY()
this.REQA1.parent = this
with (this.REQA1)
left = 2
top = 2.7273
session = form.SESSIONEDIWHERE
database = form.dbediwhere
sql = 'select * from "reqa.dbf"'
active = true
endwith




this.COMMOD1 = new QUERY()
this.COMMOD1.parent = this
with (this.COMMOD1)
left = 6.2857
top = 2.7273
database = form.dbediwhere
sql = 'SELECT * FROM "COMMOD.DBF"'
active = true
endwith


with (this.COMMOD1.rowset)

indexName = "COMPNY"
masterRowset = parent.parent.reqa1.rowset
masterFields = "COMPNY"
endwith

endclass

REQA1 query will display all requirements in my display form. The COMMOD1 query need to display the commodity codes corresponding for each reach according to field COMPNY (By Company). When I try to connect a Combo box in the form to the Commod1 query the data will be displayed only if the query is not filtered by compny. If Delete the lines in the data module where I filter by company then the data will be populated in the combo box. I just don't understand why it won't work? any idea?

Post by Jan Hoelterling

Post by David Hurtado
Hi Jan,
I tried this process and it does work. But it doesn't work for Combo box. It works for Data Grids and List Box. Do you know why is not populating the combo box even though I connected the data source accordingly? any idea?
Thank you,
David.

David,
Can you show us what you did and describe what the results were?
--
Geoff Wass [dBVIPS]
Montréal, Québec, Canada
.|.|.| dBASE info at http://geocities.com/geoff_wass |.|.|.
.|.|.| ---------------------------------------------------------- |.|.|.
.|.|.| IT Consultant http://Geoff_Wass.com |.|.|.