Getting Started with dBASE - LookupRowset problem

Author

LookupRowset problem

John Marshall

2005-06-26, 1:23 pm

I have a transaction file with an employee number that relates to a employee DB. I want to disply BOTH employee number as well as first,last. I have a LookupRowset defined in the transaction DB field that does link the two files correctly ... However, w
hen I show the resultant rowset in a grid the transaction file employee number is morphed into last, fist names. If I define another column in the rowset to show the empno from the employee db, there seems to be a bug. The first record's employee number
always shows in front of the first two records, and the subsequent employee names and matching empno's are correct ... it's just that record two always receives/shows record one's data.

Any thoughts? Are there any such bugs on this topic? Has anyone used/implented any updated rowset cc's?

Thanks in advance.

JM

Todd Kreuter

2005-06-27, 11:23 am

John Marshall wrote:
>
> I have a transaction file with an employee number that relates to a employee DB. I want to disply BOTH employee number as well as first,last. I have a LookupRowset defined in the transaction DB field that does link the two files correctly ... However,
when I show the resultant rowset in a grid the transaction file employee number is morphed into last, fist names. If I define another column in the rowset to show the empno from the employee db, there seems to be a bug. The first record's employee numb
er always shows in front of the first two records, and the subsequent employee names and matching empno's are correct ... it's just that record two always receives/shows record one's data.
>
> Any thoughts? Are there any such bugs on this topic? Has anyone used/implented any updated rowset cc's?

Looks like you trying to use the lookupRowset for something it was not
intened for. I think you need to use the table as a masterRowset/fields
link instead. The code would look something like:

this.query1 = new query()
with(this.query1)
sql = "Select * from primary.dbf"
active = true
endwith

this.query2 = new query()
with(this.query2)
sql = "Select * from employee.dbf"
active = true
endwith

with (this.query2.rowset)
indexname = "EmpNo"
masterRowset = parent.parent.query1.rowset
masterFields = "EmpNo" // name of field in query1.rowset
endwith

--
Todd Kreuter [dBVIPS]

john marshall

2005-06-27, 1:23 pm

Does it matter that the 'link' to employee db is many to one and that the child db is reused across lots of primary db records?

