JOIN CRUDSPACE (WAITLIST)

Manipulate Salesforce metadata and permisions with speed!

Filtered & Searchable datatable in LWC with wired method

Filtered & Searchable datatable in LWC

Hello Trailblazers, In this post, we will see how you can create an HTML table with search and filter options in the Lightning web component. I will show the list of cases in the HTML table with the filter and search option.

In this component, you have two options, filter data from the database and search through all fields of all records retrieved from the database.

If you need you can add additional features like pagination, additional fields by modifying the code. You can also use the same code for a different object with some tweaks.


Code

We will use wired apex to get data from Salesforce. Here is the apex code. You can also use imperative apex call if needed.


FilteredTableController.cls

public with sharing class FilteredTableController {
    @AuraEnabled(cacheable=true)
    public static List<Case> getCases(
        String caseNumber,
        String subject,
        String priority,
        String status,
        String accountName,
        String contactName
    ) {
        String query;
        String condition = (String.isNotBlank(caseNumber)
            ? 'CaseNumber LIKE \'' + '%' + caseNumber + '%\''
            : '');

        condition += (String.isNotBlank(subject)
            ? (String.isNotBlank(condition) ? +' AND ' : '') +
              ' Subject LIKE \'' +
              '%' +
              subject +
              '%\''
            : '');

        condition += (String.isNotBlank(accountName)
            ? (String.isNotBlank(condition) ? +' AND ' : '') +
              ' Account.Name LIKE \'' +
              '%' +
              accountName +
              '%\''
            : '');

        condition += (String.isNotBlank(contactName)
            ? (String.isNotBlank(condition) ? +' AND ' : '') +
              ' Contact.Name LIKE \'' +
              '%' +
              contactName +
              '%\''
            : '');

        condition += (String.isNotBlank(status)
            ? (String.isNotBlank(condition) ? +' AND ' : '') +
              ' Status LIKE \'' +
              '%' +
              status +
              '%\''
            : '');

        condition += (String.isNotBlank(priority)
            ? (String.isNotBlank(condition) ? +' AND ' : '') +
              ' Priority LIKE \'' +
              '%' +
              Priority +
              '%\''
            : '');

        System.debug('condition ' + condition);
        if (String.isNotBlank(condition)) {
            query =
                'SELECT CaseNumber,Status,Subject,Account.Name,Contact.Name,Priority FROM Case WHERE ' +
                condition +
                ' ORDER BY CaseNumber';
        } else {
            query = 'SELECT CaseNumber,Status,Subject,Account.Name,Contact.Name,Priority FROM Case ORDER BY CaseNumber LIMIT 200';
        }

        List<Case> records = Database.query(query);
        return records;
    }
}

filteredTable.js

I have created a component called FilteredTable and below is the code for that.

import { LightningElement, wire, api, track } from "lwc";
import getCases from "@salesforce/apex/FilteredTableController.getCases";
import { NavigationMixin } from "lightning/navigation";
import { refreshApex } from "@salesforce/apex";
import { ShowToastEvent } from "lightning/platformShowToastEvent";

import { getPicklistValues } from "lightning/uiObjectInfoApi";
import STATUS_FIELD from "@salesforce/schema/Case.Status";
import PRIORITY_FIELD from "@salesforce/schema/Case.Priority";

