in SharePoint 2010

BCS: Showing a friendly display name on the External Item Picker control

Associations in BCS are a great way to relate two external content types based on a foreign key. It is actually very easy to create an association using SharePoint Designer 2010 – no code required!

 

You can read more on the following blogs posts to know more about associations:

 

1) The Notion of Associations and the External Item Picker

 

2) Tooling Associations in SharePoint Designer 2010

 

If you want to know quickly how can Associations help me, consider the picture below:

 

image

 

The Sales Order table has a column CustomerID which is actually a foreign key in the Customers table.

 

If you create Sales Order external list, you will notice that you get a text field for entering the CustomerID, which isn’t that helpful:

 

image

 

But if you create an association, then you get an external item picker where you can filter/select the customer:

 

image

 

If you read the blog post which I pointed out earlier, it says:

 

 

“……it’s possible to show a friendly display name on the external item picker control upon customer selection to show something more meaningful that the number that identifies the customer in the external system, for instance the customer name, or whatever field that is tagged as the title field in SPD.”

 

 

But how do you ‘tag as the title field in SPD’ ?

 

Well, it turns out it is very easy and simple!

 

Set Field as Title

 

In your external content type Summary View, select the field you want to ‘tag as the title’:

 

image

 

In the Ribbon, select Set as Title:

 

image

 

Now, you will be able to see that field, whatever is set as Title in the external item picker instead of the default identifier field

Write a Comment

Comment

Time limit is exhausted. Please reload the CAPTCHA.

23 Comments

  1. Handy tip thanks Chak! Would be even more useful if it displayed the new ‘title’ in the list view as well, in place of the foreign identifier. Know of a workaround to get the more relevant title to show up in your external list, instead of the foreign identifier?

  2. That is great for create/update/view screens but on list view the ID still shows! Such ashame, could have been really powerful! Some other button to push maybe?!?

  3. @Kylie – when you say ‘list view’, is it adding the external list web part?

    If so, I just added that by going to Insert->Existing List and added my external list. The default view it adds is crap. So, edit the web part and choose the appropriate view. You will now exactly get as in the list. View or Edit, the external item picker should show the field whatever is set as Title.

  4. I basically meant what Jeremy was saying – in the external list view you still see your foreign key as opposed to your title – works fine in the new item/update/view item form though.

  5. @jeremy – I am able to sort (Ascending/Descending) and also filter based on the association column, except that it will not be based on the ‘friendly name’.

  6. I havent found a way of displaying the friendly name in a list view short of using a .NET connector

  7. We’re trying to do our complete development using VS2010, so do you know if its possible to set the Title field in Visual Studio?

    Thanks!

  8. Hi Simon,

    You should be able to do that by following this steps:
    1. Select the Entity and select the properties.
    2. In the Custom Properties section, add the following:
    Name : Title
    Type : System.String
    Value: FieldName you want to come up as title.

    Alternately you can open the BCS model in XML editor and add the following tag in the properties of the entity:
    <Property Name="Title" Type="System.String">FieldName</Property>

  9. when you click the small button next to the textbox to open the Modaldialog, select an item, and click add(Multiselect is set to true), the BDC Identity of the entity Instance is displayed in the textbox instead of the identifier or the tile. Note this is only in the textbox of the modal dialog not the original textbox.

    Do you how might I control this?

  10. Hello,

    There’s a proeperty on the external item picker called MultiSelect. Set it to true from the markup or onInit.

    <SharePoint:ItemPicker MultiSelect=true id="YourEntityPickerID"

  11. Hi,

    I have two quries:

    1. When a field is selected as title for an entity that has associations, In the external picker results only titles are shown instead of the whole grid information. Is there a way to set the picker control to display other fields as well?

    2. Did anybody figure out a way to display the title instead of id in the list view for an associated field ?

    Cheers,
    Vamshi

  12. To answer a very old question on this page (how to display a friendly name in a list view): If using SQL, create a stored procedure for your Read List and Read Item methods (instead of connecting to the table directly), and return the value of the friendly name as part of the query inside the stored proc. If using web service or .net, since you have complete control over what fields you return, you can do the equivalent. **NOTE: You MUST add it to the Read Item method as well: SharePoint ignores and Read List methods containing fields not found in the Default Read Item method (and may even refuse to create the list.)

  13. And to ask a question of my own…

    What you describe here is great for displaying the friendly name, this is the first site I’ve found that actually had a solution to this problem. However, even though it displays correctly, If the user enters a value in the textbox, and uses the left icon to find the item, it won’t find friendly names, only the ID of the field. For example, if I have a cities foreign-keyed field, it will display “New York” perfectly using your method. However, if you type in “New York”, it will say that no entries match this value. Instead, you need to type “12” (or whatever the ID for New York is), then it will find the correct value. (This is when you type in a value rather that using the popup dialog to find a value.) Have you found a solution to this problem? Thanks!

  14. @Hutch. For resolution by name and not by ID this is a solution that works for me :

    If you try to check an item by something other than the ID, the ReadList operation of the associated item is used. (In case of IDs it seems to be the ReadItem operation.) You need to implement a wildcard filter against the property you want to search after in the checker, which also enables the search field in the picker.

    Filter Type: Wildcard
    Data source element: your property
    Default value: %

    Source : http://sharepoint.stackexchange.com/questions/69761/configure-check-if-item-exists-in-external-item-picker