GENERATE SOQL QUERY, RETRIVE DATA AND DOWNLOAD DATA AS CSV FILE USING LWC

In this blog we see how to generate soql query, retrive data in datatable and download data as csv file by using lightning web component.

For code you can view below or click this link

https://bitbucket.org/saikodavali/workspace/snippets/LMMy6E

SOQLGenerator.apx

public with sharing class ApexPicklist {
    public ApexPicklist() {

    }
    @AuraEnabled(cacheable=true)
    public static list<String> GetObjects(){
        list<String> AllObjects=new list<String>();
        for( Schema.SObjectType o: Schema.getGlobalDescribe().values()){
            AllObjects.add(o.getDescribe().getName());
        }
        AllObjects.sort();
        return AllObjects;
    }

    @AuraEnabled(cacheable=true)
    public static list<String> GetFields(String ObjName){
        SObjectType MyFields = Schema.getGlobalDescribe().get(ObjName);
        Map<String,Schema.SObjectField> mfields = MyFields.getDescribe().fields.getMap();

        List<String> sObjectDetailsList = new List<String>();
        for(String s : mfields.keySet()){
            sObjectDetailsList.add(String.valueOf(mfields.get(s)));
        }
        return sObjectDetailsList;
    }

    @AuraEnabled (cacheable=true)
    public static list<sobject> GenrateList(String q){
        try {
            list<sobject>DataList=database.query(q+' LIMIT 200');
            return DataList;
            
        } catch (Exception e) {
            throw new AuraHandledException(e.getMessage());
        }
    }
}

SOQLGenerator.js

import { LightningElement,track,wire} from 'lwc';
import GetObjects from '@salesforce/apex/ApexPicklist.GetObjects';
import GetFields from '@salesforce/apex/ApexPicklist.GetFields';
import GenrateList from '@salesforce/apex/ApexPicklist.GenrateList';
import { NavigationMixin } from 'lightning/navigation';
import LightningAlert from 'lightning/alert';
import LightningConfirm from 'lightning/confirm';
export default class ApexPicklist extends NavigationMixin(LightningElement){
    @track ObjectsPicklist=[];
    @track FieldsPicklist=[];
    @track error;
    @track ObjectName;
    @track FieldName;
    @track Query="";
    @track Query1;
    @track Query2="";
    @track Query3="";
    @track WhereQuery="";
    @track NIP=0;
    @track WhereField;
    @track WhereOp;
    @track WhereVal;
    @track OrderBy;
    @track Col=[];
    @track Order=true;
    @track FieldList;
    @track ReturnedData=[];
    @track AD=[{ label: 'Ascending', value: 'ASC' },{ label: 'Decending', value: 'DESC' }];
    @track AD1;
    @track BtnLable="Add"
    @track ShowDataTable=false;
    @track load=false;
    @track L1=true;
    @track L2=6;
    @track DisableFilters=true;
    @track DisableObject=false;
    @track DisableOR=true;
    @track IN="utility:search";
    @track SearchedObjects=[];
    @track SaveAllObjects=[];
    @track Datatableheight="oncollapse";
    @track Operators=[
        { label: '=', value: '=' },
        { label: '!=', value: '!=' },
        { label: '<', value: '<' },
        { label: '<=', value: '<=' },
        { label: '>', value: '>' },
        { label: '>=', value: '>=' },
        { label: 'IN', value: ' IN ' },
        { label: 'NOT IN', value: ' NOT IN ' },
        { label: 'LIKE', value: ' LIKE ' },
        { label: 'INCLUDE', value: ' INCLUDE ' },
        { label: 'EXCLUDE', value: ' EXCLUDE ' }
        
    ];
    @wire (GetObjects) 
    GetAllObjects({ error, data }){
        setTimeout(()=>{
            this.load=true;
        },1000);
        let options=[];
        if(data){
            data.forEach(i=>{
                options.push({ label: i, value: i });
            })
            this.ObjectsPicklist=options;
            this.SaveAllObjects=options;
            console.log(this.ObjectsPicklist);
        }
        else if(error){
            console.log(error);
        }
    }

