How to combinate two tables in one grid?

[duongduong12345]

Dear all,

I'm new one to VF, please teach me how to combinate two tables in one grid.

I have 2 tables (1 to many relation), anh i have 1 grid for display 1st table, but now i want to add more one column (is the field in second table) into that grid and I made relation in Data Environment alrealy). But when i run the form, the new column doesn't show data in second table, it just show data in first table.

Urgent please.
tks

NCD
duong3101@yahoo.com

 

[Mike Lewis]

One way to do this is to create a cursor. A cursor is simply a temporary table which you can use to populate the grid.

To create the cursor, you need to write a SQL SELECT based on a join of your two tables. The SELECT should specify the columns that you want to appear in the grid, and the join condition between the two tables.

Then, set the grid's RecordSource to the name of the cursor. (The cursor must already exist at the point that you do that.)

Having said that, if the two tables have a 1-to-many relationship, the fields on the "1" side will repeat many times in the grid. I wonder if that is what you want?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[cbasoz]

In the new column you would have a controlsource like:

ChildTable.FieldName

However, when you do that you get only the first row of 'child' table unless you set skip. Setting it then the grid would look ugly having *** for the repeating rows on 'parent'. Since you should never think to enable editing data on such a grid, using a recordsourcetype of 4 and and SQL as recordsourcetype is the way to go IMHO. Something like:

Public oForm
oForm = Createobject('GridSample')
oForm.Show()

Define Class GridSample As Form
  DataSession = 2
  Add Object myGrid As Grid

  Procedure myGrid.Init
    With This
      .RecordSourceType = 4
      TEXT to this.RecordSource noshow pretext 15
select cs.Cust_id,cs.Company,cs.Contact,
 ord.Order_id, ord.Order_date, ord.Order_net
from (_samples+'data\Customer') cs
left join (_samples+'data\orders') ord on cs.cust_id = ord.cust_id
order by cs.cust_id
into cursor crsMyGrid
      ENDTEXT
      .AutoFit()
    Endwith
  Endproc
Enddefine

Probably what you really want is two grids, one showing 'parent' and other 'child' like this one:

Public oForm
oForm = Createobject('GridSample')
oForm.Show()

Define Class GridSample As Form
  DataSession = 2
  Height=600
  Width=800
  Add Object grdCustomer As Grid With ;
    top=0,Left=0,Height=300,Width=800,Anchor=11+64,;
    recordSource = 'Customer'
  Add Object grdOrders As Grid With ;
    top=300,Left=0,Height=300,Width=800,Anchor=14+16, ;
    recordsource = 'Orders',;
    LinkMaster = 'Customer',;
    ChildOrder = 'Cust_id',;
    RelationalExpr = 'Cust_id'

  Procedure Load
    Set Multilocks On
    Use (_samples+'data\Customer')
    Use (_samples+'data\orders') In 0

    CursorSetProp("Buffering",5,'Customer')
    CursorSetProp("Buffering",5,'Orders')
  Endproc
Enddefine

Cetin Basoz
MS Foxpro MVP, MCP

 

[JRB-Bldr]

Since you are new to VFP - Welcome to the community.

Next I generally recommend that individuals who are new to VFP work to build a foundation of understanding from which to move forward.

One reference that I generally recommend for information on basic topics would be the free videos at:

http://www.garfieldhudson.com/FreeVideos.aspx

These videos can be viewed on-line or downloaded.

Another reference would be the variety of excellent VFP books at Hentzenwerke (

http://www.hentzenwerke.com/catalog/zcatalogsubject.htm#visualfoxpro

)

Now on to your specific question....

Grids need to be 'told' what to display.

An extremely simple grid can merely be 'told' to display a table/cursor by defining its RecordSourceType and RecordSource. From that definition it will display what it 'sees'. This simple grid setup will only 'know' about the table which is defined and will not 'know' to display other table contents.

Mike's suggestion of combining both the Parent and Child data tables into a single table/cursor with a SQL Query for grid display purposes is one good approach. Then the grid setup can again be simple, but the grid can easily 'see' a more comprehensive set of records.

A more complex grid can be 'told' column-by-column what to display - again by defining the grid's RecordSource like above but also separately defining the individual column's ControlSource. This approach, while more time consuming to create, will display more specific detail as defined.

One way to utilize this more complex method would be to begin by establishing a 'backward' relation between the Parent and Child tables (Child related into Parent rather than the other way around). This sets things up so that for every Child record 'seen' by the grid, there is an associated Parent record and its fields immediately 'visible'.

Then set the Child table as the Grid RecordSource and go on to define individual column ControlSource as Child fields and Parent fields as needed.

Good Luck,
JRB-Bldr

 

[SnyAc]

On a one-to-many parent/child relationship in a grid you will also need to use the SET SKIP TO command to get the parent/child display in the grid.

Andy Snyder
SnyAc Software Services

http://www.snysoft.com

Hyperware Inc. a division of AmTech Software

http://www.hyperwareinc.com

 

[JRB-Bldr]

SnyAc - actually you do not need to use the SET SKIP command if you use my suggested 'backwards' relation.

NCD - Good Luck,
JRB-Bldr

 

[cbasoz]

jrbbldr,
A 'backward' relation is not the same thing as a 'forward' relation. Consider this:

Customers and their Orders where you have 100 Customers but only 80 of them have Orders

If you create the relation from Customers to Orders, with a set skip on you have all the rows.

If you change the relation backwards and make it Orders - Customers then you might lose some of the rows from Customers.

