Lookup lists within List?

Dec 14, 2011 at 4:48 PM

So we have a list of Notes and a lookup attribute of Note Types within the Notes list.  Will SLAM allow for that so I can query like this?

Select * from Notes where NoteType = 'meeting'

Developer
Dec 14, 2011 at 11:01 PM

What you want to do is slam a lookup list as an association.  So in your Notes List you'd configure the Note Type field like

<Field Name="Note Type" Column="NoteTypeID" SPType="Lookup" AssociatedTypeName="Note Types" Required="true"></Field>

 

Of course you'd also slam the Note Types list.

The result then is 2 tables, Notes and NoteTypes, where Notes has a NoteTypeID column that is a foreign key.   Querying against it then is like "select * from Notes join NoteTypes on Notes.NoteTypeID = NoteTypes.ListItemID where NoteTypes.Title = 'meeting'" (assuming you're not using a TypeIdDefinition such that ListItemID is not the primary key column of NoteTypes).

Slamming your lookup as an association is much cleaner and more maintainable.  If you just had a string in a Notes.NoteType column and you wanted to change the Title of your Note Type then all of your Notes would be out of synch.