    GetObjectName(event){
        this.ObjectName=event.target.value;
        this.GetAllFields();
    }

    GetAllFields(){
        GetFields({ObjName:this.ObjectName})
        .then(result=>{
            let options=[];
            result.forEach(i=>{
                options.push({ label: i, value: i });
            })
            this.FieldsPicklist=options;
        })
        .catch(error=>{
            this.error=error;
        });
    }

    GetFieldNames(event){
        this.FieldName=event.target.value;
        this.Query1="SELECT "+this.FieldName+" FROM "+this.ObjectName;
        if(this.WhereField==undefined && this.OrderBy==undefined){
            this.Query=this.Query1;
        }
        else if(this.WhereField==undefined){
            this.Query=this.Query1+this.Query2;
        }
        else if(this.OrderBy==undefined){
            this.Query=this.Query1+this.WhereQuery;
        }
        else{
            this.Query=this.Query1+this.WhereQuery+this.Query2;
        }
        this.DisableFilters=false;
        this.DisableObject=true;
    }

    AddInputs(){
        if(this.WhereField!=undefined && this.WhereOp!=undefined && this.WhereVal!=undefined){
            this.NIP+=1;
            if(this.NIP==1){
                if ((isNaN(this.WhereVal)==true && this.WhereVal!="true" && this.WhereVal!="false" && this.WhereVal!="null") || (this.WhereField=="Id" && this.WhereVal!="null")){
                    this.WhereQuery+=" WHERE "+this.WhereField + this.WhereOp  +"'"+this.WhereVal+"'";
                }
                else{
                    this.WhereQuery+=" WHERE "+this.WhereField + this.WhereOp  +this.WhereVal;
                }
                this.Query=this.Query1+this.WhereQuery+this.Query2+this.Query3;
                this.ResetFilters();
            } 
            else if(this.NIP!=1){
                if ((isNaN(this.WhereVal)==true && this.WhereVal!="true" && this.WhereVal!="false" && this.WhereVal!="null") || (this.WhereField=="Id" && this.WhereVal!="null")){
                    this.WhereQuery+=" AND "+this.WhereField + this.WhereOp +"'"+this.WhereVal+"'";
                }
                else{
                    this.WhereQuery+=" AND "+this.WhereField + this.WhereOp +this.WhereVal;
                }
                this.Query=this.Query1+this.WhereQuery+this.Query2+this.Query3;
                this.ResetFilters();
            } 	
            this.DisableOR=false;
            this.BtnLable="AND";
        }
        else{
            LightningAlert.open({
                message: 'Fill the required fields with valid values. [field] [operator] [value]',
                theme: 'error', 
                label: 'Error!', 
            });
        }
    }

    AddOR(){
        if(this.WhereField!=undefined && this.WhereOp!=undefined && this.WhereVal!=undefined){
            this.NIP+=1;
            if ((isNaN(this.WhereVal)==true && this.WhereVal!="true" && this.WhereVal!="false" && this.WhereVal!="null") || (this.WhereField=="Id" && this.WhereVal!="null")){
                this.WhereQuery+=" OR "+this.WhereField + this.WhereOp +"'"+this.WhereVal+"'";
            }
            else{
                this.WhereQuery+=" OR "+this.WhereField + this.WhereOp +this.WhereVal;
            }
            this.Query=this.Query1+this.WhereQuery+this.Query2+this.Query3;
            this.ResetFilters();
        }
        else{
            LightningAlert.open({
                message: 'Fill the required fields. [field] [operator] [value]',
                theme: 'error', 
                label: 'Error!', 
            });
        }
    }

    ResetFilters(){
        this.WhereField=undefined;
        this.WhereOp=undefined;
        this.WhereVal=undefined;
    }