export default class FilteredTable extends NavigationMixin(
    LightningElement
) {
    @track data;
    searchable = [];
    wiredCaseCount;
    wiredCases;

    doneTypingInterval = 0;
    statusPickListValues;
    priorityPickListValues;

    searchAllValue;

    caseNumber = "";
    accountName = "";
    contactName = "";
    subject = "";
    status = null;
    priority = null;

    @wire(getCases, {
        caseNumber: "$caseNumber",
        accountName: "$accountName",
        contactName: "$contactName",
        subject: "$subject",
        status: "$status",
        priority: "$priority"
    })
    wiredSObjects(result) {
        console.log("wire getting called");
        this.wiredCases = result;
        if (result.data) {
            this.searchable = this.data = result.data.map((caseObj, index) => ({
                caseData: { ...caseObj },
                index: index + 1,
                rowIndex: index
            }));
        } else if (result.error) {
            console.error("Error", error);
        }
    }

    @wire(getPicklistValues, {
        recordTypeId: "012000000000000AAA",
        fieldApiName: STATUS_FIELD
    })
    statusPickLists({ error, data }) {
        if (error) {
            console.error("error", error);
        } else if (data) {
            this.statusPickListValues = [
                { label: "All", value: null },
                ...data.values
            ];
        }
    }

    @wire(getPicklistValues, {
        recordTypeId: "012000000000000AAA",
        fieldApiName: PRIORITY_FIELD
    })
    priorityPickListPickLists({ error, data }) {
        if (error) {
            console.error("error", error);
        } else if (data) {
            this.priorityPickListValues = [
                { label: "All", value: null },
                ...data.values
            ];
        }
    }

    handleChange(event) {
        this[event.target.name] = event.target.value;
        console.log("change", this[event.target.name]);
    }

    handleKeyUp(event) {
        clearTimeout(this.typingTimer);
        let value = event.target.value;
        let name = event.target.name;

        this.typingTimer = setTimeout(() => {
            this[name] = value;
        }, this.doneTypingInterval);
    }

    clearSearch() {
        this.caseNumber = "";
        this.accountName = "";
        this.contactName = "";
        this.subject = "";
        this.status = null;
        this.priority = null;
        this.searchable = this.data;
        this.searchAllValue = "";
        this.searchAll();
    }

    handleSearchAll(event) {
        this.searchAllValue = event.target.value;
        this.searchAll();
    }

    searchAll() {
        let searchStr = this.searchAllValue.toLowerCase();
        const regex = new RegExp(
            "(^" + searchStr + ")|(." + searchStr + ")|(" + searchStr + "$)"
        );
        if (searchStr.length > 2) {
            this.searchable = this.data.filter((item) => {
                if (
                    regex.test(
                        item.caseData.CaseNumber.toLowerCase() +
                            " " +
                            item.caseData.CaseNumber.toLowerCase()
                    ) ||
                    regex.test(
                        item.caseData.Status?.toLowerCase() +
                            " " +
                            item.caseData.Status?.toLowerCase()
                    ) ||
                    regex.test(
                        item.caseData.Subject?.toLowerCase() +
                            " " +
                            item.caseData.Subject?.toLowerCase()
                    ) ||
                    regex.test(
                        item.caseData.Account?.Name?.toLowerCase() +
                            " " +
                            item.caseData.Account?.Name?.toLowerCase()
                    ) ||
                    regex.test(
                        item.caseData.Contact?.Name?.toLowerCase() +
                            " " +
                            item.caseData.Contact?.Name?.toLowerCase()
                    ) ||
                    regex.test(
                        item.caseData.Priority?.toLowerCase() +
                            " " +
                            item.caseData.Priority?.toLowerCase()
                    )
                ) {
                    return item;
                }
            });
        } else if (this.caseNumber.length <= 2) {
            this.searchable = this.data;
        }
        console.log(this.searchable);
    }

    handleNavigate(event) {
        event.preventDefault();
        this[NavigationMixin.Navigate]({
            type: "standard__recordPage",
            attributes: {
                actionName: "view",
                recordId: event.target.dataset.id
            }
        });
    }
}

filteredTable.html

<template>
    <lightning-card variant="Narrow">
        <lightning-layout multiple-rows>
            <lightning-layout-item
                size="12"
                padding="around-small"
            >
                <table class="slds-table slds-table_cell-buffer slds-table_bordered">
                    <thead>
                        <tr class="slds-line-height_reset">
                            <td colspan="3">
                                <lightning-input
                                    type="search"
                                    variant="standard"
                                    name="allfieldSearch"
                                    label="Search All Fields"
                                    placeholder="type text..."
                                    min-length="3"
                                    message-when-range-underflow="Type min 3 chars"
                                    value={searchAllValue}
                                    onchange={handleSearchAll}
                                ></lightning-input>
                            </td>
                            <td colspan="3">The "Search all fields" searches in local data only, use below filters to
                                get data from server</td>
                        </tr>
                        <tr class="slds-line-height_reset">
                            <td
                                scope="col"
                                style="width: 20px"
                            >
                                <lightning-button-icon
                                    variant="base"
                                    size="small"
                                    icon-name="utility:clear"
                                    alternative-text="Clear Search"
                                    onclick={clearSearch}
                                ></lightning-button-icon>
                            </td>
                            <td scope="col">
                                <lightning-input
                                    type="text"
                                    variant="standard"
                                    name="caseNumber"
                                    value={caseNumber}
                                    label="Search Case Number"
                                    placeholder="type case number..."
                                    onkeyup={handleKeyUp}
                                ></lightning-input>
                            </td>
                            <td scope="col">
                                <lightning-input
                                    type="text"
                                    variant="standard"
                                    name="accountName"
                                    value={accountName}
                                    label="Search Account"
                                    placeholder="Account name..."
                                    onkeyup={handleKeyUp}
                                ></lightning-input>
                            </td>
                            <td scope="col">
                                <lightning-input
                                    type="text"
                                    variant="standard"
                                    name="contactName"
                                    value={contactName}
                                    label="Search Contact"
                                    placeholder="Contact number..."
                                    onkeyup={handleKeyUp}
                                ></lightning-input>
                            </td>
                            <td scope="col">
                                <lightning-input
                                    type="text"
                                    variant="standard"
                                    name="subject"
                                    value={subject}
                                    label="Search Subject"
                                    placeholder="Subject..."
                                    onkeyup={handleKeyUp}
                                ></lightning-input>
                            </td>
                            <td scope="col">
                                <lightning-combobox
                                    name="status"
                                    label="Status"
                                    value={status}
                                    placeholder="Search Status"
                                    options={statusPickListValues}
                                    onchange={handleChange}
                                ></lightning-combobox>
                            </td>
                            <td scope="col">
                                <lightning-combobox
                                    name="priority"
                                    label="Priority"
                                    value={priority}
                                    placeholder="Search Priority"
                                    options={priorityPickListValues}
                                    onchange={handleChange}
                                ></lightning-combobox>
                            </td>
                        </tr>
                        <tr class="slds-line-height_reset">
                            <th scope="col">Index</th>
                            <th scope="col">Case Number</th>
                            <th scope="col">Account Name</th>
                            <th scope="col">Contact</th>
                            <th scope="col">Subject</th>
                            <th scope="col">Status</th>
                            <th scope="col">Priority</th>
                        </tr>
                    </thead>
                    <tbody>
                        <tr
                            class="slds-hint-parent"
                            for:each={searchable}
                            for:item="item"
                            key={item.Id}
                        >
                            <td scope="col">{item.index}</td>
                            <th scope="row">
                                <a
                                    href="#"
                                    onclick={handleNavigate}
                                    data-id={item.caseData.Id}
                                >{item.caseData.CaseNumber}
                                </a>
                            </th>
                            <th scope="row">
                                <span if:true={item.caseData.Account}>
                                    <a
                                        href="#"
                                        onclick={handleNavigate}
                                        data-id={item.caseData.Account.Id}
                                    >
                                        {item.caseData.Account.Name}
                                    </a>
                                </span>
                            </th>
                            <th scope="row">
                                <span if:true={item.caseData.Contact}>
                                    <a
                                        href="#"
                                        onclick={handleNavigate}
                                        data-id={item.caseData.Contact.Id}
                                    >
                                        {item.caseData.Contact.Name}
                                    </a>
                                </span>
                            </th>
                            <th scope="row">{item.caseData.Subject}</th>
                            <th scope="row">{item.caseData.Status}</th>
                            <th scope="row">{item.caseData.Priority}</th>
                        </tr>
                    </tbody>
                </table>
            </lightning-layout-item>
        </lightning-layout>
    </lightning-card>
