Skinny tables in Salesforce

Use skinny table if performance is not good enough even after using custom indexes- A skinny table is a custom table in the Force.com platform that contains a subset of fields from a standard or custom base Salesforce object. Force.com can have multiple skinny tables if needed, and maintains them and keeps them completely transparent to you.

What is the Skinny table?

Salesforce can create skinny tables to contain frequently used fields and to avoid joins. This can improve the performance of certain read-only operations. Skinny tables are kept in sync with their source tables when the source tables are modified.

You need to contact Salesforce Customer Support for skinny table. You can’t create, access, or modify skinny tables yourself. This table shows an Account view, a corresponding database table, and a skinny table that would speed up Account queries

How Skinny Tables Can Improve Performance

 What are skinny tables? What makes them fast? For each object table that’s visible to you, Salesforce maintains other, separate tables at the database level for standard and custom fields. This separation, which is invisible to customers, ordinarily requires a join when a query contains both kinds of fields. A skinny table contains both kinds of fields and also omits soft-deleted records

• They avoid resource intensive joins
• Their tables are kept in sync with their source tables when source tables are modified
• They do not include soft deleted records
• Skinny help improve report and query performance in following ways-:
• Skinny tables provide a view across multiple objects for easy access to combined data
• Skinny tables contain frequently used fields and thereby help avoiding joins
• Skinny tables are kept in sync with changes to data in source tables
• Skinny tables can contain the following types of fields : 
• Checkbox
• Date
• Date and time
• Email
• Number
• Percent
• Phone
• Picklist (multi-select)
• Text
• Text area
• Text area (long)
• URL

When to Use?

Skinny tables are most useful with tables containing millions of records. They can be created on custom objects, and on Account, Contact, Opportunity, Lead, and Case objects. In addition, they can enhance performance for reports, list views, and SOQL. The skinny table allows you to reorganize selected columns and rows of data into a separate table.

Considerations

• Skinny tables can contain a maximum of 100 columns.
• Skinny tables can’t contain fields from other objects.
• Skinny tables are copied to your Full sandbox orgs.
• Skinny tables aren’t copied to your sandbox organizations. To have production skinny tables activated for sandbox types other than Full sandboxes, contact Salesforce Customer Support.
  

Polymorphic Relationships in SOQL Queries : TYPEOF

 Lets understand what is Polymorphic Relationships. In a polymorphic relationship, the referenced object of the relationship can be one of several different types of object for example who and what field on Task and Event Object. Who can be Contact or Lead. 

Relationship field and Polymorphic field

Let understand the different between Relationship field and Polymorphic field

Relationship field	Polymorphic field
OwnerId field of the Account and contact object. Most of the Lookup and master-detail fields.	OwnerId field of the event Object. It can be Calender or User. Or Who
Example Select id, Owner.Name from Account	Example SELECT Id, Owner.Name FROM Event WHERE Owner.Type = 'User'
To get Parent record data use dot(.).	You can use a TYPEOF clause in a query

 

What Field is also polymorphic field on Event/Task object. What about if we need to get field base on SObject Type? We have solution of this problem : TYPEOF.

TypeOF

TYPEOF is an optional clause that can be used in a SELECT statement of a SOQL query when you’re querying data that contains polymorphic relationships. A TYPEOF expression specifies a set of fields to select that depend on the runtime type of the polymorphic reference. TYPEOF is available in API version 46.0 and later (It is also available in API version 26.0 and later as part of a Developer Preview).

Syntax:

SELECT fieldList
        TYPEOF typeOfField
            WHEN whenObjectType THEN whenFieldList [...]
            ELSE elseFieldList
        END
FROM objectType

 

Use Case:

On Task Object "Related To" (What) is Polymorphic field. Which can be account, opportunity or other sobject. If "Related To" (What) field is related to account then we need to get account phone and number of employees fields. If the "Related To" (WhatId) field is related to Opportunity then we need to get amount and closeDate.

Set see how we can create our query for above requirement.

SELECT
      TYPEOF What
        WHEN Account THEN Phone, NumberOfEmployees
        WHEN Opportunity THEN Amount, CloseDate
        ELSE Name
      END
