Welcome to the nuBuilder forums!

Please register and login to view forums and other content only available to registered users.

Hash Variables

Post Reply
Fundi
Posts: 27
Joined: Sun Apr 17, 2011 10:22 pm

Hash Variables

Unread post by 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
Site Admin
Posts: 2791
Joined: Mon Jun 15, 2009 2:23 am
nuBuilder Version: 4.5
Been thanked: 4 times

Re: Hash Variables

Unread post by 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
Posts: 27
Joined: Sun Apr 17, 2011 10:22 pm

Re: Hash Variables

Unread post by 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
Site Admin
Posts: 2791
Joined: Mon Jun 15, 2009 2:23 am
nuBuilder Version: 4.5
Been thanked: 4 times

Re: Hash Variables

Unread post by 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
Post Reply