Field of SPType="Lookup"

May 17, 2011 at 7:41 PM
Edited May 17, 2011 at 7:42 PM


I'm trying to get a grasp on how this works. If I include a field of SPType="Lookup" in a given list, that field doesn't actually have a corresponding column in the table, correct? They way I see it functioning now is that the lookup field is missing from the table that corresponds to the list, and a mapping of ListItemIDs is merely represented in the association table. So basically I'm going to have to join the two tables on these ListItemIDs to get a view of the data as it appears in the list that resides in sharepoint.

Is my understanding of this correct?



May 17, 2011 at 8:31 PM

Well a lookup field can become an association by setting AssociationName and AssociatedList (it's possible to omit these attributes and have the lookup value slam as a delimited string into a column into the type's table), but I'm assuming you've done that and that is why the value of the lookup column is manifest in the association table.  At that point you're right, in order to return, say, the Title values of both associated items you would join the two relevant tables to the association table.

May 17, 2011 at 9:08 PM
Edited May 17, 2011 at 11:22 PM

Ok cool, thanks for the explanation. I had originally tried setting the SPType="Text" for the List's lookup columns. The resulting data was populated with a string like you said in the form "726;#ThisWouldBeTheFieldsText". So basically the field's text is preceded by the ID, a semicolon, and a pound sign. I don't really need the association as I'd rather just have the table represent the value without having to do any joins. I'll probably just end up using the SUBSTRING() function when I query for the data to remove the unneeded preceding stuff, unless I'm missing something and there is a way to do this via SLAM configuration?

EDIT: It doesn't look like the event handler actually updates the the table containing the lookup field when you edit the value for the item in the source list. association table joins it is then!

BTW I've only been playing with SLAM for a day now, but it seems really terrific. For the last few months I've been querying the AllUserData table to extract info from SP lists to build SSRS reports and as you probably know that makes for complicated, pooorly performing queries, and headaches. Great job on SLAM!