Binding Id's to lookup tables

[JuJu]

I have a form set up to view and submit data to a SQL db.
I would like to submit data to my master table, but look
up possible values in drop down lists from look up tables
(these would be tied to the master db by id fields).

Example: when submitting a new record there is an employee
name field that would be a drop down list looking up the
data from a look up table and when a name is select both
the name and nameid would be submitted to the master SQL
db.

Can this be done easily without writing code?

Thanks!

 

[Steve van Dongen [MSFT]]

I have a form set up to view and submit data to a SQL db.
I would like to submit data to my master table, but look
up possible values in drop down lists from look up tables
(these would be tied to the master db by id fields).

Example: when submitting a new record there is an employee
name field that would be a drop down list looking up the
data from a look up table and when a name is select both
the name and nameid would be submitted to the master SQL
db.

Can this be done easily without writing code?

Why do you want to submit the name and id? That implies that both
your master and lookup tables have both the name and id. That would
be a horrible design IMHO. The name should only be in the lookup
table. If you really want the name and id in both tables the way to
do it which ensures database consistency is to submit only the id to
the master table and have a trigger lookup the appropriate name for
that id and update the name in the master table.

Design a form against the master table. Create a secondary datasource
for the lookup table with id and name, then bind the dropdown list to
that datasource with id as the value and name as the displayed text.
That's it.

Regards,
Steve

 

[JuJu]

That makes sense, but I want users to also be able to
enter a value in the form that may not yet be listed in
the lookup tables. Would it be best just to schedule a
sproc to run every hour and check the master table for new
records that are not in the lookup table - if there are
new records in the master table then it would insert them
into the lookup table and create an id for them? The
lookup tables are being used in the query section of the
form for filtering what data a user wants to see.

Is there a better design?

Thanks,
Heidi

 

[Steve van Dongen [MSFT]]

That makes sense, but I want users to also be able to
enter a value in the form that may not yet be listed in
the lookup tables. Would it be best just to schedule a
sproc to run every hour and check the master table for new
records that are not in the lookup table - if there are
new records in the master table then it would insert them
into the lookup table and create an id for them? The
lookup tables are being used in the query section of the
form for filtering what data a user wants to see.

Is there a better design?

You would use a trigger instead of a scheduled job to keep the data in
the two tables consistent.

Alternatively, it sounds like you should completely get rid of the
lookup table and create a view in its place that just selects the
distinct ids and names from your master table.

Regardless of what you decide do with the lookup table, if you have
the name in the master table, you need a trigger to ensure that if you
update a name, that change is also made in all the other rows in the
master table with that id. The trigger needs to detect an update to
the name and
1. call RAISERROR to reject the change if an updated name conflicts
with another updated/inserted name for the same id
2. update the name in all the other rows in the master table for that
id
3. create an id for new names

See SQL Books OnLine for details on the SELECT statement, views, and
triggers.

Regards,
Steve

 

[JuJu]

-----Original Message-----



You would use a trigger instead of a scheduled job to keep the data in
the two tables consistent.

Alternatively, it sounds like you should completely get rid of the
lookup table and create a view in its place that just selects the
distinct ids and names from your master table.

Regardless of what you decide do with the lookup table, if you have
the name in the master table, you need a trigger to ensure that if you
update a name, that change is also made in all the other rows in the
master table with that id. The trigger needs to detect an update to
the name and
1. call RAISERROR to reject the change if an updated name conflicts
with another updated/inserted name for the same id
2. update the name in all the other rows in the master table for that
id
3. create an id for new names

See SQL Books OnLine for details on the SELECT statement, views, and
triggers.

Regards,
Steve
--
Please post questions to the newsgroup; everyone benefits.
This post is provided "AS IS" with no warranties, and confers no rights
Sample code subject to http://www.microsoft.com/info/cpyright.htm
.

Thanks for your help Steve!