1

Can you please guide me how to pass the Query String Parameters to Data source Fiters in external list?

I have created external list based on external content type. External content type has three input parameters 1.ID 2.Start Date 3.Policy Number. While creating the external content type, I have added three filters as mentioned.

When I have added the static value in Data source filter from Modify View in external list and it's working fine as expected.

But I am getting problem with retrieving data from Query String Parameter (URL Parameter) and pass to the Data Source Filters.

Actually, I want to pass dynamic values based on URL Parameter instead of static value.

I highly appreciate your help !!!

Thanks in advance,

2

I've been working on this task from many days. My limitations was to only use SharePoint Designer. I couldn't use visual studio or other custom code.

After digging into google for many days, I've done this by performing following steps:

  1. Add one Business Data List Web part on the page.
  2. Configure that web part by choosing your External content type in Edit Web part pane.
  3. Set 3 filter parameters in External content type with "And" condition.
  4. Add 3 Query string filter web parts on the page and configure it for 3 different parameters.
  5. Add Connections of that query string filter web parts with business data list web part.
0

You can add Query string URL Filter webpart from Filter above BDC list and connect it with BDC list.

  • But I've multiple parameters as I've mentioned above. How to query string url filter web parts with multiple parameters? – Dikesh Gandhi Apr 8 '16 at 12:47
  • Ok but do you want to get result with & operator? – Viraj Gorajia Apr 8 '16 at 13:10
0

If you have less than 2000 (default threshold value to throttle records from a database backed BCS model) items, you can use CAML query as you would query a normal list. It would work the same. But as soon as you have items more than 2000, CAML query will not return results.

I searched, researched and leveraged resources available to me and came up with the following steps to accomplish this task in hand.

  1. Before performing these steps make sure you have all the configuration done and you are able to see the external list in SharePoint UI with all permissions and that you are able to query the list using CAML. This will help ensure you have a good working set.

  2. Edit the external content type using SharePoint Designer.

  3. Click Operations Design view to list all the methods/operations defined on the external source.

  4. Click on the existing ReadList operation or create a new one. The choice is completely at the hands of the designer. I prefer editing the existing one.

  5. Click Next till you land on the page which says Filter Parameters Configuration.

  6. Click Add Filter Parameter.

  7. Click the link (Click to Add) on the right pane.

  8. In the New Filter textbox, provide a filter name. E.g., SearchMyID.

  9. Based on the type of the primary key of your external data source, you can select Compare or Wildcard filter types. I had an integer data type as the primary key of the external data source (SQL database table), so selected Comparison filter type.

  10. Select the operator as Equal for equal comparison and the filter field you want to search on.

  11. Leave rest of the options to their default and hit OK.

  12. Make sure you have a limit filter (you should have already configured this when creating this operation the first time) set as default in order to display the list in SharePoint UI.

  13. Click Finish. Save the external content type and publish it again.

That’s it with respect to configuring the external content type, now comes the part where you use the filter in the code to query external list using CAML. This approach works equally good in server, client and javascript SharePoint object models.

I’ll skip the part where you create site/web objects and get the list instance. Here’s a sample CAML query that let’s you query for items in the external content type.

<view>
    <method name="<The name of the Read List method which we edited to add the Filter>">
        <filter name="<The name of the Filter>" value='{0}' />
    </method>
    <query>
        <where>
            <eq>
                <fieldref name='<Name of the Filter Field>' />
                <value type='Number'>{0}</value>
            </eq>
        </where>
    </query>
    <viewfields>
        <fieldref name='Field1' />
        <fieldref name='Field2' />
        <fieldref name='Field3' />
        <fieldref name='Field4' />
        <fieldref name='Field5' />
    </viewfields>
</view>

Update the attributes marked with some text in the CAML query sample above with the appropriate values. E.g, in the above CAML query in my case is actually ReadList. Use the String.Format to pass in the filter ID. That should be it.

Hope this helps!

  • Thanks for the answer. I've tried your mentioned steps with {0},{1} and {2} parameters in above query (As I've 3 parameters for filtering) but it doesn't work for me. In external list I've passed these parameters in "Data Source Filters". I want to filter data using "Data Source Filters" in external list. – Dikesh Gandhi Apr 11 '16 at 4:48
  • You have to define all 3 filters in method & add all 3 parameters in Eq condition with AND operator. have you tried it by doing same? – Hardik Apr 11 '16 at 5:48
  • Yes. Tried that. I want this filtration in list view and using SP designer. – Dikesh Gandhi Apr 11 '16 at 5:51
  • Have you checked this sharepoint.stackexchange.com/questions/4102/… – Hardik Apr 11 '16 at 5:55
  • Please also check this stackoverflow.com/questions/353485/… – Hardik Apr 11 '16 at 6:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.