    AddWhereField(event){
        this.WhereField=event.target.value;
    }
    AddWhereOp(event){
        this.WhereOp=event.target.value;
    }
    AddWhereVal(event){
        this.WhereVal=event.target.value;
    }
    AddOrderBy(event){
        this.OrderBy=event.target.value;
        this.Query2=" ORDER BY " + this.OrderBy;
        this.AD1="";
        this.Query=this.Query1+this.WhereQuery+this.Query2;
        this.Order=false;
    }
    AddAD(event){
        this.AD1=event.target.value;
        this.Query3=" "+this.AD1;
        this.Query=this.Query1+this.WhereQuery+this.Query2+this.Query3;
    }

    async GenerateAllList(){
        this.load=false;
        let FL=""+this.FieldName+""
        let Fields=FL.split(",");
        console.log(Fields);
        let columns=[];
        Fields.forEach(i=>{
            columns.push({label: i,fieldName: i});
        })
        columns.push({
            label: "Record",
            type: "button",
            typeAttributes: {name:"Nav",label:"view",variant: "base"}
        });
        this.Col=columns;
        await GenrateList({q:this.Query})
        .then(result =>{
            this.ReturnedData=result;
        })
        .catch(error=>{
            LightningAlert.open({
                message: error.body.message,
                theme: 'error', 
                label: 'Something went wrong. Please check the query.', 
            });
        });
        this.ShowDataTable=true;
        this.load=true;
    }

    Back(){
        this.load=false;
        this.Datatableheight="oncollapse";
        setTimeout(()=>{
            this.load=true;
        },500);
        this.L1=true;
        this.L2=6;
    }

    Expand(){
        this.load=false;
        this.Datatableheight="onexpand";
        setTimeout(()=>{
            this.load=true;
        },500);
        this.L1=false;
        this.L2=12;
    }

    async ResetQuery(){
        const ConfirmToReset= await LightningConfirm.open({
            message: 'Click OK to proceed.',
            theme: 'warning',
            label: 'Do you want to reset the query?',
        });
        if(ConfirmToReset==true && this.Query!=""){
            this.load=false;
            setTimeout(()=>{
                this.load=true;
            },500);
            this.Query="";
            this.Query1="";
            this.Query2="";
            this.WhereQuery="";
            this.ObjectName="";
            this.FieldName="";
            this.FieldsPicklist=[];
            this.AD1="";
            this.NIP=0;
            this.WhereField=undefined;
            this.WhereOp=undefined;
            this.WhereVal=undefined;
            this.OrderBy="";
            this.ShowDataTable=false;
            this.DisableFilters=true;
            this.DisableObject=false;
            this.DisableOR=true;
            this.BtnLable="Add";
            this.ObjectsPicklist=this.SaveAllObjects;
            this.template.querySelector(".searchinp").value="";
            this.Order=true;
        }
    }

    NavigateToRecordPage(event){
        this[NavigationMixin.Navigate]({
            type: 'standard__recordPage',
            attributes: {
                recordId: event.detail.row.Id,
                objectApiName: "Account",
                actionName: 'view'
            },
        });
        
    }

    OnQueryChange(event){
        let ChangedQuery=event.target.value;
        this.Query=ChangedQuery;
    }

    SearchObject(){
        this.ObjectsPicklist=this.SaveAllObjects;
        let SearchTerm=this.template.querySelector(".searchinp").value;
        let foundobj=this.ObjectsPicklist.filter(i=>i.value.includes(SearchTerm[0].toUpperCase()+SearchTerm.slice(1)));
        this.ObjectsPicklist=foundobj;
        this.IN="utility:success";
        setTimeout(()=>{
            this.IN="utility:search";
        },1000);
    }