Can a grid show this hybrid grouping of records from 2 files, and normally, I set a range in the primary date and recipient (which doesn't seem to work when I tried left outer joins - see 'how to' question). If so, I guess I'd datalink to the primary and
do the child db records show in the column field chooser of the grid tool?

What I really want to do is get a grid to show empID, first, last, and amount. I need an index for order and range purposes. I am currently using morphing, but I cannot show the empno, because it gets turned into the name.

Thanks,

JM

Todd Kreuter Wrote:

>
>
> John Marshall wrote:
r, when I show the resultant rowset in a grid the transaction file employee number is morphed into last, fist names. If I define another column in the rowset to show the empno from the employee db, there seems to be a bug. The first record's employee nu
mber always shows in front of the first two records, and the subsequent employee names and matching empno's are correct ... it's just that record two always receives/shows record one's data.[color=darkred]
>
> Looks like you trying to use the lookupRowset for something it was not
> intened for. I think you need to use the table as a masterRowset/fields
> link instead. The code would look something like:
>
> this.query1 = new query()
> with(this.query1)
> sql = "Select * from primary.dbf"
> active = true
> endwith
>
> this.query2 = new query()
> with(this.query2)
> sql = "Select * from employee.dbf"
> active = true
> endwith
>
> with (this.query2.rowset)
> indexname = "EmpNo"
> masterRowset = parent.parent.query1.rowset
> masterFields = "EmpNo" // name of field in query1.rowset
> endwith
>
>
> --
> Todd Kreuter [dBVIPS]

john marshall

2005-06-27, 8:23 pm

Todd:

I tried the master rowset and the first matching record for first/last name repeats down the whole list of employee id's. Did I set it up correctly? Can one do a lookup in a 'beforegetvalue' in order to stuff the rowset array? Seems like a brute for
ce way to get a simple task done.

JM

Todd Kreuter Wrote:

>
>
> John Marshall wrote:
r, when I show the resultant rowset in a grid the transaction file employee number is morphed into last, fist names. If I define another column in the rowset to show the empno from the employee db, there seems to be a bug. The first record's employee nu
mber always shows in front of the first two records, and the subsequent employee names and matching empno's are correct ... it's just that record two always receives/shows record one's data.[color=darkred]
>
> Looks like you trying to use the lookupRowset for something it was not
> intened for. I think you need to use the table as a masterRowset/fields
> link instead. The code would look something like:
>
> this.query1 = new query()
> with(this.query1)
> sql = "Select * from primary.dbf"
> active = true
> endwith
>
> this.query2 = new query()
> with(this.query2)
> sql = "Select * from employee.dbf"
> active = true
> endwith
>
> with (this.query2.rowset)
> indexname = "EmpNo"
> masterRowset = parent.parent.query1.rowset
> masterFields = "EmpNo" // name of field in query1.rowset
> endwith
>
>
> --
> Todd Kreuter [dBVIPS]

john marshall

2005-06-27, 8:23 pm

Funny thing - perhaps a clue .... the names lookup and match when in the designer, but when I run it, they all are just the first matching record's data across all.

JM

Todd Kreuter Wrote:

>
>
> John Marshall wrote:
r, when I show the resultant rowset in a grid the transaction file employee number is morphed into last, fist names. If I define another column in the rowset to show the empno from the employee db, there seems to be a bug. The first record's employee nu
mber always shows in front of the first two records, and the subsequent employee names and matching empno's are correct ... it's just that record two always receives/shows record one's data.[color=darkred]
>
> Looks like you trying to use the lookupRowset for something it was not
> intened for. I think you need to use the table as a masterRowset/fields
> link instead. The code would look something like:
>
> this.query1 = new query()
> with(this.query1)
> sql = "Select * from primary.dbf"
> active = true
> endwith
>
> this.query2 = new query()
> with(this.query2)
> sql = "Select * from employee.dbf"
> active = true
> endwith
>
> with (this.query2.rowset)
> indexname = "EmpNo"
> masterRowset = parent.parent.query1.rowset
> masterFields = "EmpNo" // name of field in query1.rowset
> endwith
>
>
> --
> Todd Kreuter [dBVIPS]

Todd Kreuter

2005-06-27, 8:23 pm

john marshall wrote:
>
> Funny thing - perhaps a clue .... the names lookup and match when in the designer, but when I run it, they all are just the first matching record's data across all.

Not sure whats happening. Sounds like the link is getting broken at some
point.

Following is a sample I put together in response to an early reply, in
case it helps.

--
Todd Kreuter [dBVIPS]

*** Copy and paste into one file ***

set database to

if NOT _app.databases[1].tableExists("Primary")
create table "Primary" (;
EmpNo character(2),;
Item character(10))

insert into "Primary" values("01", "Red")
insert into "Primary" values("02", "Green")
insert into "Primary" values("03", "Blue")
insert into "Primary" values("01", "Sun")
insert into "Primary" values("02", "Land")
insert into "Primary" values("03", "Water")

endif

if NOT _app.databases[1].tableExists("Employee")
create table "Employee" (;
EmpNo character(2),;
Name character(10))

insert into "Employee" values("01", "Larry")
insert into "Employee" values("02", "Curley")
insert into "Employee" values("03", "Mo")

use employee exclusive

index on EmpNo tag EmpNo

use

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

class GridMRForm of FORM
with (this)
height = 16.0
left = 44.8571
top = 0.0
width = 48.1429
text = ""
endwith

this.QUERY1 = new QUERY()
this.QUERY1.parent = this
with (this.QUERY1)
left = 20.0
top = 14.0
sql = 'select * from "Primary.dbf"'
active = true
endwith

this.QUERY2 = new QUERY()
this.QUERY2.parent = this
with (this.QUERY2)
left = 29.0
top = 14.5
sql = 'select * from "Employee.dbf"'
active = true
endwith

with (this.QUERY2.rowset)
indexName = "EmpNo"
masterRowset = form.query1.rowset
masterFields = "EmpNo"
endwith

this.GRID1 = new GRID(this)
with (this.GRID1)
dataLink = form.query1.rowset
columns["Column1"] = new GRIDCOLUMN(form.GRID1)
columns["Column1"].dataLink = form.query1.rowset.fields["empno"]
columns["Column1"].editorType = 1 // EntryField
columns["Column1"].width = 7.1429
columns["Column2"] = new GRIDCOLUMN(form.GRID1)
columns["Column2"].dataLink = form.query2.rowset.fields["name"]
columns["Column2"].width = 14.2857
columns["Column3"] = new GRIDCOLUMN(form.GRID1)
columns["Column3"].dataLink = form.query1.rowset.fields["item"]
columns["Column3"].editorType = 1 // EntryField
columns["Column3"].width = 14.2857
with (columns["Column1"].headingControl)
value = "EmpNo"
endwith

with (columns["Column2"].headingControl)
value = "Name"
endwith

with (columns["Column3"].headingControl)
value = "Item"
endwith

height = 8.0
left = 2.0
top = 1.0
width = 42.0
endwith

this.rowset = this.query1.rowset

endclass

Sponsored Links