FROM Task
WHERE What.Type In ('Account','Opportunity')

•  In this query we used TYPEOF to get runtime type of the polymorphic reference field.
• We can use the TYPE to filter our query.
  

Let see how to handle it in Apex Class

List<Task> listTask = [SELECT
                                        TYPEOF What
                                           WHEN Account THEN Phone, NumberOfEmployees
                                           WHEN Opportunity THEN Amount, CloseDate
                                           ELSE Name
                                        END
                                   FROM Task
                                   WHERE What.Type In ('Account','Opportunity')];
    
for (Task tsk: listTask) {
    if (tsk.What instanceof Account) {
        Account acc = tsk.What;
        System.debug('--acc--->'+acc.NumberOfEmployees);
    } else if (tsk.What instanceof Opportunity) {
        Opportunity opp = tsk.What;
        System.debug('--opp--->'+opp.CloseDate);
    }
}

1.  Use InstanceOf to check the Object Type.
   

Lightning Datatable Sorting in Lightning Web Components

Last time we talk about Lightning Datatable in Lightning Web Components (LWC). In this post we will talk about lightning datatable example with sorting in lightning web components. We can achieve the column sorting with the help of onsort attribute in datatable.

Lightning-datatable

Lightning datatable provides an onsort attribute which allow us to implement the sorting in lightning datatable. To enable the sorting on row you need to set sortable to true for the column and set sorted-By to match the fieldName attribute on the column. 

Use onsort event handler to update the table with the new column index and sort direction. The sort event returns the following parameter.

1. fieldName : The fieldName that controls the sorting.
2. sortDirection : The sorting direction. Valid options include 'asc' and 'desc'.

We can implement the sorting in LWC with following ways :-

1. sorting locally 
2. via apex call.

Local Sorting

We mostly implement this type of sorting when we know data elements in lightning datatable is small and limited

1. Create Apex Class : To select certain contacts using SOQL, use an Apex method. Check this post to learn about how to Call Apex Methods in LWC.
   LWCDataTableSortingExample
   public with sharing class LWCDataTableSortingExample {
       @AuraEnabled(Cacheable=true)
       public static List <Contact> getContacts() {
           List<Contact> contList = [ SELECT Id, FirstName, LastName, Phone, Email
                                      FROM Contact
                                      LIMIT 10 ];
           return contList;
       }   
   }
   
2. Create Lightning web component : Create one Lightning web component in your developer org or sandbox.
   dataTableSortingLWC.html
   <template>
       <lightning-card title="Data Sorting in Lightning Datatable in LWC" icon-name="standard:contact" >
           <br/>
           <div style="width: auto;">
               <template if:true={data}>
                   <lightning-datatable data={data}
                                        columns={columns}
                                        key-field="id"
                                        sorted-by={sortBy}
                                        sorted-direction={sortDirection}
                                        onsort={doSorting}
                                        hide-checkbox-column="true"></lightning-datatable>
               </template>
           </div>
       </lightning-card>
   </template>
   • In lightning datatable use sorted-by and sorted-direction attribute to define the direction and sorted column.
   • use onsort event to call javascript function to sort your local data.
   
   dataTableSortingLWC.js
   import {LightningElement, wire, track} from 'lwc';
   import getContacts from '@salesforce/apex/LWCDataTableSortingExample.getContacts';
   
   // datatable columns with row actions. Set sortable = true
   const columns = [ { label: 'FirstName', fieldName: 'FirstName', sortable: "true"},
                     { label: 'LastName', fieldName: 'LastName', sortable: "true"},
                     { label: 'Phone', fieldName: 'Phone', type: 'phone', sortable: "true"},
                     { label: 'Email', fieldName: 'Email', type: 'email', sortable: "true" },];
   
   export default class DataTableSortingLWC extends LightningElement {
       @track data;
       @track columns = columns;
       @track sortBy;
       @track sortDirection;
    
       @wire(getContacts)
       contacts(result) {
           if (result.data) {
               this.data = result.data;
               this.error = undefined;
           } else if (result.error) {
               this.error = result.error;
               this.data = undefined;
           }
       }
   
       doSorting(event) {
           this.sortBy = event.detail.fieldName;
           this.sortDirection = event.detail.sortDirection;
           this.sortData(this.sortBy, this.sortDirection);
       }
   
       sortData(fieldname, direction) {
           let parseData = JSON.parse(JSON.stringify(this.data));
           // Return the value stored in the field
           let keyValue = (a) => {
               return a[fieldname];
           };
           // cheking reverse direction
           let isReverse = direction === 'asc' ? 1: -1;
           // sorting data
           parseData.sort((x, y) => {
               x = keyValue(x) ? keyValue(x) : ''; // handling null values
               y = keyValue(y) ? keyValue(y) : '';
               // sorting values based on direction
               return isReverse * ((x > y) - (y > x));
           });
           this.data = parseData;
       }    
   }
   •  On which column you want to enable the sorting use sortable: "true"
   • Call your javaScript sorting method from onSorting event.
   dataTableSortingLWC.js-meta.xml
   <?xml version="1.0" encoding="UTF-8"?>
   <LightningComponentBundle xmlns="http://soap.sforce.com/2006/04/metadata" fqn="dataTableSortingLWC">
       <apiVersion>46.0</apiVersion>
       <isExposed>true</isExposed>
       <targets>
           <target>lightning__AppPage</target>
           <target>lightning__RecordPage</target>
           <target>lightning__HomePage</target>
       </targets>
   </LightningComponentBundle>
       

