Lookup equivalent in SQL Server?

[El_Pablo]

Hi,

I'm creating a database in SQL Server since I cannot implement FM in my customer business.

It's been too long since the last time I went in SQL.

How do we create a lookup for a field?

Thanx

[kmtenor]

At the risk of being scolded that this forum is for Filemaker and not for SQL, (and if I understand your question correctly) I believe the following is what you're looking for:

insert into newTable

(tableId,

tableName,

tableAddr1,

tableAddr2,

tableCode)

select

'1',

lookupName,

lookupAddr1,

lookupAddr2,

'test'

from lookupTable

where lookupId = @lookupValue

The above code, using a subselect for the insert, emulates Filemaker's "Auto-Enter Looked Up Value" feature at the field definition level. You would have to replace the constants in the select statement with the values captured from your user.

You could also accomplish the same thing with an update following your insert in your code. Do some google searches for 'insert subselect mssql' and you should find some direction. These are just two of the many ways it could be done.

Hope that Helps.

-Kevin

[El_Pablo]

I know that we are in a FM forum, but since I am too accustomized to FM I forgot how to think SQL.

For the lookup, would it be better to create an insert trigger?

[kmtenor]

That would work, as well. In general, any time you can keep activity on the server-side, you'll improve the database performance, particularly when you're dealing with MSSQL over a network. The 'best' method is always defined by the application you're trying to create, and the environment in which you are operating.

A quick search for 'mssql insert trigger' turned up quite a few options, but the one at the top should suffice as a guide (the site has been reliable and accurate in the past).

http://www.devarticles.com/c/a/SQL-Server/Using-Triggers-In-MS-SQL-Server/

Good Luck!

-Kevin