Lookup field as feature

Question

 

Hi, I'm trying to create a Lookup field (as a feature) that pulls its values from a SharePoint list and have found an online post that describes how to do it, but am having trouble getting it to work. Here's the post:

http://www.developermania.com/newsgroups/item/271181/Tip_on_defining_lookup_field_types.aspx

 

I assumed that JoinColName would be one of the columns in the "where" clause (eg ... where JoinColName = FieldRef), but am having trouble getting it to work. Nothing I've tried works. Can someone out there who's done this explain?

 

Thanks

Answer 1

 

So what do you have setup in your definition?  I am guessing that a fieldname does not match what you think, I would guess that you have the right list GUID here?  If you goto the settings page of your list where the lookup is in, and click on the name of the field you want, at the end of the URL, you should see the name of the column, if you included spaces, it might have _x200_ instead of a space in the name.  You might also want to get a U2U Query tool and run a query against the list, you can check internal column names there as well.

Answer 2

Thanks for your response dkeeling. Here's the contents of the xml file:

 

<Elements xmlns="http://schemas.microsoft.com/sharepoint/">

    <!-- Lookup fields -->

<Field  ID="{A6B25233-A368-452d-B5DE-FF9E85425CB4}"
       Name="Job Family8"
       SourceID="http://schemas.microsoft.com/sharepoint/v3"
       Group="Custom Columns"
       Type="Lookup"
       DisplayName="Job Family8"

       ReadOnly="FALSE"
       Sealed="False"
       List="{E24341CB-176F-45CF-8574-AFEA3AB8FC96}"
       JoinColName="nvarchar3"
       JoinRowOrdinal="0"
       JoinType="INNER"
       ShowField="Job_x0020_Family_x0020_choice"
       FieldRef="Job_x0020_Function3"

</Field>

</Elements>

 

The id in List=... is the id of the list that stores the data values to be shown on the drop down menu. FieldRef has the name of the related field in the list where the lookup is initiated and JoinColName= has the db column name of the field in List=... that should be matched to FieldRef. As I mentioned above, I assumed the lookup would perform as though it were being done like this: select ShowField from List where FieldRef = JoinColName. This is the assumtion I was hoping someone could verify. As I mentioned this and other variations are not working.

Answer 3

Assume:

    List1:                                // data list
            ID="{551A2047-6E01-4F73-A475-ED51FEEB0796}"
           
        Inv Cat                             // lookup code
            StaticName="Invoice_x0020_Category1234"
            Name="Invoice_x0020_Category1234"
       
        Cat                                // where I want the desc to appear
       
       
    List2:                                // lookup list
            ID="{ED874E6F-A8C0-4CA5-A482-065881A845A6}"
           
        CatID                             // equiv lookup code
            ColName="float1"
       
        CatDesc,

 

then the lookup definition is:

 

    <Field ID="{2387cdb0-0d2c-4485-98e3-7a2dcedbe41c}"
        Name="InvoiceCategoryDescription"
        Type="Lookup"
        SourceID="http://schemas.microsoft.com/sharepoint/v3"
        DisplayName="Cat"
        StaticName="InvoiceCategoryDescription"
        Group="SoJCustomColumns"
        Sealed="FALSE"
        ReadOnly="FALSE"
        Filterable="TRUE"
        Sortable="TRUE"
        Hidden="FALSE"
        List="{ED874E6F-A8C0-4CA5-A482-065881A845A6}"
        FieldRef="Invoice_x0020_Category1234"
        ShowField="CatDesc"
        JoinColName="float1"
        JoinRowOrdinal="0"
        JoinType="INNER"
        />

giving us:

 

    SELECT CatDesc FROM {ED874E6F...} WHERE Invoice_x0020_Category1234 = float1

Answer 4

Thanks Andrew. That's pretty much what I have, but its not working possibly because the column/field in FieldRef is itself a Lookup (and as a result of type int) whereas the type of JoinCol is varchar. I'll play around with some different field column types to see if I can get what I want.

 

Thanks again.