Sorting by Apex Call

We also have another way of data sorting with Apex class.

1. Create Apex Class : Update your apex method and include sord column and sort order.
   
   
   public with sharing class LWCDataTableSortingExample {
       @AuraEnabled(Cacheable=true)
       public static List <Contact> getContacts(String field, String sortOrder) {
           String query;
           query  = 'SELECT Id, FirstName, LastName, Phone, Email FROM Contact';
           if(field != null && sortOrder !=null){
               query += ' ORDER BY '+field+' '+sortOrder;
           }
           return Database.query(query);
       }
   }
2. Create Lightning web components :- No change required in html file.
   
   
   <template>
       <lightning-card title="Data Sorting in Lightning Datatable in LWC" icon-name="standard:contact" >
           <br/>
           <div style="width: auto;">
               <template if:true={data}>
                   <lightning-datatable data={data}
                                        columns={columns}
                                        key-field="id"
                                        sorted-by={sortBy}
                                        sorted-direction={sortDirection}
                                        onsort={doSorting}
                                        hide-checkbox-column="true"></lightning-datatable>
               </template>
           </div>
       </lightning-card>
   </template>
   
   
   
   import {LightningElement, wire, track} from 'lwc';
   import getContacts from '@salesforce/apex/LWCDataTableSortingExample.getContacts';
   
   // datatable columns with row actions. Set sortable = true
   const columns = [ { label: 'FirstName', fieldName: 'FirstName', sortable: "true"},
                     { label: 'LastName', fieldName: 'LastName', sortable: "true"},
                     { label: 'Phone', fieldName: 'Phone', type: 'phone', sortable: "true"},
                     { label: 'Email', fieldName: 'Email', type: 'email', sortable: "true" },];
   
   export default class DataTableSortingLWC extends LightningElement {
       @track data;
       @track columns = columns;
       @track sortBy='FirstName';
       @track sortDirection='asc';
    
       // retrieving the data using wire service
       @wire(getContacts,{field : '$sortBy',sortOrder : '$sortDirection'})
       contacts(result) {
           if (result.data) {
               this.data = result.data;
               this.error = undefined;
           } else if (result.error) {
               this.error = result.error;
               this.data = undefined;
           }
       }
       doSorting(event) {
           // calling sortdata function to sort the data based on direction and selected field
           this.sortBy = event.detail.fieldName;
           this.sortDirection = event.detail.sortDirection;
       }
   }
   •  Retrieving the data using wire service. Wire will automatically call your apex class when sort field or direction will change
   • Call onSorting method when sort event will fire. 

If you want to load to many record on one single page then use Lazy loading in dataTable.

