Vlocity Dataraptor – How to Use Dynamic Query in Extract Action

omnistudiovlocity

Requirement :

  1. create a dynamic query that accepts multiple params (from input fields in Step)
  2. each param is added in the WHERE clause and seperated by an AND statement
  3. if param is blank then it is excluded from the WHERE clause

e.g. select id, name, stagename, LeadSource, Amount, Type, IsWon from
Opportunity where StageName =: param1 and LeadSource =: param2 and
Amount >: param3

Is this type of requirement possible to implement with standard Vlocity Dataraptors or do we need to invoke Apex?

Any tips/suggestions would be appreciated. Thanks.

Best Answer

After some trial and error I could not find any means to implement my requirement with standard Vlocity Dataraptors, hence I opted for below custom solution (with Apex)

Algorithm

  1. create an apex method with dynamic query
  2. create a remote action in omniscript
  3. pass params from omniscript to apex method
  4. return result in omniscript

Step #1 :

Create a Step element which will take 5 opportunity params, namely - StageName, LeadSource, Type, IsWon and Amount

Step #2 :

Create a Remote Action element which takes the above parameters in its Extra Payload

Step #3 :

Create the following Apex Class -

global with sharing class OmniscriptUtility implements omnistudio.VlocityOpenInterface  {
    
    global static Boolean invokeMethod(String methodName, Map < String, Object > inputMap, Map < String, Object > outMap, Map < String, Object > options) {
        Boolean result = true;
        try {
            if ('getOppsWithFilters'.equalsIgnoreCase(methodName)) {
                getOppsWithFilters(inputMap, outMap, options); 
            } else {
                result = false;
            }
        } catch (Exception e) {
            result = false;
        }
        return result;
    }

    global static void getOppsWithFilters(Map < String, Object > inputMap, Map < String, Object > outMap, Map < String, Object > options){
                
        string stage = (string)inputMap.get('stage');
        string leadSource = (string)inputMap.get('leadSource');
        string oppType = (string)inputMap.get('oppType');
        string isWon = (string)inputMap.get('isWon');
        string amount = (string)inputMap.get('amount');
        
        system.debug('stage -- '+ stage);

        string q = 'Select Id, Name, StageName, LeadSource, Amount, Type, IsWon from Opportunity where Id <> '+ null ; 

        string q1 = ' and StageName = '+ '\''+ stage +'\'' ; 
        string q2 = ' and  LeadSource = '+ '\''+ leadSource + '\'' ; 
        string q3 = ' and Type = '+ '\''+ oppType + '\''  ; 
        string q4 = ' and isWon = '+ isWon  ; 
        string q5 = ' and Amount >= '+ amount  ; 


        if(stage != '' ){
            q = q + q1; 
        }
        if(leadSource != ''){
            q = q + q2;  
        }
        if(oppType != ''){
            q = q + q3;  
        }        
        if(isWon != ''){
            q = q + q4;  
        }
        if(amount != ''){
            q = q + q5;  
        }
        
        q = q + ' limit 1000'; 

        System.debug('query string -- '+ q); 
        List<Opportunity> oppList = Database.query(q); 
        
        System.debug('Opportunity list -- '+ oppList); 

        outMap.put('Opportunity',oppList);
    }

}

Step #4 :

Go to Remote Properties inside the Remote Action and add the Remote Class and Method names, respectively.

Step #5 :

Create another Step and add a custom LWC inside it.

Step #6 :

HTML -

<template>
    <lightning-datatable
        key-field="id"
        data={dataList}
        hide-checkbox-column
        columns={columnsList}
    >
    </lightning-datatable>

</template>

JS -

import { LightningElement, api } from 'lwc';
import { OmniscriptBaseMixin } from 'omnistudio/omniscriptBaseMixin';

const columnsList = [
    {label : 'Id', fieldName : 'Id'}, 
    {label : 'Name', fieldName : 'Name'}, 
    {label : 'Stage', fieldName : 'StageName'}, 
    {label : 'Source', fieldName : 'LeadSource'}, 
    {label : 'Amount', fieldName : 'Amount'}, 
    {label : 'Type', fieldName : 'Type'}, 
    {label : 'IsWon?', fieldName : 'IsWon'}
];

export default class DisplayOppList extends OmniscriptBaseMixin(LightningElement) {

    dataList = []; 
    columnsList = columnsList; 

    @api oppList; 

    renderedCallback(){
        console.log('renderedCallback -- '+  JSON.stringify( this.oppList)); 
        
        this.dataList = this.oppList; 
    }
}
Related Topic