Hash Variables

[Fundi]

Hi

Is there a way to use the value of a field in the underlying table of a form or the value of an object like one can use '#id#'. With other words:
lets say we have a Curtomer Table and a Job Table. The job Table saves the Id of the Customer in Job_Customer_ID. Now when dislaying the Form with the Job I like to display related customer information on the Job Form from the Customer table. the select statement would look somewhat like this:
select Customer From Tbl_Customer where Customer_ID = '#Job_Customer_ID#'

My only problem is that it does not work . How can I get the value of Job_Customer_ID?

I hope this makes sense.
Fundi

[admin]

Hi.


instead of

Code: Select all

select Customer From Tbl_Customer where Customer_ID = '#Job_Customer_ID#'

Because you cant use #Job_Customer_ID#

try

Code: Select all

select Customer From Tbl_Customer 
INNER JOIN Job_Customer ON Job_Customer_ID = Customer_ID
where Job_ID = '#id#'

You can still do what you want with #id# but it will need an inner join

I hope this helps

Steven

[Fundi]

Hi Steven

Thanks for your input and help. this has been invaluable. When I finish my database I will make it available here as a example for others.
Your explanation although spot on caused me to puzzle for the whole night. I tried to use the naming convention in my post but it is very confusing. So for others who might come after me the code is easier understood like this (correct me if I am wrong):

Code: Select all

select Name from Tbl_Customers inner join Tbl_Jobs on Tbl_Customers.ID = Tbl_Jobs.Customer_ID Where Tbl_Jobs.ID = '#id#'

This works now. Thank you very much.

Now I got one more question on this. the display field is a field of on line. The code is able to fetch more than one column to display. Can this be applied to a text area? What ai like to do is show the relevant Customer name, Address, contact details in a read only field on my Jobs form. This way the user can see the jobs of a customer and see the customer data at the same time.
Thanks again
Fundi

I am learning by the day.

[admin]

You will need Customer name, Address and contact details each in its own display field

eg.

Code: Select all

select `Customer name` from Tbl_Customers inner join Tbl_Jobs on Tbl_Customers.ID = Tbl_Jobs.Customer_ID Where Tbl_Jobs.ID = '#id#';

select `Address` from Tbl_Customers inner join Tbl_Jobs on Tbl_Customers.ID = Tbl_Jobs.Customer_ID Where Tbl_Jobs.ID = '#id#';

select `contact detail` from Tbl_Customers inner join Tbl_Jobs on Tbl_Customers.ID = Tbl_Jobs.Customer_ID Where Tbl_Jobs.ID = '#id#';

Steven