How Do I Search A Sharepoint List By Using Prefix And Suffix Wildcards And Pagination?

Question

I am trying to develop a html/css/js frontend that allows me to search my SharePoint online list as I would a normal list. This turned out surprisingly impossible for MS Sharepoint.

By trial and error and reading through the sparse documentation, I did the following:

1. I tried using Search Rest API, but it doesn't allow suffix matching, so *arepoint won't return items that have words like sharepoint.
2. I tried using CAML query, but it doesn't allow me to jump to a page because I can't define a start row.
3. I tried using list filtering, but its "substringof" filter doesn't allow me to search multiline text.

Basically all three of them seem to have at least one glaring issue that impedes normal usage.

I'd like to know if anybody found a way to get around the issues of any one of the three methods.

I didn't paste the code because the issue I'm having is with the API's themselves, but I could post them if needed. MS Sharepoint just flat out said it doesn't support suffix matching with no further explanation like a punch in the face.

Edit: The workaround that I have so far is to get all the items as one giant JSON and do the searching and filtering client side, but that's already slow with my 900 row list.

Thanks,

Update: Wow, I've been trying and errorring with CAML since it seemed the best of the three, but also the hardest. So my best workaround so far is a two stage simultaneous search...

One function searches the first 20-30 rows and returns the results pretty quickly, so the user can scroll asap.

Another function starts at the same time and searches the whole list. It returns only IDs, just to build the pagination buttons. It's pretty slow but I'm still pretty stubborn about not using the left and right button without paging because ew.

Update: Here are my sharepoint search functions: git

You can use the same sp_listSearch to search for the first 30 items, then use sp_getRecordsFromList to get the whole details of the 30 items.

Run the sp_listSearch again without row limit so that it will return all the ids of all matches (might take long) and then program your own page loading functionality for all those ids... like maybe a put every 30 ids in a button named page1 and so on and then run sp_getRecordsFromList on button click.

Answer 1

I guess you are using old Sharepoint because you tried caml query the same I did. I have a partial solution but if u want to search on all your queries while they are shown to the user u cant because caml query doesn't have an option to tell it the first 100 rows and then from 101 to 200.

You can filter your caml query with a search input, and after that, u can apply datatables jquery to search on that result and pagination too. like this sample, u introduce the filter parameter and get an exact result just like SQL. Just send your view data to the model and from the model to the query. it's on c#.

 camlQuery.ViewXml = @"<View>
                          <Query>
                        <Where>
                        <Eq>
                        <FieldRef Name='CI' />
                        <Value Type='Text'>" + model.Carnet +"</Value></Eq></Where></Query></View>";

this returns exactly and specified results so you won't get 900 rows

try saketa caml query builder for testing your queries.

you can limit your query with a row limit, but the fasted solution to queries fast and not get 100000 rows is to limit your query with a search parameter like before. further, then that I didn't find a solution either to stop 100000 row queries.

this is the datatables that let you paginate and search but it won't limit the query rows. all docs are on datatables.net

<script type="text/javascript" src="~/Scripts/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="~/Scripts/dataTables.bootstrap5.min.js"></script>

<script type="text/javascript">
    $(document).ready(function () {
        $('#tblResultados').dataTable({
            "language": {
                "url": "//cdn.datatables.net/plug-ins/1.10.16/i18n/Spanish.json"
            },
            order: [[0, 'desc']],
            "iDisplayLength": 25,
            "lengthMenu": [25, 50, 100]
        });
    });
</script>