grid with multiple recordsources for different columns 1

[ahaws]

Hi all-
Have grid with 5 columns. Column 1 and 2 are recordsourced to a table called bkpeople. Column 3,4,5 are recordsourced to bkmaster.
I do a search based on a name in bkpeople.
When the name is found ( or not - set exact is off - should show closest match), I want to show information from both tables in the grid.

The reason why - information from both tables are needed to decide which record to choose.

Not sure how to do this...or if it is even possible.
Any suggestions?
Thanks
Angie

 

[ahaws]

Ok - I found the SET RELATION command that should correspond all records in the child table (bkmaster) to that of the parent table (bkpeople)

How do I show these two different record sources in the grid?
Thanks

 

[DSummZZZ]

Use a query on the related records and use that as the record source.



-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[ahaws]

I tried the query but took so long to pull info up...like 20 sec...not acceptable.

is there any way to populate a grid through a do while loop?

Im asking because I 'am rewriting a program that was written with Fox 2.6a and I want to simulate a BROWSE WINDOW...with a SET RELATION TO between the two tables...that has a do while loop such as:


DO WHILE m.list = .T.
m.itemnr = m.itemnr + 1
DO CASE
CASE INLIST(accessby,'D','I')
@ ROW(),2 SAY bkmaster.casenumber
tempname = IIF(bkpeople.is_busname = .F.,ALLTRIM(bkpeople.lname) + ;
', ' + ALLTRIM(bkpeople.fname) + ' ' + ;
ALLTRIM(bkpeople.mname),(bkpeople.fname) + ;
bkpeople.mname + bkpeople.lname)
tempname = IIF(LEN(tempname) > 35,SUBSTR(tempname,1,35), ;
tempname + SPACE(35-LEN(tempname)))
IF accessby = 'D'
@ ROW(),15 SAY tempname COLOR N/W*
@ ROW(),51 SAY IIF(!EMPTY(bkpeople.ssn), ;
TRANSFORM(bkpeople.ssn,'@R ###-##-####'), ;
TRANSFORM(bkpeople.ein,'@R ##-#######'))
ELSE
@ ROW(),15 SAY tempname
@ ROW(),51 SAY IIF(!EMPTY(bkpeople.ssn), ;
TRANSFORM(bkpeople.ssn,'@R ###-##-####'), ;
TRANSFORM(bkpeople.ein,'@R ##-#######')) COLOR N/W*
ENDIF
@ ROW(),64 SAY SUBSTR(DTOC(bkmaster.filing),1,6) + ;
SUBSTR(DTOC(bkmaster.filing),9,2)
@ ROW(),74 SAY bkmaster.bkstatus
SKIP
IF EOF()
GO BOTTOM
m.list = .f.
LOOP
ENDIF
IF m.itemnr = 20
SKIP -1
m.list = .F.
ENDIF
ENDDO

 

[ahaws]

I realize the endcase statement is missing....

And yet another question.
If say column2.text1.value in a grid is the result of concatenating 3 fields (bkpeople.lname, bkpeople.fname,and bkpeople.mnamee) - I can't really give a controlsource to that column...since if I do that it will only show the one field.
If I create a variable and set the controlsource for that column to that variable - ALL records that show in the grid have the same value for column2.

This is making my day miserable.
thanks in advance.
angie

 

[darrellblackhawk]

What you are trying to accomplish is relatively simple.

Just set the controlsource of the various columns to the
table.field you want at either set up time or within
some other method.

The following uses all of the tables in the testdata.dbc
(database) which comes with the samples files.

Caution: Be careful if you change any of the values as
you may mess up future relations.

You'll notice that the last column (column6) of the top
grid (grdMultiSource) is the concatenation of Emp_Id,
First_Name, and Last_Name of the employee table.

Darrell

LOCAL oForm
oForm = CREATEOBJECT("clsMultSourceGridForm")
oForm.SHOW()
READ EVENTS

