Combine two tables in a browse?

[Nathan Zeiset]

I think I'm trying to do something impossible, but it won't hurt to ask. Is there a way to combine a parent record and child records in a browse or list as separate individual rows? Let's say fields 1, 2, and 3 in Parent table are identical in structure to fields 1, 2, and 3 in Child table, and they are linked in a one-to-many relationship. I would like a list or browse showing the following:

Row 1 (Parent record)	Field1	Field2	Field3
Row 2 (Child record 1)	Field1	Field2	Field3
Row 3 (Child record 2)	Field1	Field2	Field3
Row 4 (Child record 3)	Field1	Field2	Field3

I know that I can create a new table by copying the Parent table and then using an Append operation to add the Child table to it, and then base the browse on that, but if there's any way to do this dynamically without creating a new table first, I would be very interested in learning about it.

Thanks!
Nathan

[MoGrace]

Re: Combine two tables in a browse?

Take a look at mapped tables and see if that does what you want.

[Nathan Zeiset]

Re: Combine two tables in a browse?

That doesn't seem to accomplish what I'm looking for. The linked table appears to be similar to a one-to-one set. The browse needs to show the record from the Parent table as a separate row from the records from the Child table.

Nathan

[johnkoh]

Re: Combine two tables in a browse?

Nathan,

Make a temporary table and
1) Find the data and Import(transfer) the records with some of the Reference_Group, display_Sequence from Two tables.
2) Play with the Temp table
If you know the xbasic, will do easy to transfer the records to Temp table.

[Al Buchholz]

Re: Combine two tables in a browse?

I would like a list or browse

And then do what with it?

A list may be an answer, but it depends on what you want to do with it.....

How many users?

[Nathan Zeiset]

Re: Combine two tables in a browse?

John,

I'm not sure what the purpose of the temp table would be. Do you mean to base the browse on the new temp table?

Al,

I have a members table and a dependents table. I would like to have a browse that lists a member and his dependents in the same browse (or list). Each member or dependent would have multiple office visits associated with them. When the user selects the member or one of the dependents in the list, the associated visit records would be displayed in another browse.

Nathan

[johnkoh]

Re: Combine two tables in a browse?

Nathan,

I'm not sure what the purpose of the temp table would be. Do you mean to base the browse on the new temp table?

Make a new table which will hold the Transfer data and you have to manipulate with xbasic.

If you want to see with Browse, there are no way to show as your layout(post #1).

You can do with xDialog ListBox. - still get the data from Parent and child tables.

[NicholasWieland]

Re: Combine two tables in a browse?

Create a view and use it for the grid.

[Stan Mathews]

Re: Combine two tables in a browse?

Create a view and use it for the grid.

On the desktop with dbf tables? Have an example to share?

[Tom Cone Jr]

Re: Combine two tables in a browse?

I agree with Al. Curious as to purpose for the resulting browse or list. If it's simply to display data consider using a previewed report.

[Nathan Zeiset]

Re: Combine two tables in a browse?

I'm trying a different approach to displaying the data I want. If I decide to come back to this idea, I'll repost here.

Thanks to everyone who chimed in!
Nathan

[Al Buchholz]

Re: Combine two tables in a browse?

Al,

I have a members table and a dependents table. I would like to have a browse that lists a member and his dependents in the same browse (or list). Each member or dependent would have multiple office visits associated with them. When the user selects the member or one of the dependents in the list, the associated visit records would be displayed in another browse.

Nathan

Then as Tom Cone described, I'd use a report to display the data and have a linked field with an onclick event that you would click on to open a form/browse to the proper data for maintenance.

[MoGrace]

Re: Combine two tables in a browse?

Al,

I have a members table and a dependents table. I would like to have a browse that lists a member and his dependents in the same browse (or list). Each member or dependent would have multiple office visits associated with them. When the user selects the member or one of the dependents in the list, the associated visit records would be displayed in another browse.

Nathan

You may need to normalize your tables differently. To link members to dependents you need a common field they all share - like a CLIENTID field. But you will also need a unique MEMBID to refer to them individually in order to show visits. If you think of your client member as the head of household, then you can add a logical field to the member table to show TRUE for that member, and false for the others. A separate CLIENT table is where you generate the unique CLIENTID when a new client is added that can also act as the header table for a set.

Members:
CLIENTID  MEMBID  HOH  NAME
12345       7880       .T.     DOE, JOHN
12345       7881       .F.     DOE, JANE
12345       7882       .F.     DOE, BILLY BOB

Visits:
DATEIN        MEMBID  KEYLINK (auto increment)
12/15/2016  7881       10000014
03/01/2017  7881       10000123

CLIENT
  |==MEMBER
     |==VISIT

[Mike Wilson]

Re: Combine two tables in a browse?

Row 1 (Parent record)   Field1     Field2       Field3
Row 2 (Child record 1) 	Field1     Field2 	Field3
Row 3 (Child record 2) 	Field1     Field2 	Field3
Row 4 (Child record 3) 	Field1     Field2 	Field3

Something isn't quite right in what you are telling us. Is the above lines 2-4 supposed to be the same child #1 with multiple records or is it supposed to be 4 separate people and should be:

Row 1 (Parent record)       Field1 	Field2 	Field3
Row 2 (Child [COLOR="#FF0000"][SIZE=3]1[/SIZE][/COLOR] record )     Field1 	Field2 	Field3
Row 3 (Child [COLOR="#FF0000"][SIZE=3]2[/SIZE][/COLOR] record )     Field1 	Field2 	Field3
Row 4 (Child [COLOR="#FF0000"][SIZE=3]3[/SIZE][/COLOR] record )     Field1 	Field2 	Field3

with the multiple "visits" in another table?

[MoGrace]

Re: Combine two tables in a browse?

In my example, John Doe is the 'member' and Jane and Billy are the 'dependents' - they are all records from the same members table.