    downloadCSVFile() {   
        let rowEnd = '\n';
        let csvString = '';
        
        let rowData = new Set();

        
        this.ReturnedData.forEach(function (record) {
            Object.keys(record).forEach(function (key) {
                rowData.add(key);
            });
        });

        
        rowData = Array.from(rowData);
        
        
        csvString += rowData.join(',');
        csvString += rowEnd;

        
        for(let i=0; i < this.ReturnedData.length; i++){
            let colValue = 0;

            
            for(let key in rowData) {
                if(rowData.hasOwnProperty(key)) {
                    
                    let rowKey = rowData[key];
                    
                    if(colValue > 0){
                        csvString += ',';
                    }
                   
                    let value = this.ReturnedData[i][rowKey] === undefined ? '' : this.ReturnedData[i][rowKey];
                    csvString += '"'+ value +'"';
                    colValue++;
                }
            }
            csvString += rowEnd;
        }

        
        let downloadElement = document.createElement('a');

        
        downloadElement.href = 'data:text/csv;charset=utf-8,' + encodeURI(csvString);
        downloadElement.target = '_self';
        
        downloadElement.download = this.ObjectName+'.csv';
        
        document.body.appendChild(downloadElement);
        
        downloadElement.click(); 
    }
}

SOQLGenerator.html

<template>
    <div class="styling">
        <lightning-layout style="margin-bottom: 0.8%;">
            <lightning-layout-item size="6" if:true={L1} style="margin-right: 0.5%">
            <lightning-tile style="padding:1.5%;background-color:white;border-style:ridge;border-radius:8px;border-color:#F2EBE9;">
                <h1 class="heading">Select Object :</h1>
                <lightning-layout style="padding: 2%;">
                    <lightning-layout-item style="padding-right: 0.5%;" size="4">
                        <lightning-input placeholder="Filter Object (optional)" variant="label-hidden" class="searchinp"></lightning-input>
                    </lightning-layout-item>
                    <lightning-layout-item style="padding-right: 0.5%;" size="1">
                        <lightning-button-icon icon-name={IN} onclick={SearchObject} alternative-text="Search" title="Filter Objects"></lightning-button-icon>
                    </lightning-layout-item>
                    <lightning-layout-item style="padding-right: 0.5%;" size="7">
                        <lightning-combobox disabled={DisableObject} variant="label-hidden" options={ObjectsPicklist} onchange={GetObjectName} value={ObjectName} placeholder="Select an Object"></lightning-combobox>
                    </lightning-layout-item>
                </lightning-layout>
                <h1 class="heading">Select Field(s) :</h1>
                <lightning-dual-listbox source-label="Available" selected-label="Selected" options={FieldsPicklist} onchange={GetFieldNames} value={FieldName}></lightning-dual-listbox>
                <h1 class="heading" style="margin-top: 1%;">Filters :</h1>
                <lightning-layout>
                    <lightning-layout-item padding="around-small" flexibility="auto">
                        <lightning-combobox disabled={DisableFilters} variant="label-hidden" placeholder="Select Field" options={FieldsPicklist} onchange={AddWhereField} value={WhereField}></lightning-combobox>
                    </lightning-layout-item>
                    <lightning-layout-item padding="around-small" flexibility="auto">
                        <lightning-combobox disabled={DisableFilters} options={Operators} variant="label-hidden" placeholder="Operator" onchange={AddWhereOp} value={WhereOp}></lightning-combobox>
                    </lightning-layout-item>
                    <lightning-layout-item padding="around-small" flexibility="auto">
                        <lightning-input disabled={DisableFilters} variant="label-hidden" placeholder="Value" onchange={AddWhereVal} value={WhereVal}></lightning-input>
                    </lightning-layout-item>
                    <lightning-layout-item padding="around-small" flexibility="auto">
                        <lightning-button-group>
                        <lightning-button disabled={DisableFilters} onclick={AddInputs} label={BtnLable} tiltle="ADD/AND"></lightning-button>
                        <lightning-button if:false={DisableOR} onclick={AddOR} label="OR" tiltle="OR"></lightning-button>
                        </lightning-button-group>
                    </lightning-layout-item>
                </lightning-layout>
                <div style="text-align: center;">
                    <h1><b style="color:green">{NIP} </b>Filter(s) are added</h1>
                </div>

                <h1 class="heading">Order By :</h1>
                <lightning-layout>
                    <lightning-layout-item padding="around-small" size="8">
                        <lightning-combobox disabled={DisableFilters} variant="label-hidden" placeholder="Select Field" options={FieldsPicklist} onchange={AddOrderBy} value={OrderBy}></lightning-combobox>
                    </lightning-layout-item>
                    <lightning-layout-item padding="around-small" size="4">
                        <lightning-combobox options={AD} disabled={Order} variant="label-hidden" onchange={AddAD} value={AD1}></lightning-combobox>
                    </lightning-layout-item>
                </lightning-layout>
            
            </lightning-tile>
            </lightning-layout-item>

            <lightning-layout-item size={L2} style="border-color:#7F8487;border-radius:8px;padding:0.5%;">
                <lightning-card>
                    <h1 slot="title"><lightning-icon style="margin-right: 1.5%;" icon-name="standard:apex"></lightning-icon>Query</h1>
                    <lightning-button-group slot="actions">
                        <lightning-button icon-name="utility:list" disabled={DisableFilters} onclick={GenerateAllList} label="List"></lightning-button>
                        <lightning-button variant="destructive-text" icon-name="utility:refresh" label="Reset" onclick={ResetQuery}></lightning-button>
                    </lightning-button-group>
                    <lightning-helptext slot="actions" icon-name="utility:info" content="Modify query any time but do not add any new fields manually. Use '( )' to avoid the logical errors when using both 'AND' 'OR' at a time."></lightning-helptext>
                    <lightning-textarea style="margin-right: 1%;margin-left:1%" disabled={DisableFilters} placeholder="SOQL Query..." value={Query} class="QR" onchange={OnQueryChange}></lightning-textarea>
                </lightning-card>
                <div class={Datatableheight} if:true={ShowDataTable}>
                    <div style="text-align: right;background-color:#f1f1f1;">
                        <lightning-button icon-name="utility:back" if:false={L1} label="Back" onclick={Back}></lightning-button>
                        <lightning-button if:true={L1} icon-name="utility:expand" label="Expand" onclick={Expand}></lightning-button>
                        <lightning-button style="margin-left: 0.5%;margin-right:0.5%" variant="success" icon-name="utility:download" label="csv" onclick={downloadCSVFile}></lightning-button>  
                    </div>
                    <lightning-datatable onrowaction={NavigateToRecordPage} hide-checkbox-column="true" show-row-number-column data={ReturnedData} columns={Col} key-field="Id">
                    </lightning-datatable>  
                </div>
            </lightning-layout-item>
        </lightning-layout>
    </div>

    <div if:false={load}>
        <lightning-spinner alternative-text="Loading" variant="brand" size="medium" class="slds-is-fixed"></lightning-spinner>
    </div>
