How to show a full dropdown (all distinct values) instead of a text input?

burakbesli · September 3

I’m using SearchBuilder with serverSide: true. For certain fields (e.g., User or Form Stage) I need the value control to be a dropdown populated from the entire dataset (distinct values from the backend), not just the values shown on the current page.

What I observe:

With serverSide: true, SearchBuilder renders a text input rather than a select/dropdown for value entry. The docs say that, with server-side integration, places that would normally use selects are replaced with inputs to reduce server load, and custom conditions aren’t supported on the client in this mode.
datatables.net

If I force an “array”-style scenario, I can sometimes get a dropdown UI—but the options still only reflect the current page, not the whole dataset. I would like to preload/push a full list of options from my API so users can filter by values that may appear on other pages.

What I’ve read / tried:

I’ve seen the Editor example that shows SearchBuilder select elements with server-side by using the server-side SearchBuilderOptions class, with no extra client-side changes. That example suggests it’s possible to have server-provided option lists when using the Editor stack.
editor.datatables.net

I’m aware of columns.searchBuilderType (e.g., forcing string, date, etc.) to keep types consistent under server-side processing. I’m already setting that where appropriate.
datatables.net

I also came across this related forum thread about pre-populated dropdowns: “Using pre-populated drop down in search builder for server side processing using editor libs” (April 2023).
datatables.net

Questions:

Without Editor: Is there an officially supported way to supply a server-provided distinct options list for a column’s value control in SearchBuilder when serverSide: true?

Docs note that server-side SB switches selects to inputs and doesn’t allow custom conditions on the client, so I’m unsure if there is any config/callback (similar to how SearchPanes can be fed options server-side) that lets me inject an options list for SB’s value UI.
datatables.net

With Editor: If Editor’s server-side libraries are the intended path for select-style value UIs in SB, can I use those libraries just to power SearchBuilderOptions (no client-side Editor editing features)? The “Server-side processing” page states the Editor server-side libraries are open source and can be used for SearchBuilder’s server-side integration. Any minimal example for .NET/Node/PHP that shows returning the option set for SB would be super helpful.
datatables.net

Fallback / Alternatives: If SearchBuilder won’t support a server-pushed options list without Editor, is SearchPanes the recommended alternative for a global distinct-values UI under server-side?

allan · September 3

Hi,

Good question, and it appears my documentation is lacking in this respect. My apologies.

