Two list boxes, options in 2nd box change based on first

[anorman]

Hi,

I hope this question has not already been answered previously. If so, I apologize. I looked and didn't see anything like it.

I have a form with 2 lookups on it. The first lookup does an SQL query for all the "clients" in the client table.

The second lookup will be for a list of Contacts associated with the "client" selected above. This is a different table, with a list of all contacts in the database with a key to which "client" the contact belongs.

The problem is, I am not sure how to specify the SQL query (I think I need to mix JavaScript with the query to see what the value of box #1 is before initiating the SQL query), but I'm at a loss to see how that is done.

[anorman]

Sorry, that post was a little cryptic.. I have no problem with the actual SQL query to give me the proper answer, I just don't know how to mix the JavaScript required to "find the current client" inside the SQL query box in NuBuilder..

[admin]

anorman,

You can use a hash variable on the WHERE clause of the second lookup.

eg. SELECT contact_id, con_code, con_name FROM contact WHERE con_customer_id = '#selected_customer_id#'

I hope this helps.

Steven

[anorman]

Hi Steven,

I think I tried what you suggested, but either I tried it incorrectly, or I didn't explain the problem very well in the first shot..

here is what I tried:

select cn_id, cn_name from contact where cn_cl_id = '#selected_tk_cl_id#' (I also tried it without the selected, as the actual field name on the form is tk_cl_id)

Let me give specifics and maybe you can point out my error (sorry)...

The form has 2 dropdowns, the 2nd dropdown options should change upon the choice made in the first dropdown

The table the form is working on is the Ticket [tk] form

The 2 dropdown field names are

tk_cl_id (Ticket client ID)
tk_cn_id (Ticket contact ID)

each refers to a primary key in separate tables (client [cl] and contact [cn])

In the contact table, there is a foreign key to the client table ID

So, when I add a new record the first dropdown list would have Customer1, Customer2, Customer3 .... in it

As soon as I select Customer2 the 2nd dropdown list should now have all the contacts in the database that are associated with Customer2

Sorry, I speak English as my first language, honest <haha>..

- Andre

[anorman]

O, one other thing that is confusing me:

If I replace the hash code with a hardcoded value in the SQL select (select cn_id, cn_name from contact where cn_cl_id = 2) [instead of cn_cl_id = '#selected_tk_cl_id#' ] the 2nd dropdown list does indeed contain the proper data (of course, now it is hardcoded so it is of little use, but the select does work properly.. I guess I don't understand the proper use of hash codes <sigh>

[admin]

Andre,

I thought you were using Lookups instead of Drop Downs.

Drop Downs are static once an Edit Form is loaded.

Lookups on the other hand will requery everytime you use them.

Lookups are good for whenever your list of clients or contacts grows to over 30 records as dropdowns start to get a bit long and ugly and the list is inside the page's html, theoretically making it slightly longer to load.


Steven

[anorman]

Ahh.. Got you now..

Thanks, I will change them to lookups..

- Andre

[admin]

Cool!