Hello friends, Today I am going to discuss Search Records and Show in Datatable In Lightning Component. I will create 4 search fields and after filling and submitting, data will show in Datatable. In this way, we will show data according to the user’s perspective.
Also check this: Search Records and Show in Datatable In LWC
Highlights Points :
- Datatable have paginations so show data in chunk.
- Datatable data can be sort.
- Data table has url link column.
- Show error message in Lightning Component toast.
- Show datatable data in each column based on the data type.
Step 1: In this step, we will create an apex class to fetch data whenever we feed the inputs on UI and hit the button. In this Apex Class, we create a static method that is annotated with Auraenabled. Also, we write a dynamic SOQL query according to UI inputs filters. Here we can fetch max 50000 records of a SOQL query put the limit at the end of the query.
SearchRecordsController.cls:
public class SearchRecordsController {
@AuraEnabled
public static String getUIThemeDescription() {
String theme = UserInfo.getUiThemeDisplayed();
return theme;
}
//Get Account Records
@AuraEnabled
public static wResult getAccounts(String pageSize, String pageNumber, string searchText, string sortDirection,string sortBy, string searchType){
List<WAccount> wAccList = new List<WAccount>();
String searchKey = searchText+ '%';
String query = 'SELECT Id, Name, BillingAddress, BillingStreet, BillingCity, BillingState, BillingCountry, BillingPostalCode, RecordType.Name, AccountNumber, type, Owner.Name, Owner.phone, Phone FROM Account where Name != null ';
if (String.isNotBlank(searchText)) {
if(searchType == 'Name'){
query += ' and (name like: searchKey)';
} else if(searchType == 'Phone'){
query += ' and (Phone LIKE: searchKey)';
} else if(searchType == 'Number'){
query += ' and (AccountNumber like: searchKey)';
}
}
query += ' limit 1000';
List<Account> lacc = Database.query(query);
for(Account a: lacc){
wAccList.add(new WAccount(a));
}
return new wResult(lacc.size(),wAccList);
}
//Get Account Records LWC
@AuraEnabled
public static wResult getAccountsLWC(string searchText, string searchType){
List<WAccount> wAccList = new List<WAccount>();
String searchKey = searchText+ '%';
String query = 'SELECT Id, Name, BillingAddress, BillingStreet, BillingCity, BillingState, BillingCountry, BillingPostalCode, RecordType.Name, AccountNumber, type, Owner.Name, Owner.phone, Phone FROM Account WHERE Name != null';
if (String.isNotBlank(searchText)) {
if(searchType == 'Name'){
query += ' and (name like: searchKey)';
} else if(searchType == 'Phone'){
query += ' and (Phone LIKE: searchKey)';
} else if(searchType == 'Number'){
query += ' and (AccountNumber like: searchKey)';
}
}
query += ' limit 1000';
List<Account> lacc = Database.query(query);
for(Account a: lacc){
wAccList.add(new WAccount(a));
}
return new wResult(lacc.size(),wAccList);
}
public class wResult{
@AuraEnabled public Integer totalAccount;
@AuraEnabled public List<WAccount> lstAccounts;
public wResult(Integer totalAccount, List<WAccount> lstAccounts){
this.totalAccount = totalAccount;
this.lstAccounts = lstAccounts;
}
}
public class WAccount{
@AuraEnabled public string Name;
@AuraEnabled public string Type;
@AuraEnabled public string Phone;
@AuraEnabled public string OwnerName;
@AuraEnabled public string OwnerId;
@AuraEnabled public string OwnerPhone;
@AuraEnabled public string AccountNumber;
@AuraEnabled public string BillingAddress;
@AuraEnabled public string BillingState;
public string makeAddress(Account Acc){
string address = '';
if(Acc.BillingAddress != null){
address = acc.BillingStreet+', '+acc.BillingCity+', '+acc.BillingState+', '+acc.BillingCountry+', '+acc.BillingPostalCode;
address = address.replace('null','');address = address.replace(', , ',',');
address = address.replace(', , ',',');
address = address.replace(',, ','');
}
return address;
}
public WAccount(Account acc){
this.name = (acc.name <> null) ? acc.name : '';
this.Type = (acc.Type <> null) ? acc.Type : '';
this.OwnerName = (acc.Owner.Name <> null) ? acc.Owner.Name : '';
this.OwnerId = acc.Owner.Id;
this.OwnerPhone = (acc.Owner.Phone <> null) ? acc.Owner.Phone : '';
this.Phone = acc.phone;
this.BillingAddress = makeAddress(acc);
this.AccountNumber = (this.AccountNumber <> null) ? this.AccountNumber : '';
this.Phone = (this.Phone <> null) ? this.Phone : '';
this.BillingState = (acc.BillingState <> null) ? acc.BillingState : '';
}
}
}
Step 2: In this step, we will create Lightning Component. Here we are going to create a UI part so users can feed data and display data with appropriate search in Datatable.
We create things one is input and the other is Datatable. We all know we can show the all data at one time but page loading time will increase so the solution is to show data in chunk form. For this we create pagination.
SearchRecordComp.cmp:
<aura:component implements="force:appHostable,flexipage:availableForAllPageTypes" access="global" controller="SearchRecordsController">
<aura:attribute name="data" type="Object"/>
<aura:attribute name="dataSort" type="Object"/>
<aura:attribute name="columns" type="List"/>
<aura:attribute name="m_SearchKeyword" type="String"/>
<aura:attribute name="searchKeyword" type="String"/>
<aura:attribute name="searchKeyword2" type="String"/>
<aura:attribute name="searchKeyword3" type="String"/>
<aura:attribute name="pageNumber" type="Integer" default="1"/>
<aura:attribute name="totalPage" type="Integer" default="1"/>
<aura:attribute name="totalRecords" type="Integer" default="0"/>
<aura:attribute name="pageSize" type="Integer" default="15"/>
<aura:attribute name="isLastPage" type="Boolean" default="true"/>
<aura:attribute name="dataSize" type="Integer" default="0"/>
<aura:handler name="init" action="{!c.doInit}" value="{!this}"/>
<aura:attribute name="sortBy" type="String" default="Name"/>
<aura:attribute name="sortDirection" type="String" default="asc"/>
<aura:attribute name="condition" type="Boolean"/>
<aura:attribute name="searchType" type="string"/>
<aura:attribute name="modal" type="Boolean" default="false"/>
<aura:attribute name="address" type="String"/>
<aura:attribute name="AccountNumber" type="String"/>
<aura:attribute name="Phone" type="String"/>
<aura:attribute name="OwnerName" type="String"/>
<aura:attribute name="Name" type="String"/>
<aura:attribute name="isDesktop" type="boolean"/>
<aura:attribute name="isMobile1" type="boolean"/>
<lightning:spinner variant="brand" size="large" aura:id="Id_spinner" class="slds-hide" />
<div class="slds-tabs_card">
<div class="slds-page-header">
<div class="slds-page-header__row">
<div class="slds-page-header__col-title">
<div class="slds-media">
<div class="slds-media__figure">
<span class="slds-icon_container slds-icon-standard-opportunity">
<lightning:icon iconName="standard:event" alternativeText="Event" title="Event" />
</span>
</div>
<div class="slds-media__body">
<div class="slds-page-header__name">
<div class="slds-page-header__name-title">
<h1>
<span>Lightning Component Search Records and Show in Datatable</span>
<span class="slds-page-header__title slds-truncate" title="TechDicer">TechDicer</span>
</h1>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div> <br/>
<lightning:card title="Search by one of the following Account fields." iconName="standard:account">
<aura:if isTrue="{!v.isDesktop}">
<lightning:layout>
<lightning:layoutItem size="3" padding="around-small">
<div style="font-weight:bold">
<lightning:input value="{!v.searchKeyword}" onchange="{!c.onSearchChange}" label="Account Name" placeholder="Enter Account Name" minlength="3"
aura:id="searchField" messageWhenTooShort="Minimum of 3 characters required to search"/>
</div>
</lightning:layoutItem>
<lightning:layoutItem size="3" padding="around-small">
<div style="font-weight:bold">
<lightning:input value="{!v.searchKeyword2}" onchange="{!c.onSearchChange}" label="Account Phone" placeholder="Enter Account Phone" minlength="3"
aura:id="searchField2" messageWhenTooShort="Minimum of 3 characters required to search"/>
</div>
</lightning:layoutItem>
<lightning:layoutItem size="3" padding="around-small">
<div style="font-weight:bold">
<lightning:input value="{!v.searchKeyword3}" onchange="{!c.onSearchChange}" label="Account Number" placeholder="Enter Account Number" minlength="3"
aura:id="searchField3" messageWhenTooShort="Minimum of 3 characters required to search"/>
</div>
</lightning:layoutItem>
<lightning:layoutItem size="3" padding="around-small">
<lightning:button onclick="{!c.Search}"
variant="brand"
label="Search" class="searchbtn"
iconName="utility:search"/>
</lightning:layoutItem>
</lightning:layout>
</aura:if>
<aura:if isTrue="{!v.isMobile1}">
<lightning:layout>
<lightning:layoutItem size="12" padding="around-small">
<lightning:select aura:id="selectSearchType" onchange="{!c.onSearchType}">
<option value="NONE">Select Search Type</option>
<option value="Name">Account Name</option>
<option value="Phone">Account Phone</option>
<option value="Number">Account Number</option>
</lightning:select>
</lightning:layoutItem>
</lightning:layout>
<lightning:layout>
<lightning:layoutItem size="12" padding="around-small">
<div style="font-weight:bold">
<lightning:input value="{!v.m_SearchKeyword}" disabled="true" label="" placeholder="Enter Search Text" minlength="3"
aura:id="m_SearchField" messageWhenTooShort="At least 3 character required to search"/>
</div>
</lightning:layoutItem>
</lightning:layout>
<lightning:layout>
<lightning:layoutItem size="12" padding="around-small">
<lightning:button disabled="true" aura:id="m_SearchButton" onclick="{!c.SearchMobile}"
variant="brand"
label="Search" class="searchbtn MButton"
iconName="utility:search"/>
</lightning:layoutItem>
</lightning:layout>
</aura:if>
<aura:if isTrue="{!v.condition}">
<p class="slds-p-horizontal_small">
<div class="myTest" >
<div class="slds-clearfix">
<div class="slds-page-header" role="banner">
<p class="slds-page-header__title">Accounts</p>
</div>
</div>
<lightning:datatable aura:id = "accDT"
hideCheckboxColumn="true"
columns = "{!v.columns}"
maxRowSelection="{!v.maxRowSelection}"
data = "{!v.data}"
keyField = "Id"
selectedRows = "{!v.selectedRowList}"
sortedBy="{!v.sortBy}"
sortedDirection="{!v.sortDirection}"
onsort="{!c.handleSort}"
onrowaction="{! c.handleRowAction }"
wrapTextMaxLines="5"
/>
<div class="slds-clearfix">
<div class="slds-page-header" role="banner">
<aura:if isTrue="{!v.isDesktop}">
<!-- Pagination Buttons Start -->
<div class="slds-align_absolute-center">
<lightning:button label="Prev" iconName="utility:back" iconPosition="left"
onclick="{!c.handlePrev}" disabled="{! v.pageNumber == 1}" variant="brand"/>
<span class="slds-badge slds-badge_lightest"
style="margin-right: 10px;margin-left: 10px;">
{!(v.pageNumber)+' of '+(v.totalPage)+' pages'} {! ((v.pageNumber-1)*v.pageSize+v.dataSize)+' of '+(v.totalRecords)+' records'}
</span>
<lightning:button label="Next" iconName="utility:forward" iconPosition="right"
disabled="{! v.isLastPage}" onclick="{!c.handleNext}" variant="brand"/>
</div>
<!-- Pagination Buttons End -->
</aura:if>
<aura:if isTrue="{!v.isMobile1}">
<div class="slds-align_absolute-center">
<lightning:button label="Prev" iconName="utility:chevronleft" iconPosition="left" onclick="{!c.handlePrev}" disabled="{! v.pageNumber == 1}"/>
<div style="padding:0px 10px">
{! ((v.pageNumber-1)*v.pageSize+v.dataSize)+' of '+(v.totalRecords)+' records'}
</div>
<lightning:button label="Next" iconName="utility:chevronright" iconPosition="right" disabled="{! v.isLastPage}" onclick="{!c.handleNext}"/>
</div>
</aura:if>
</div>
</div>
</div>
</p>
</aura:if>
<!--Modal Screen -->
<aura:if isTrue="{!v.modal}">
<section role="dialog" tabindex="-1" aria-labelledby="modal-heading-01" aria-modal="true" aria-describedby="modal-content-id-1" class="slds-modal slds-fade-in-open slds-modal_large">
<div class="slds-modal__container">
<header class="slds-modal__header">
<lightning:buttonIcon iconName="utility:close"
onclick="{! c.closeModel }"
alternativeText="close"
variant="bare-inverse"
class="slds-modal__close"/>
<h2 id="modal-heading-01" class="slds-text-heading_medium slds-hyphenate">Account Details</h2>
</header>
<!--###### MODAL BOX BODY Part Start######-->
<div class="slds-modal__content slds-p-around_medium" id="modal-content-id-1">
<p>Account Name : {!(v.Name)}</p><br></br>
<p>Account Owner : {!(v.OwnerName)}</p><br></br>
<p>Account No : {!(v.AccountNumber)}</p><br></br>
<p>Phone : {!(v.Phone)}</p><br></br>
<p>Address : {!(v.address)}</p><br></br>
</div>
<footer class="slds-modal__footer">
<lightning:button variant="neutral"
label="Close"
title="Close"
onclick="{! c.closeModel }"/>
</footer>
</div>
</section>
<div class="slds-backdrop slds-backdrop_open"></div>
</aura:if>
</lightning:card>
</aura:component>
SearchRecordCompcontroller.js:
({
doInit : function(component, event, helper) {
helper.getColumnAndAction(component);
component.set('v.condition',false);
},
onSearchType: function(component, event, helper) {
component.find('m_SearchField').set("v.disabled", true);
component.find('m_SearchButton').set("v.disabled", true);
component.set('v.searchType','');
if(component.find('selectSearchType').get('v.value') != 'NONE'){
component.find('m_SearchField').set("v.disabled", false);
component.find('m_SearchButton').set("v.disabled", false);
component.set('v.searchType',component.find('selectSearchType').get('v.value'));
}else{
component.set('v.pageNumber',1);
component.set('v.isLastPage',false);
component.set('v.dataSize',0);
component.set('v.data','');
component.set('v.dataSort','');
component.set('v.totalRecords',0);
component.set('v.totalPage',1);
component.set('v.isLastPage',true);
}
},
SearchMobile: function(component, event, helper) {
component.set('v.pageNumber',1);
component.set('v.isLastPage',false);
component.set('v.dataSize',0);
component.set('v.data','');
component.set('v.dataSort','');
component.set('v.totalRecords',0);
component.set('v.totalPage',1);
component.set('v.isLastPage',true);
var searchField = component.find('m_SearchField');
if(searchField == undefined || searchField.get('v.value') == undefined) {
searchField.set('v.validity', {valid:false, badInput :true});
searchField.showHelpMessageIfInvalid();
searchField.focus();
} else {
searchField.set('v.value',(searchField.get('v.value')).trim());
if(searchField.get('v.value').length < 3) {
searchField.set('v.validity', {valid:false, badInput :true});
searchField.showHelpMessageIfInvalid();
searchField.focus();
} else {
helper.getAccounts(component, helper);
}
}
},
onSearchChange: function(component, event, helper) {
component.find('searchField').set("v.disabled", false);
component.find('searchField2').set("v.disabled", false);
component.find('searchField3').set("v.disabled", false);
component.set('v.searchType','');
var val = event.getSource().get("v.value");
if(val != null && (val).trim() != '' && ((val).trim()).length > 0) {
if(event.getSource().getLocalId() == 'searchField') {
component.find('searchField2').set("v.disabled", true);
component.find('searchField3').set("v.disabled", true);
component.set('v.searchType','Name');
}
if(event.getSource().getLocalId() == 'searchField2') {
component.find('searchField').set("v.disabled", true);
component.find('searchField3').set("v.disabled", true);
component.set('v.searchType','Phone');
}
if(event.getSource().getLocalId() == 'searchField3') {
component.find('searchField').set("v.disabled", true);
component.find('searchField2').set("v.disabled", true);
component.set('v.searchType','Number');
}
}
},
Search: function(component, event, helper) {
component.set('v.pageNumber',1);
component.set('v.isLastPage',false);
component.set('v.dataSize',0);
component.set('v.data','');
component.set('v.dataSort','');
component.set('v.totalRecords',0);
component.set('v.totalPage',1);
component.set('v.isLastPage',true);
if(component.get('v.searchType') != undefined && component.get('v.searchType') != '') {
var searchField;
if(component.get('v.searchType') == 'Name') {
searchField = component.find('searchField');
}
if(component.get('v.searchType') == 'Phone') {
searchField = component.find('searchField2');
}
if(component.get('v.searchType') == 'Number') {
searchField = component.find('searchField3');
}
var isValueMissing = searchField.get('v.validity').valueMissing;
if(isValueMissing) {
searchField.showHelpMessageIfInvalid();
searchField.focus();
} else {
searchField.set('v.value',(searchField.get('v.value')).trim());
if(searchField.get('v.value').length < 3) {
searchField.set('v.validity', {valid:false, badInput :true});
searchField.showHelpMessageIfInvalid();
searchField.focus();
} else {
helper.getAccounts(component, helper);
}
}
}
},
handleSort : function(component,event,helper) {
//Returns the field which has to be sorted
var sortBy = event.getParam("fieldName");
//returns the direction of sorting like asc or desc
var sortDirection = event.getParam("sortDirection");
//Set the sortBy and SortDirection attributes
component.set("v.sortBy",sortBy);
component.set("v.sortDirection",sortDirection);
// call sortData helper function
helper.sortData(component,sortBy,sortDirection);
},
handleNext : function(component, event, helper) {
var pageNumber = component.get("v.pageNumber");
component.set("v.pageNumber", pageNumber+1);
var dataSort1 = component.get("v.dataSort");
var parseData = JSON.parse(JSON.stringify(dataSort1));
var data1 = parseData.slice((component.get("v.pageNumber") * component.get('v.pageSize')) - 15, component.get("v.pageNumber") * component.get('v.pageSize'));
component.set("v.data", data1);
component.set("v.dataSize", data1.length);
if(component.get('v.pageNumber') == component.get('v.totalPage')) {
component.set('v.isLastPage',true);
} else {
component.set("v.isLastPage", false);
}
},
handlePrev : function(component, event, helper) {
var pageNumber = component.get("v.pageNumber");
component.set("v.pageNumber", pageNumber-1);
var dataSort1 = component.get("v.dataSort");
var parseData = JSON.parse(JSON.stringify(dataSort1));
var data1 = parseData.slice((component.get("v.pageNumber") * component.get('v.pageSize')) - 15, component.get("v.pageNumber") * component.get('v.pageSize'));
component.set("v.data", data1);
component.set("v.dataSize", data1.length);
component.set("v.isLastPage", false);
},
handleRowAction: function (component, event, helper) {
var action = event.getParam('action');
var row = event.getParam('row');
if (action.name == 'view') {
component.set("v.modal", true);
component.set("v.address", row.BillingAddress);
component.set("v.AccountNumber", row.AccountNumber);
component.set("v.Phone", row.Phone);
component.set("v.Name", row.Name);
component.set("v.OwnerName", row.OwnerName);
}
},
closeModel: function(component, event, helper) {
component.set("v.modal", false);
},
})
SearchRecordCompHelper.Js:
({
getColumnAndAction : function(component) {
var action = component.get("c.getUIThemeDescription");
action.setCallback(this, function(a) {
component.set("v.theme", a.getReturnValue());
if(a.getReturnValue()=='Theme4d'){
component.set("v.isDesktop",true);
component.set("v.isMobile1",false);
component.set('v.columns', [
{label: 'Name', fieldName: 'Name', type: 'text',sortable : true , wrapText: true},
{label: 'Account ID', fieldName: 'AccountNumber', type: 'text',sortable : true , wrapText: true},
{label: 'Phone', fieldName: 'Phone', type: 'phone',sortable : true , wrapText: true},
{label: 'Owner', fieldName: 'linkName', type: 'url',sortable : true, wrapText: true,
typeAttributes: {label: { fieldName: 'OwnerName' }, target: '_blank' }},
{label: 'State', fieldName: 'BillingState', type: 'text',sortable : true , wrapText: true},
{label: 'Address', fieldName: 'BillingAddress', type: 'text',sortable : true , wrapText: true}
//{label: 'State', fieldName: 'BillingState', type: 'text',sortable : true , wrapText: true},
]);
}else if(a.getReturnValue()=='Theme4t'){
component.set("v.isMobile1",true);
component.set("v.isDesktop",false);
component.set('v.columns', [
{label: 'Name', fieldName: 'Name', type: 'text',sortable : true , wrapText: true},
//{label: 'Account ID', fieldName: 'AccountNumber', type: 'text',sortable : true , wrapText: true},
//{label: 'Account Phone', fieldName: 'Phone', type: 'phone',sortable : true , wrapText: true},
{label: 'Owner', fieldName: 'linkName', type: 'url',sortable : true, wrapText: true,
typeAttributes: {label: { fieldName: 'OwnerName' }, target: '_blank' }},
{label: 'Details', type: 'button', initialWidth: 105, typeAttributes: { label:'Details', name:'view', title: 'Click to View Details'}},
// {label: 'Account Address', fieldName: 'BillingAddress', type: 'text',sortable : true , wrapText: true}
]);
}
console.log('='+component.get("v.isMobile1")+'::'+component.get("v.isDesktop"));
});
$A.enqueueAction(action);
},
getAccounts : function(component, helper) {
var searchField;
var action = component.get("c.getAccounts");
var pageSize = component.get("v.pageSize").toString();
var pageNumber = component.get("v.pageNumber").toString();
var sortBy = component.get("v.sortBy");
var sortDirection = component.get("v.sortDirection");
component.find('Id_spinner').set('v.class','slds-show');
if(component.get("v.isDesktop")){
if(component.get('v.searchType') == 'Name') {
searchField = component.find('searchField');
}
if(component.get('v.searchType') == 'Phone') {
searchField = component.find('searchField2');
}
if(component.get('v.searchType') == 'Number') {
searchField = component.find('searchField3');
}
}
else if(component.get("v.isMobile1")){
searchField = component.find('m_SearchField');
}
action.setParams({
'pageSize' : pageSize,
'pageNumber' : pageNumber,
'searchText':searchField.get('v.value'),
'sortDirection':sortDirection,
'sortBy':sortBy,
'searchType':component.get('v.searchType')
});
action.setCallback(this,function(response) {
var state = response.getState();
component.find('Id_spinner').set('v.class' , 'slds-hide');
if (state === "SUCCESS") {
var resultData = response.getReturnValue();
var parseData = JSON.parse(JSON.stringify(resultData.lstAccounts));
parseData.forEach(function(record){
record.linkName = '/'+record.OwnerId;
});
var resData = parseData.slice(0, component.get('v.pageSize'));
component.set("v.data", resData);
component.set("v.dataSize", resData.length);
component.set("v.dataSort", parseData);
component.set('v.totalRecords',resultData.totalAccount);
component.set("v.condition",true);
if(resultData.totalAccount > component.get('v.pageSize')) {
component.set('v.totalPage',Math.ceil(resultData.totalAccount/component.get('v.pageSize')));
}
if(component.get('v.pageNumber') < component.get('v.totalPage')) {
component.set('v.isLastPage',false);
} else {
component.set("v.isLastPage", true);
}
}
});
$A.enqueueAction(action);
},
sortData : function(component,fieldName,sortDirection) {
var data = component.get("v.dataSort");
if (fieldName == 'linkName') {
fieldName = 'OwnerName';
}
var key = function(a) { return a[fieldName]; }
var reverse = sortDirection == 'asc' ? 1: -1;
data.sort(function(a,b){
var a = key(a) ? key(a).toLowerCase() : '';//To handle null values , uppercase records during sorting
var b = key(b) ? key(b).toLowerCase() : '';
return reverse * ((a>b) - (b>a));
});
//set sorted data to account data attribute
data.forEach(function(record) {
record.linkName = '/'+record.OwnerId;
});
var data1 = data.slice((component.get("v.pageNumber") * component.get('v.pageSize')) - 15, component.get("v.pageNumber") * component.get('v.pageSize'));
component.set("v.data",data1);
component.set("v.dataSort", data);
},
})
SearchRecordComp.Css :
.THIS .slds-dropdown-trigger{
display:none !important;
}
.THIS .searchbtn{
margin-top: 25px !important;
}
.THIS .slds-size_12-of-12{
padding-top:0px !important;
}
.THIS .MButton{
width: 100% !important;
padding: 5px !important;
}