</template>

SOQLGenerator.css

.andbtn{
    color: white;
    background-color: darkblue;
    margin-right: 0.5%;
    border: none;
}
.orbtn{
    color: white;
    background-color: darkgreen;
    border: none;
}
.andbtn:disabled,
button[disabled]{
  border: 1px solid #999999;
  background-color: #cccccc;
  color: #666666;
}
.orbtn:disabled,
button[disabled]{
  border: 1px solid #999999;
  background-color: #cccccc;
  color: #666666;
}

.heading{
  font-weight: 700;
  font-family:Arial, Helvetica, sans-serif
}

.oncollapse{
  margin-top: 1%;
  height: 370px;
  padding: 0.3%;
  
}

.onexpand{
  margin-top: 0.3%;
}

.styling{
  background-color:white;
  padding:0.5%;
  font-family:Verdana, sans-serif;
  --slds-c-card-text-color:#243A73;
}

Search This Blog

SAIKODAVALI

GENERATE SOQL QUERY, RETRIVE DATA AND DOWNLOAD DATA AS CSV FILE USING LWC

In this blog we see how to generate soql query, retrive data in datatable and download data as csv file by using lightning web component.

SOQLGenerator.apx

SOQLGenerator.js

SOQLGenerator.html

SOQLGenerator.css

Comments

Post a Comment