IMHO ideal solution is using normal Customer-Orders relation (even you don't need the relation and could set it via Grid properties as shown in the sample code above) with two grids.

Cetin Basoz
MS Foxpro MVP, MCP

 

[JRB-Bldr]

Cetin - your example could be accurate if entering Customers without an Order were allowed.

However I can't think of any business that I have been involved with that allows the entry of Customers without with the associated entry of an Order. That was the business rule for how they were defined as a Customer.

Yes, there could be 100 Customers with only 80 OPEN/ACTIVE Orders, but typically the 100 Customers would have 100 and most likely even more Orders - both ACTIVE and NON-ACTIVE.

The 'backward' relation can work just fine for this situation depending on what it is that NCD wants their grid to display.

If only Customer info is desired regardless of the Order status, then why ask about displaying a combination of Order table records combined with the Customer records at all. Just get the Customer info by itself and display it - typically using some display form other than a Grid.

I guess that we will have to wait for feedback from NCD to see what it is that works for them.

NCD - Good Luck,
JRB-Bldr

 

[markros]

I disagree. There may be potential Customers (no orders yet) and still exist as Customers.

PluralSight Learning Library

 

[duongduong12345]

Hi Mike, JRB and all,

Thanks for your support.
I wonder, why there is only one solution as you teach me. It's so complicated one.
In the Foxpro for Windows, very easy for me to do it (First: Make the relationship, Second: Brow Table1.fiels, Table2.fields..). So i think VFP's better than FPW.

But i will try as your solution, and i will discuss with you if there is any new info.

Thanks again.

NCD

 

[JRB-Bldr]

"I wonder, why there is only one solution as you teach me."

What you should have gotten from our discussion above is that there is NOT just one solution.

There are a variety of solutions depending on what you actually need to do.

There may be a method that I feel would be the 'best' solution and, as can be seen from other people's suggestions, there are other methods preferred by them. All of them might work - again depending on what you need to do.

Fortunately the VFP approach doesn't absolutely need to vary too much from the FPW approach. Yes, VFP Forms are put together differently than FPW Screens and the VFP Grids are more complex to provide more flexibility, but they don't have to be used in the fully complex manner - as I suggested above in my "An extremely simple grid" description.

You might get some insight by viewing the video:

http://www.garfieldhudson.com/freevideos/online/lvfp_app_pt3/lvfp_app_pt3.html

Good Luck,
JRB-Bldr

 

[Mike Lewis]

NCD,

In the Foxpro for Windows, very easy for me to do it

Then do the same thing in VFP. If you have a method that worked in FPW, the same method will work in VFP.
The only significant difference is that you are now using a grid rather than a browse. But the tables / cursors / relationships that underlie the grid or browse are the same, which is the important point.

Also, the solutions we proposed need not be complicated. If you are comfortable with SQL SELECT, I suggest you start with my original suggestion of using a cursor. I think you'll find that it's simple, and it'll do what you want. But if it doesn't, we can refine it, or you can try one of the alternatives. At least, make a start.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[cbasoz]

jrbbldr,
I disagree with all my respect. In our business there are potential individuals/companies/institutions that we call Customer. We might have named them wrongly from an English purizm point but that is how we did. And I am sure you actually understood my point. It doesn't need to be Customers-Orders. It could be any tableA - tableB which have a parent - child relation and as it is widely known in database industry a parent having 0 'child' rows is fairly acceptable situation (a child having no parent, although not impossible, is seen second to none - such as temporary unallocated work items).

Cetin Basoz
MS Foxpro MVP, MCP

 

[JRB-Bldr]

markros - I don't want to belabor this discussion too much more - especially since it primarily relates to not much more than terminology.

In most companies, 'customers' who have not yet placed orders are not considered 'CUSTOMERS' they are typically considered 'PROSPECTS' (or potential/prospective customers) who might place an order someday and then become a real 'CUSTOMER'.

The information for these individuals might be put into the same data table as CUSTOMERS, but, regardless of the term used - CUSTOMER or PROSPECT, they are most definitely a different category of 'customer' (one who by definition has NO order yet).
NOTE - If the PROSPECT records were to be kept in a different data table, that would make this discussion moot (we aren't told by NCD).

We have to make some assumptions here since the details of the original question were somewhat vague, but when NCD originally asked their question they wanted to display data from both the ORDERS table and the CUSTOMERS table. Obviously those PROSPECTS would be guaranteed by definition to have no ORDERS record at all so it is likely that that sort of record type from the CUSTOMER table records would be intentionally excluded from the Grid display.

Cetin - you have a valid point about the circumstances regarding the 'generic' TableA and TableB model.
However, when the 'business rules' and content of TableA and TableB are KNOWN factors then the determination can be made if using the 'backward' relationship will work.
Sometimes YES, sometimes NO
When it can be applicable, it is an easy way to accomplish a number of tasks.

Again, these sorts of discussions could go on and on un-necessarily - it may boil down to largely to:
1. our individual interpretation (accurate or not) of NCD's data table content and what they are trying to accomplish with it.
2. and/or our individual development styles

Lets instead just focus on getting NCD what they need to accomplish the task they need.

Good Luck,
JRB-Bldr

 

[duongduong12345]

Dear all,

Thanks for your help.
But now, i solved it already, very easy.

. Make relationship (table1 and table2) in Dataenvironment
. The grid display fields in table1.
. Add new column to grid, bind the table2.field direct to that column in the grid (don't bind table2.field to column.text). that's all.

Good luck
NCD