DEFINE CLASS clsMultSourceGridForm AS FORM
  DOCREATE = .T.
  AUTOCENTER = .T.
  DATASESSION = 2
  WIDTH = 800
  HEIGHT = 600
  CAPTION = "Multi recordsource grid"

  ADD OBJECT lblMultiSourceGrid AS LABEL WITH ;
    FONTNAME = "Times New Roman", ;
    FONTSIZE = 12, ;
    LEFT = 10, ;
    TOP = 10, ;
    CAPTION = "Multi Source Grid", ;
    AUTOSIZE = .T.

  ADD OBJECT grdMultiSource AS GRID WITH ;
    FONTNAME = "Times New Roman", ;
    LEFT = 10, ;
    TOP = THIS.lblMultiSourceGrid.TOP + THIS.lblMultiSourceGrid.HEIGHT + 4, ;
    HEIGHT = THIS.HEIGHT - 348, ;
    WIDTH = THIS.WIDTH - 20, ;
    DELETEMARK = .F., ;
    COLUMNCOUNT = 6, ;
    column1.CONTROLSOURCE = "Orders.Order_Id", ;
    column2.CONTROLSOURCE = "Orders.Order_Date", ;
    column3.CONTROLSOURCE = "Orders.Order_Amt", ;
    column4.CONTROLSOURCE = "Customer.Cust_Id", ;
    column5.CONTROLSOURCE = "Customer.Company", ;
    column6.CONTROLSOURCE = "padr(allt(Employee.Emp_Id),6,' ')+allt(Employee.First_Name)+' '+allt(Employee.Last_Name)"


  ADD OBJECT lblOrderItems AS LABEL WITH ;
    FONTNAME = "Times New Roman", ;
    FONTSIZE = 12, ;
    LEFT = 10, ;
    TOP = THIS.grdMultiSource.TOP + THIS.grdMultiSource.HEIGHT + 16, ;
    CAPTION = "Order Items", ;
    AUTOSIZE = .T.

  ADD OBJECT grdOrderItems AS GRID WITH ;
    FONTNAME = "Times New Roman", ;
    LEFT = 10, ;
    TOP = THIS.lblOrderItems.TOP + THIS.lblOrderItems.HEIGHT + 4, ;
    HEIGHT = 130, ;
    WIDTH = THIS.WIDTH - 20, ;
    RECORDSOURCE = "OrdItems", ;
    DELETEMARK = .F.


  ADD OBJECT lblProducts AS LABEL WITH ;
    FONTNAME = "Times New Roman", ;
    FONTSIZE = 12, ;
    LEFT = 10, ;
    TOP = THIS.grdOrderItems.TOP + THIS.grdOrderItems.HEIGHT + 16, ;
    CAPTION = "Products", ;
    AUTOSIZE = .T.

  ADD OBJECT grdProducts AS GRID WITH ;
    FONTNAME = "Times New Roman", ;
    LEFT = 10, ;
    TOP = THIS.lblProducts.TOP + THIS.lblProducts.HEIGHT + 4, ;
    HEIGHT = 56, ;
    WIDTH = THIS.WIDTH - 20, ;
    RECORDSOURCE = "Products", ;
    SCROLLBARS = 1, ;
    DELETEMARK = .F.

  PROCEDURE grdMultiSource.INIT
    WITH THIS
      .column1.header1.CAPTION = "Order ID"
      .column2.header1.CAPTION = "Order Date"
      .column3.header1.CAPTION = "Order Amt"
      .column4.header1.CAPTION = "Customer ID"

      .column5.WIDTH = 200
      .column5.header1.CAPTION = "Customer Name"

      .column6.WIDTH = 220
      .column6.header1.CAPTION = "Emp_Id & First_Name & Last_Name"
    ENDWITH
  ENDPROC

  PROCEDURE LOAD
    SET CENT ON
    SET TALK OFF
    SET DELETED ON

    OPEN DATABASE _samples+"\data\testdata"

    USE Orders ORDER Order_Id IN 0 SHARED
    USE Customer ORDER Cust_Id IN 0 SHARED
    USE Employee ORDER Emp_Id IN 0 SHARED
    USE OrdItems ORDER Order_Id IN 0 SHARED
    USE Products ORDER Product_Id IN 0 SHARED

    SELECT Orders
    SET RELATION TO Cust_Id INTO Customer ADDI
    SET RELATION TO Emp_Id INTO Employee ADDI
    SET RELATION TO Order_Id INTO OrdItems ADDIT

    SELECT OrdItems
    SET RELATION TO Product_Id INTO Products ADDIT

    SELECT Orders
  ENDPROC

  PROCEDURE DESTROY
    CLEAR EVENTS
    CLOSE DATABASE
  ENDPROC

ENDDEFINE

 

[ahaws]

Hi Darrell-
The concatentation works as you have shown, although -
Now all the records within the grid have the same persons name. the other columns in the grid are different - with different data for each record, but the name of the current record in bkpeople shows for all.

any other suggestions?
thanks

 

[ahaws]

Not sure if the same name is showing now, because the frid seems to scroll to one record showing only...no way to see above.
This stinks

 

[ahaws]

This is what I have in a 'find.click()' button on page1 in a pageframe1 with 2 pages:


Set Talk Off
Set Exact Off
thisform.debtorname = allt(thisform.pageframe1.page1.txtname.value)
Select bkmaster
Set Order To casenumber
Select bkpeople
Set Relation To casenumber Into bkmaster