The answer is that you need to have the following strcture included in the JSON return from the server:

  "searchBuilder": {
    "options": {
      "fieldName": [

and that array should be a list of objects with value and label properties. You can have multiple field names in the options object if you need (i.e. multiple columns).

SearchBuilder will detect that in the JSON return and use it. You do not have to use the Editor server-side libraries for this, but that is an option if you wish to (the Editor server-side libraries are open source as you note).

Allan

burakbesli · September 4

First of all, thank you very much for your response. I may not have conveyed my request completely. To elaborate, my fieldNames are as follows.

    var serverSideOptions = {
                ajax: {
                    url: `/api/something/search`,
                    type: 'POST',
                    data: function (d) {
                        d.workflowId = formType;
                        d.listType = SomePath.data.WorkflowType;
                    },
                    beforeSend: function (request) {
                        request.setRequestHeader("Authorization", `Bearer ${SomeLibrary.token}`);
                        request.setRequestHeader("Language", Gizmo.dataStore.siteData.language);
                    }
                },
                searchBuilder: {
                    liveSearch: false
                },
                columns: [
                    {
                        data: 'FormNumber'
                    },
                    {
                        data: 'GenericState',
                        searchBuilderType: 'array',                
                        searchBuilder: {
                            orthogonal: {
                                search: 'filter',
                                display: 'display'
                            }
                        },
                        render: function (data, type, row) {
                            if (type === "display" || type === "export") {
                                return Handlebars.helpers.getGenericStateName(data);
                            }
                            return data;
                        }
                    },
                    {
                        data: 'CurrentWorkflowStageId',
                        render: {
                            _: 'display',
                            search: 'id',
                            filter: 'id'
                        },
                        searchBuilderType: 'array',
                        searchBuilder: {
                            orthogonal: {
                                search: 'filter',
                                display: 'display'
                            }
                        }
                    },
                    {
                        data: 'CreatedBy',
                        searchBuilderType: 'array',                
                        searchBuilder: {
                            orthogonal: {
                                search: 'filter',
                                display: 'display'
                            }
                        },
                        render: function (data, type, row) {
                            if (type == "display") {
                                return Handlebars.helpers.getOrganizationSchemaItem('users', data, 'fullName');
                            }
                            return data;
                        }
                    },
                    {
                        data: 'CreatedDate',
                        type: 'date',
                        render: function (data, type, row) {
                            if (type === "display" || type === "export") {
                                return Handlebars.helpers.formatDate(data, "long");
                            }
                            return data;
                        }
                    },
                    {
                        data: 'UpdatedBy',
                        searchBuilderType: 'array',                
                        searchBuilder: {
                            orthogonal: {
                                search: 'filter',
                                display: 'display'
                            }
                        },
                        render: function (data, type, row) {
                            if (type == "display") {
                                return Handlebars.helpers.getOrganizationSchemaItem('users', data, 'fullName');
                            }
                            return data;
                        }
                    },
                    {
                        data: 'UpdatedDate',
                        type: 'date',
                        render: function (data, type, row) {
                            if (type === "display" || type === "export") {
                                return Handlebars.helpers.formatDate(data, "long");
                            }
                            return data;
                        }
                    }
                ]
            };
    
            for (var i = 0; i < quickViewFields.length; i++) {
                var colOptions = {
                    data: quickViewFields[i].fieldName
                };
    
                switch (quickViewFields[i].dataType) {
                    case "date":
                        colOptions.type = "date";
                        colOptions.render = function (data, type, row) {
                            if (type === "display" || type === "export") {
                                return Handlebars.helpers.formatDate(data, "short");
                            }
                            return data;
                        }
                        break;
                    case "user":
                        colOptions.searchBuilderType = 'array',                
                        colOptions.searchBuilder = {
                            orthogonal: {
                                search: 'filter',
                                display: 'display'
                            }
                        },
                        colOptions.render = function (data, type, row) {
                            if (type == "display") {
                                return Handlebars.helpers.getOrganizationSchemaItem('users', data, 'fullName');
                            }
                            return data;
                        }
                        break;
                    case "department":
                        colOptions.searchBuilderType = 'array',                
                        colOptions.searchBuilder = {
                            orthogonal: {
                                search: 'filter',
                                display: 'display'
                            }
                        },
                        colOptions.render = function (data, type, row) {
                            if (type == "display") {
                                return Handlebars.helpers.getOrganizationSchemaItem('departments', data, 'departmentName');
                            }
                            return data;
                        }
                        break;
                }
    
                serverSideOptions.columns.push(colOptions);
            }

I'm receiving a response like the one below from the API, but the response only contains records related to that page.
Actually, I need to be able to specifically list all users (example: CreatedBy) in the filter section so that when I want to filter, all users are visible, not just those on page 1.

API Response:

    {
        "recordsTotal": 14447,
        "recordsFiltered": 14447,
        "draw": 1,
        "data": [
            {
                "FormNumber": "IMS-250601-0058",
                "GenericState": 1,
                "CurrentWorkflowStageId": {
                    "display": "Onay Bekleniyor",
                    "id": "645dcea49994a41cd00929ed"
                },
                "CreatedBy": "66fa78ea0a3c45e3fa2d79d5",
                "CreatedDate": "2025-06-01T04:11:25.898Z",
                "UpdatedBy": "66fa78ea0a3c45e3fa2d79d5",
                "UpdatedDate": "2025-06-01T04:11:26.458Z",
                "sellerName": "Some Company",
                "documentNumber": "Some Document Number",
                "documentDate": "2025-05-31T21:00:00.000Z",
                "TotalAmountIncludingTax": 12345,
                "rid": "Some RID"
            },
            {
                "FormNumber": "IMS-250601-0064",
                "GenericState": 1,
                "CurrentWorkflowStageId": {
                    "display": "Onay Bekleniyor",
                    "id": "645dcea49994a41cd00929ed"
                },
                "CreatedBy": "66fa51ea0a3c934e3fa2d66d5",
                "CreatedDate": "2025-06-01T14:39:46.759Z",
                "UpdatedBy": "66fa51ea0a3c934e3fa2d66d5",
                "UpdatedDate": "2025-06-01T14:39:47.334Z",
                "sellerName": "Some Company",
                "documentNumber": "some Document Number",
                "documentDate": "2025-05-31T21:00:00.000Z",
                "TotalAmountIncludingTax": 12345,
                "rid": "Some RID"
            },
            //...
            //Others
        ]
    }

My first question is, can we add an extra field to the API response for SearchBuilder to fulfill this request?
My second question is, if we can solve this in JS, is there a field like "possible value"?

allan · September 4

I'm receiving a response like the one below from the API

I don't see a searchBuilder object in the JSON, with an options object, as I descrbied above. If you want SearchBuilder to show a select with a list of options, you would need to include that in your JSON response, like in the example.

Actually, I need to be able to specifically list all users (example: CreatedBy) in the filter section so that when I want to filter, all users are visible, not just those on page 1.

If you are returning searchBuilder.options and it is only showing the options for the first page, then whatever query you are using to get the options is being limited to just that first page, and needs to be modified to consider the full data set.

My second question is, if we can solve this in JS, is there a field like "possible value"?

I don't know what you mean I'm afraid. How would a search for a possible value work?

Allan

burakbesli · September 11

https://live.datatables.net/seleyosi/1/edit

A sample was created and presented as a live example. It also included data from a fake API. In fact, the usage appeared to be working just fine, until the IDs returned from the API response stopped appearing in the Filtering list.

You can also see that the next list does not include the IDs returned from the API by going to
Add Condition > Creating User > Equals.

Note: Only one page of data is returned from the fake API. However, the important point is that

"searchBuilder": {
    "options": {
      "CreatedBy": [
        "66fa51ea0a3c91e3fa2d79d5",
        "679e7325340f47f120bc3f6a",
        "679e732b340f47f120bc41b6"
      ]
    }
  }

There is a problem with the Value and display name of the IDs sent in the section.

kthorngren · September 11

Looking at the JSON response in the example Allan linked to it looks like the options are an array of objects with the objects containing value and label key/value pairs. From the JSON response:

    "searchBuilder": {
        "options": {
            "users.first_name": [
                {
                    "value": "Alexa",
                    "label": "Alexa"
                },
                {
                    "value": "Avram",
                    "label": "Avram"
                },

Try changing your JSON response to this format.

Kevin

burakbesli · September 11

@kthorngren oh, thank you for making me realize that Now I changed my fake API response like that and boom, it's working as I expected @allan

I will share again if I encounter any problems, thank you for everything

burakbesli · September 12

We were able to get it to work as intended, but we want to use select2 for filtering. In this case, when we looked at the documentation, you suggested using "dtsb-inserted" and provided sample code, but this isn't the method we want. Is there an alternative method we can use here?

In short, we have multiple screens using DT, and we're loading DT using a single method. Therefore, using this method could be problematic. @allan

allan · September 12

I assume you are referring to this example and specifically:

$(el).on('dtsb-inserted', function () {

in the plugin code?

I'm not clear on why you can't use that way of creating the Select2 instance?

Allan

burakbesli · September 12

What I want is to use Select2 in the filtering section. Because when selecting Filter, ~500 users will appear in the list, and being able to filter the filter is a more accurate method for us.

I don't want to add select2 separately for each element, so I can't actually add it with this method.

I was referring:
https://datatables.net/reference/event/dtsb-inserted

kthorngren · September 12

I don't want to add select2 separately for each element, so I can't actually add it with this method.

How would you want to make this work? The example Allan linked to defines which columns that use Select2 with columns.type.

we have multiple screens using DT, and we're loading DT using a single method.

Are you saying you can't define columns.type for the columns you want using Select2 for each Datatable? Possibly you can use columnsDefs for this and pass into the single method the columns to apply Select2 to and use columnDefs.targets to define the columns for the custom columns.type.

Another option might be to apply a classname to the appropriate header th elements, in HTML, of the columns to use Select2. Use that as the columnDefs.targets.

If this doesn't help then please provide more details of how you would like the selection of the Select2 columns to work and also your relevant JS code so we can offer suggestions.

Kevin

How to show a full dropdown (all distinct values) instead of a text input?

How to show a full dropdown (all distinct values) instead of a text input?

Answers

Howdy, Stranger!

Categories

DataTables

How to show a full dropdown (all distinct values) instead of a text input?

How to show a full dropdown (all distinct values) instead of a text input?

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables