Filtered Dropdown list

[spyzed]

Hi,

I want to create 2 tables with a 1-1 relationship. I call the tables Customers, and Rooms. Customers and Rooms are connected by a databasejoin list. When I create a new customer, Rooms will be a dropdown option where I can assign a room to a customer. but is a room is selected by another customer on the customer list, that room will not appear in this dropdown for this customer. Any idea how I can do that?

Thanks in advance!

 

[cheesegrits]

You'll have to use a WHERE clause in the join element, something like ...

WHERE {thistable}.id NOT IN (SELECT room FROM customers WHERE id <> '{rowid}')

Leave the {thistable} and {rowid} placeholders exactly as shown. Change the other field and table names to match.

So the inner select returns all rooms currently selected in the customers table, except for the one for this row (which will be blank if this is a new row, but that shouldn't hurt). The WHERE then only selects rooms not in that set of rows.

-- hugh