none
External List. Filter before loading data.

    Frage

  • Hello!

    I´ve an external list which gets data from a SQL Data Source.

    The SQL Table has more than 2.000 records, so I´ve a Limit Filter.

    The problem is that if I try to search a record It only looks between the 2000 records selected.

    I would like to query the database using a filter, for example "Country", this way I would have less than 2000 records, and I would be able to find all data related to one specific country.

    Right now I´ve 3 different Filters, (Text) and they work, but the only apply once the data is loaded, not before.

    Thanks for your help

    Montag, 14. Mai 2018 10:18

Alle Antworten

  • Hi Dominaz,

    You could re-create the external content type. When you re-create the external content type, you can add filter on the table in the database.

    It will not import all data from the table. It will import the data according to filter from the table.

    Then you can create the external list from the external content type.

    Best regards,

    Sara Fan


    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.

    Dienstag, 15. Mai 2018 06:39
    Moderator
  • Hi Dominaz,

    If the reply is helpful to you, you could mark the reply as answer. Thanks for your understanding.

    Best regards,

    Sara Fan


    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.

    Mittwoch, 16. Mai 2018 01:37
    Moderator
  • Hello, Thanks for the info.

    In fact, I´ve one filter parameter like the one your propose, its called "Passport Number"

    I´ve also a Textbox filter, and I can filter the list once it has been loaded, but not before.

    So I´ve the problem with the 2.000 records limit.

    That is, I want to be able to filter the list with one parameter, but I need to filter before the data is loaded.

    Regards

    Mittwoch, 16. Mai 2018 11:22
  • Hi Dominaz,

    If you want to filter the data before the data is loaded in the list, you should filter the data when creating the external content type or filter the data in the database by creating view or stored procedure.

    Did you set the default value of the "Passport Number" in the external content type?

    If you set the value of the "Passport Number" in the filter parameters, it will load the data according to the filter condition.

    Then you can create the external list based on the external content type.

    Best regards,

    Sara Fan


    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.

    Donnerstag, 17. Mai 2018 10:05
    Moderator