Set Order To debtor
If !Empty(Thisform.debtorname)
Set Exact Off
Seek(Alltrim(Thisform.debtorname))
Thisform.pageframe1.ActivePage = 2
endif

Page 2 contains the grid.
In the INIT() of the grid:

With Thisform.pageframe1.page2.grid1

.RecordSource = "bkmaster"
.ColumnCount = 5
.column1.ReadOnly = .T.
.column1.ControlSource = "bkmaster.casenumber"
.column1.header1.Caption = "Case #"
.column1.Width = 150

.column2.ReadOnly = .T.
.column2.Width = 200
.column2.Sparse = .T.

.column2.CONTROLSOURCE = "allt(bkpeople.fname)+ ' ' + allt(bkpeople.mname)+' '+allt(bkpeople.lname)"
.column2.header1.Caption = "Debtor Name"

.column3.ReadOnly = .T.
.column3.Width = 100
If !Empty(bkpeople.ssn)
.column3.ControlSource = "bkpeople.ssn"
Else
.column3.ControlSource = "bkpeople.ein"
Endif
.column3.sparse = .t.
.column3.header1.Caption = "SSN/EIN"

.column4.ReadOnly = .T.
.column4.Width = 100
.column4.ControlSource = "bkmaster.filing"
.column4.header1.Caption = "BNK Filed"
.column4.Format = "@d"

.column5.ReadOnly = .T.
.column5.Width = 100
.column5.ControlSource = "bkmaster.bkstatus"
.column5.header1.Caption = "Status"



Endwith


If I enter "L" into the search, I want to see ALL RECORDS where bkpeople.lname begins with an "L" in the grid. As it is now, it is only showing 1 record, that record being a record that has "L M A" as name...so it is only showing the first record it finds with that criteria. How would I show all records in the bkpeopl table, but start the grid at the "L" records?
Three days Ive been on this stinkin thing.
Thanks in advance.
Angie

 

[darrellblackhawk]

I think I'd base the recordsource of the grid on a
temporary cursor which contains all of the fields required.

I'd do this, since you mentioned early on that SQL select
statements were taking too long to populate a table/cursor.

Also, because you seem to be viewing the actual underlying
tables you'll see all records unless you set a filter.

The problem with setting a filter is, if there are a large
number of records, and/or you are running on a network,
performance can be seriously degraded.


Below is a slight modification of your search code.

I noticed you weren't testing whether the user entered a valid "Debtor name."

( Just a first stab... )

Darrell

IF !EMPTY(THISFORM.debtorname)
  LOCAL cExact, cDebtorName

  cExact = SET("Exact")
  SET EXACT OFF

  cDebtorName = ALLTRIM(THISFORM.debtorname)

  IF SEEK(cDebtorName)
    * cDebtorFields is whatever fields your index is based on
    SCAN WHILE LEFT(cDebtorFields,LEN(cDebtorName))==cDebtorName
      * Populate cursor here

    ENDSCAN

    THISFORM.pageframe1.ACTIVEPAGE = 2

  ELSE
    Messagbox("Debtor not found!")
  ENDIF

  SET EXACT &cExact
ENDIF

 

[ahaws]

Thanks Darrell-
If the name is not valid, It should bring to first "near" match...Say "Long" isn't found, but Lonie is...will show lonie.

Thanks so much - all is finally working.
Angie

 

[darrellblackhawk]

Glad you got it working!

Here is another slightly modified version of the above.

Darrell

If !EMPTY(THISFORM.debtorname)
  Local cExact, cNear, cDebtorName, bFound

  cExact = SET("Exact")
  Set EXACT OFF

  cNear = SET("NEAR")
  Set NEAR ON

  cDebtorName = ALLTRIM(THISFORM.debtorname)

  bFound = SEEK(cDebtorName) && Assign in case you want to test

  * cDebtorFields is whatever fields your index is based on
  Scan WHILE LEFT(cDebtorFields,LEN(cDebtorName)) ==cDebtorName .or. !bFound

    If bFound
      * Populate cursor here with records that match search criteria
    Else
      * Only populate cursor with current record.
      * - Near is set on, and search term wasn't found! -
      Exit
    Endif
  Endscan


  * I assume you are performing a refresh
  * in the pageframe's Activate() method
  Thisform.pageframe1.ACTIVEPAGE = 2

  * --> Added code to inform user the displayed information
  * is a nearest match. i.e. Debtor searched for was not found
  Thisform.pageframe1.lblNearestMatch.Caption = ;
    iif(!bFound,"Nearest match is displayed","")

  Thisform.pageframe1.lblNearestMatch.visible = !bFound
  * <-- End added code

  Set EXACT &cExact
  Set NEAR  &cNear
Endif