InfoPath Dev

hi

i want to populate a dropdown box based on a selection made in another dropdown. however it doesnt seem striaght forward.

dropdown 1 is a list of departments and comes from a sql server database connection. i display department name (but sql server db also has a department ID field)

dropdown 2 - rather than showing all staff members, id prefer to show those that belong to the department chosen in dropdown 1. dd2 is also populated from a sql server connection, displaying staff member name.

what i want is dd2 to have a sql condition to show staff members where department table (department id) = staff table (department id) but i cant seem to access this info via a rule or a filter...

is this possible in infopath? or do i have to do something in sql server with a new connection? 

Yes, this is possible. It's called cascading dropdowns, so you want to search for that term. There are lots of previous discussions about this; I found this thread: http://www.infopathdev.com/forums/t/13940.aspx. I've seen some good tutorials, just search for cascading dropdown in Google. Good luck!

ok i found out how to do it as long as i have the EXACT same value from dropdown 1 in the table feeding dropdown 2 (ie the value displayed in the dropdiwn)

i could not seem to utilise the foreign key (departmentID) to get a match as its asking to match the value in dd1, i cant seem to get at the ID...