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
Welcome to the nuBuilder forums!
Please register and login to view forums and other content only available to registered users.
Please register and login to view forums and other content only available to registered users.
Hash Variables
-
- Site Admin
- Posts: 2791
- Joined: Mon Jun 15, 2009 2:23 am
- nuBuilder Version: 4.5
- Been thanked: 4 times
Re: Hash Variables
Hi.
instead of
Because you cant use #Job_Customer_ID#
try
You can still do what you want with #id# but it will need an inner join
I hope this helps
Steven
instead of
Code: Select all
select Customer From Tbl_Customer where Customer_ID = '#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#'
I hope this helps
Steven
-
- Posts: 27
- Joined: Sun Apr 17, 2011 10:22 pm
Re: Hash Variables
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):
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.
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#'
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.
-
- Site Admin
- Posts: 2791
- Joined: Mon Jun 15, 2009 2:23 am
- nuBuilder Version: 4.5
- Been thanked: 4 times
Re: Hash Variables
You will need Customer name, Address and contact details each in its own display field
eg.
Steven
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#';