Modal/Popup in Lightning Web Component (LWC)

In this post we will talk about how to create modal/Popup in Lightning web Component (LWC). Currently "lightning:overlayLib" is not available in LWC, the only way to show modals and popups is through styling and making a custom html modal. Modals/Popup Box are used to display content in a layer above the app. Mostly used to creation or editing of a record, as well as various types of messaging and wizards.

If you want to know how to create Modal in Lightning aura component, please refer to this post.

Modal / Popup Example Lightning Web component(LWC)

You can take a help from SLDS for creating the modals. Code has following three main part

• section
• header
• footer

Create Lightning web component in your sandbox or developer org.

modalDemoInLWC.html

<template>
    <lightning-button variant="success" label="Open popup"
                        title="Open popup" onclick={showModalBox}>
    </lightning-button>

   <!-- modal start -->      
   <template if:true={isShowModal}>

            <!--
                I Used SLDS for this code
                Here is link https://www.lightningdesignsystem.com/components/modals/
            --> 

    <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">
       <div class="slds-modal__container">
        <!-- modal header start -->
          <header class="slds-modal__header">
             <button class="slds-button slds-button_icon slds-modal__close slds-button_icon-inverse" title="Close" onclick={hideModalBox}>
                <lightning-icon icon-name="utility:close"
                   alternative-text="close"
                   variant="inverse"
                   size="small" ></lightning-icon>
                <span class="slds-assistive-text">Close</span>
             </button>
             <h2 id="modal-heading-01" class="slds-text-heading_medium slds-hyphenate">Welcome in Apex Hours</h2>
          </header>
      
          <!-- modal body start -->
          <div class="slds-modal__content slds-p-around_medium" id="modal-content-id-1">
                <p>Modal/Popup in Lightning Web Component (LWC) Demo</p>
          </div>

          <!-- modal footer start-->
          <footer class="slds-modal__footer">
             <button class="slds-button slds-button_neutral" onclick={hideModalBox}>Cancel</button>
          </footer>
       
       </div>
    </section>
    <div class="slds-backdrop slds-backdrop_open"></div>
 </template>
 <!-- modal end -->

</template>

• Enable and disable the modal with template if:true
• Use section role='dialog'

modalDemoInLWC.js

import { LightningElement,track } from 'lwc';

export default class ModalDemoInLWC extends LightningElement {
    @track isShowModal = false;

    showModalBox() {  
        this.isShowModal = true;
    }

    hideModalBox() {  
        this.isShowModal = false;
    }
}

modalDemoInLWC.js-meta.js

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

Share Custom labels between Lightning Web Components

We know how to use custom label in lightning web components. What about if you have large list of labels and you want to to use in multiple LWC components? In this post we will talk about best practice to import bulk custom label in LWC and how to create utility class to import same set of label in multiple Lightning web components.

You can create a utility component that imports all the labels and then exposes them bundled together in one object via a method. Then your main component will have a cleaner list of imports.

Step 1) Create Utility Class.

Create a Lightning web component that contains only a single Js file. Like below structure

labelUtility
   ├──labelUtility.js
   └──labelUtility.js-meta.xml

Lets create one common label Utility component.

labelUtility.js

import header from '@salesforce/label/c.Header';
import title from '@salesforce/label/c.title';

const label = {
    header: header,
    title: title
};

export {label};

• The module should only have a JS file & Metadata file in it, otherwise it will not work.
• Just like with any Lightning web component, the folder name and the filename must be identical
• Imports all the labels and then exposes them bundled together with export.

Step 2) How to use shared label Utility

Create new Lightning web component and import Js file with import statement like below

import { label  } from 'c/labelUtility';

Let see the full code.

import { LightningElement,track } from 'lwc';
import { label  } from 'c/labelUtility';
export default class CustomLabelDemo extends LightningElement {
    @track myLabel=label;
}     

• Import JS file with import

<template>
    <lightning-card  title={myLabel.title} variant="narrow" icon-name="standard:opportunity">
        <p>{myLabel.header}</p>
    </lightning-card>
</template>   

Please share your feedback and comment if there is any better way to implement or your like this post.