none
External Content Type Question (lookups) RRS feed

  • Question

  • Hi Folks,

    I am trying to create a custom list based on an ECT where one of the columns is a lookup column.

    I have a couple of database tables, one called 'Books' and the other called 'Authors'.   There is a foreign key relationship between Books and Authors   (book.AuthorId = Authors.Id).

    I have created an External Content Type that uses the Books table and have created a list based on that ECT.  I can view all of the books in the system, and I can add new ones (so long as the author id field is left blank).

    What I would like to do is on the add-new-item form, is have a drop-down so that the user can select from a list of known authors - which would read its data from the Authors table.

    Does anyone know how to do this?

    Thursday, April 26, 2018 4:29 AM

Answers

  • I eventually found a solution.

      I opened up SharePoint Designer, selected the Authors external content type and on summary view, I could select the AuthorName field, then from the toolbar select 'Set As Title'.

    I then re-created the list in SharePoint and when I now select 'edit item',  the Authors Name is displayed instead of the Author Id.  Also, in the dropdown, all of the author names are listed (whereas before, this was just an empty search field).

    • Marked as answer by AndyW2007 Friday, May 4, 2018 1:20 AM
    Friday, May 4, 2018 1:20 AM

All replies

  • As a follow up, I have created an association between the two ECTs (Books and Authors).

    However, the association was done using Books.AuthorId to Authors.AuthorId.   When I view the list in SharePoint the foreign key is displayed. If I choose edit item, then the assoication dropdown requires the user to type in a foreign key value into a search field.  This creates unnecessary complexity for the user. 

    What I would like is for the Authors.Name field (with a list of available authors) to be displayed in this dropdown, and the the value to be displayed in the list  (obviously when the item is saved, the AuthorId value would need to be saved in the Books.AuthorId field, not the plain text value.

    Thursday, April 26, 2018 11:13 PM
  • Hi,

    We are currently looking into this issue and will give you an update as soon as possible.

    Best Regards,

    Dean Wang



    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Wednesday, May 2, 2018 9:44 AM
    Moderator
  • Hi,

    A workaround: creating external list with these two columns, then get values from the two columns and show them as dropdown columns in your custom list.

    How to work with external list programmatically for your reference:

    https://zimmergren.net/sp-2010-programmatically-work-with-external-lists-bcs-in-sharepoint-2010/

    Best Regards,

    Dean Wang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Thursday, May 3, 2018 7:46 AM
    Moderator
  • Hi thanks,

      After a bit of experimentation I was able to link the two ECTs together using an ECT Association in SharePoint designer between book and author.   Now when I open the external list, I get a dropdown for selecting an author - however there is nothing in it except a search field and the user is expected to know what search term to enter.

    This is close, what I would really like is just a list of authors displayed in that dropdown.

    The other issue is that with external content types, it appears one cannot decorate them with additional columns in SharePoint, so I cant add a custom column or local lookup (choice) column or something like that.


    I could not find any documentation related to SharePoint 2013/2016 that allows one to create a custom list  in SharePoint that allows one to read from an external data source that isnt just a single table.
    • Edited by AndyW2007 Thursday, May 3, 2018 7:42 PM
    Thursday, May 3, 2018 7:40 PM
  • I eventually found a solution.

      I opened up SharePoint Designer, selected the Authors external content type and on summary view, I could select the AuthorName field, then from the toolbar select 'Set As Title'.

    I then re-created the list in SharePoint and when I now select 'edit item',  the Authors Name is displayed instead of the Author Id.  Also, in the dropdown, all of the author names are listed (whereas before, this was just an empty search field).

    • Marked as answer by AndyW2007 Friday, May 4, 2018 1:20 AM
    Friday, May 4, 2018 1:20 AM