</template>

filteredTable.js-meta.xml

<?xml version="1.0" encoding="UTF-8"?>
<LightningComponentBundle xmlns="http://soap.sforce.com/2006/04/metadata">
    <apiVersion>51.0</apiVersion>
    <isExposed>true</isExposed>
    <targets>
        <target>lightning__RecordPage</target>
        <target>lightning__AppPage</target>
        <target>lightningCommunity__Page</target>
        <target>lightningCommunity__Default</target>
    </targets>
</LightningComponentBundle>


Output

filtered-datatable-in-lwc


Notes & FAQ

You can use the same logic with lightning-datatable as well, you just need to define cols and replace the HTML table with lightning-datatable.

You can use this code with any other standard or custom object with some code changes.

You can make the searchAll dynamic, so you don't have to add code for each new field with the list of fields and Array.reduce() functions



10 comments:
  1. This is fantastic. I've modified this to pull in tasks related to accounts leveraging @api recordId. However one issue i am having is having the size of the data table stay within the confines of the region its in on the page layout. I.e. in a 3 column layout things be getting funky.

    ReplyDelete
    Replies
    1. Thanks, do you mean the filters are not inline with the columns?

      Delete
  2. Hi Rahul. Thank you for your effort and sharing the code over here. Just one doubt, I am still not able to get how is the individual filter working here. The 'handleKeyUp' doesn't really filter the data right ? Can you please explain?

    ReplyDelete
    Replies
    1. Hi PAmlan, the handlekeup is setting the tracked attributes like case number, account name these values are passed to the wired functions and wire function gets called automatically when any of the tracked attributes is changed.

      Delete
  3. Hi Raul, can you further explain this recordTypeId: "012000000000000AAA", why is it hardcoded ? thank you!

    ReplyDelete
  4. Hi Rahul, thank you for the code i really appreciate it. Can you provide an explanation on why you put a constant id like this recordTypeId: "012000000000000AAA"; fieldApiName: PRIORITY_FIELD? Help appreciated.

    ReplyDelete
    Replies
    1. Hi Donjee that is the master record type ID, you need to provide to getpicklistvalues fun it when type is not enabled for object

      Delete
  5. Hello, how would the assignment of data to lightning datatable differ if I were to use it instead. I am confused for that part. Please elaborate if you can. Thanks, Dimitrije.

    ReplyDelete
  6. Hello Rahul, how would this solution be different and how would the data be provided if I were to use the lightning data table. I am having issues with adapting it to my code, I am new to LWC. Thanks Dimitrije.

    ReplyDelete
    Replies
    1. Most of the things will be same I would suggest to get the simple lwc data table working and the slowly plugin the filter code. only the thing will change is how the data is visualised.

      Delete

Hi there, comments on this site are moderated, you might need to wait until your comment is published. Spam and promotions will be deleted. Sorry for the inconvenience but we have moderated the comments for the safety of this website users. If you have any concern, or if you are not able to comment for some reason, email us at rahul@forcetrails.com