How do I display related records to a form?

I am trying to display the related record information to a form when it is displayed.

I have a window with a table and a button to modify the record. When the user clicks on the button the form is displayed and the main record is displayed. But now I want to also display the related table's data (CONTACT & COMPANY).

The record that is displaying is REQUEST. I want to show the name of the requester (contact) and their company name on the form, also. There is a contact_id and company_id in the REQUEST table.

I tried changing the CASE BROWSE section to ... (but it did not work)

FileToScreen()
BTN_Button1..State = Grayed
HReadFirst(company)
FileToScreen(WIN_Request_Form, company)


Here is the code that is in the Global Declaration of the form window:
(This does display all the data from the REQUEST table)

PROCEDURE Form(CallMode="Browse",CallingPK = 0)
SWITCH Upper(NoSpace(CallMode))
CASE "NEW"
HReset(requests)
FileToScreen(WIN_Request_Form)
BTN_Modify..State = Grayed
CASE "BROWSE"
FileToScreen()
BTN_Button1..State = Grayed

OTHER CASE

END

Hello Dan

I presume this is a list of contacts for the company rather than a single related item ?
In either case you need to retrieve the contact records based on the company_id being the foreign key in the child contacts table.
For a single entry:
Hreadseekfirst(Contacts,CompanyID,Company.CompanyID)
If Hfound(Contacts)
FileToScreen(Form Window Name,Contacts")
//if the contact controls on the form are linked back to the contact file, this will fill them
else
//gray out the contact fields on the form
Contactname..state = grayed
end

For a table entry:
Either use a query, a view, a file table or a memory table in a read loop to fill the table
HFilter(Contacts,CompanyID,Company.CompanyID)
Hreadfirst(Contacts,CompanyID)
While Not Hout(Contacts)
Tableadd(ContactsTable,ContactsPK+tab+Contacts.Name+Tab+.....)
Hreadnext(Contacts,COmpanyID)
End

The File table using a filter is the quickest but query or views are also very quick to fill and all are less coding less coding than the read loop. There is a view wizard that works like the query wizard. If you use a view and the table structure matches the file structure you can use FileToMemoryTable() to fill the Contacts table

This code could go in the "Browse" section of the initial Switch statement and you could gray out the table or make it invisible in the "New" section

Regards
Al
















Edited 1 time(s). Last edit at 06/03/2009 12:16AM by Al.

Hi Dan,

Generally I would use queries to display linked data and this allows you to change the data display of linked tables (one or more) when you select a row in te primary table. For example -



In this project I use the following code to display the linked tables in the ROW SELECTION area of the primary table -

gRecNum=Table_ProjectQuery6.ProjectID
HExecuteQuery(DetailsQuery,hModifyFile,gRecNum)
TableDisplay(Table_DetailsQuery6)
HExecuteQuery(ActionsQuery,hModifyFile,gRecNum,gTaskNum)
TableDisplay(Table_ActionsQuery6)

This gives a result like the following -



HTH

Milton

I don't think I did a good job explaining my situation. Here is a little more detail

I have Window #1 (request table) all requests

qty part# mfr company contact

100 ABC123 TI IBM BOB
200 XYZ987 TI HP JACK
300 MMM555 TI XXX SUE

Then I have a VIEW button which take you to a new window (WINDOW #2) which displays 1 request only but I want to show much more detail for the request and it's related company and contact. In the related company and contact tables only 1 record should be displayed as only 1 person at a 1 company has given us this request.

In other words, BOB at IBM gave a request for 100pcs of ABC123 by TI

Currently, when I open the FORM window (displaying the 1 request) it shows all the data for the record including the CUSTOMER_ID & CONTACT_ID which are stored in the record of the request but I cannot get the actual company name (IBM) and contact name (BO to display in this window.

currently looks like this:

requestid : 1
customerid : 344 company_name : blank (should display company.company_name)
contactid :124 contact_name : blank (should display contact.first_name)

The requestid, customerid, and contactid are all fields in the request table.

All the fields from the request table display because of this code

CASE "BROWSE"
FileToScreen()
BTN_Button1..State = Grayed


tried changing it to

CASE "BROWSE"
FileToScreen()
HReadSeekFirst(contacts,CompanyID,company.CompanyID)
IF HFound(contacts)
FileToScreen(WIN_Request_Form,contacts)
BTN_Button1..State = Grayed

but that did not display the company.company_name or contact.first_name

not sure what I am missing or doing incorrectly ...

I got it !!!!

Al your example was correct I was just not placing the correct parameters (file, field, field) after the HReadSeek command .

This works and allows me to view fields from both the REQUEST table and CONTACT table.

CASE "BROWSE"
FileToScreen()
HReadSeekFirst(contacts,contact_id,requests.contact_id)
IF HFound(contacts)
FileToScreen(WIN_Request_Form,contacts)
BTN_Button1..State = Grayed
END

Thanks again ... At this rate I might even get to take my training wheel off soon!!! ... maybe!