Defining Lookup Fields in CAML that pulls multiple fields

Question

We have defined a number of lookup fields like so:

        <Field ID="{81A578F2-8022-4083-8683-7F9BF039C82A}"
               Name="NameLookup"
               DisplayName="Navn"
               StaticName="Name"
               List="Lists/Names"
               Indexed="TRUE"
               RelationshipDeleteBehavior="Restrict"
               Type="Lookup"
               Group="ORG.PROJ.SOLUTION.Fields"
               Required="TRUE"
               PrependId="TRUE"
               ShowInNewForm="FALSE"
               ShowInEditForm="FALSE"
               ShowInViewForms="TRUE" 
               ShowInDisplayForm="TRUE"
               EnforceUniqueValues="FALSE"
               ShowField="ID"></Field>

This seems to work just fine, but I am missing a few things. And I also see some different behaviors although all fields are defined in the exact same way (With the exception of name, ID, List etc obviously).

Lets take the different behavior first.

For some of the fields, when I navigate to [List Settings] -> [Field Settings] then under "Relationship" some of my fields have:

 [ ] Enable relationships for this column 
 [ ] Enforce relationship behavior
   (●) Restrict delete
   ( ) Cascade delete

[ ] = Checkbox, ( ) = Radio Button, and the last Checkbox and the 2 Radiobuttons are grayed out until I check the "Enable relationships for this column"...

For other fields in some of the other lists, there is just:

 [v] Enforce relationship behavior
   (●) Restrict delete
   ( ) Cascade delete

Now Enforce is checked, nothing is grayed out and otherwise the same thing... But I can't identify any differences in the way I have defined the 2 fields... Does anyone know what the cause of this could be?... (The order the lists are rolled on maybe?)

Another thing, I can't add these things to a View as is now, unless I go though a number of steps, I would like to add the title and id from the lookup list as columns I can use in views. Again I can achieve this through SharePoint it self, but how do I do the same in CAML?...

I have been able to find allot of examples on just adding these lookupfields in CAML, but none seems to dive that bit deeper that I need.

(Title doesn't have to be Unique)

So to highlight the questions:

• How do we define a lookup field that pulls more than one column?
• Why does relation ship behavior differ (turn off?) for some fields)

Answer 1

Seems i figured the first one out on my own.

• How do we define a lookup field that pulls more than one column?

Quite simple when it comes down to it... Maybe logical, maybe not, I don't know, But what one have to do is like the following:

    <Field ID="{81A578F2-8022-4083-8683-7F9BF039C82A}"
           Name="NameLookup"
           DisplayName="Navn"
           StaticName="Name"
           List="Lists/Names"
           Indexed="TRUE"
           RelationshipDeleteBehavior="Restrict"
           Type="Lookup"
           Group="ORG.PROJ.SOLUTION.Fields"
           Required="TRUE"
           PrependId="TRUE"
           ShowInNewForm="FALSE"
           ShowInEditForm="FALSE"
           ShowInViewForms="TRUE" 
           ShowInDisplayForm="TRUE"
           EnforceUniqueValues="FALSE"
           ShowField="ID"></Field>

    <Field ID="{7859DC6C-510F-441D-8274-7FF0B4F65AD2}"
           FieldRef="{81A578F2-8022-4083-8683-7F9BF039C82A}"
           Name="NameLookup:Title"
           DisplayName="Navn:Title"
           StaticName="Name:Title"
           List="Lists/Names"
           Type="Lookup"
           Group="ORG.PROJ.SOLUTION.Fields"
           ReadOnly="TRUE"
           ShowInNewForm="FALSE"
           ShowInEditForm="FALSE"
           ShowInViewForms="TRUE"
           ShowInDisplayForm="TRUE"
           EnforceUniqueValues="FALSE"
           ShowField="Title"></Field>

So in fact, it is not a Single Lookup Field that pulls multiple columns/fields over, instead it is two Lookup Fields each pulling a column/field, but where the one of them is linked to the other by a FieldRef attribute.

Answer 2

A lookup field links to a single field in a single list, so you can't pull from more than one column with it (it can have multiple values from the same column). Depending on your use case, you might be able to use a calculated column to combine the results of multiple lookup columns into one and show it in the default view, or, it's probably possible to create a custom field type for a multi-column lookup.

You should probably split your relationship question into a separate question as it's a different issue entirely.