OmniStudio – How to Filter Dates in DataRaptor Using Literal Dates

There's no reason for dynamic queries here; Salesforce is smart enough to ignore criteria that doesn't make sense. You can simply use the following instead:

@AuraEnabled(cacheable=true)
public static RecordWrapper[] getFarmData(DateTime startDateTime, DateTime endDateTime) {
    RecordWrapper[] results = new RecordWrapper[0];
    for(Farm__c record: [
        SELECT Name, Farm_Owner__c, Verifier_Visits__c, CreatedDate
        FROM Farm__c
        WHERE Farm_Owner__c = :UserInfo.getUserId() AND
            CreatedDate >= :startDateTime AND
            CreatedDate <= :endDateTime
        ORDER BY CreatedDate DESC LIMIT 100
        ]) {
        results.add(new RecordWrapper(record));
    }
    return results;
}

If you only plan on returning values on a certain "list view", don't call the server unnecessarily.

Pass in the start and end date values; if the values are null, they will be ignored in the query.

I included LIMIT 100, but is that really necessary? Does a user have more than 50,000 farms to deal with? Also, is the "wrapper" really necessary? It seems like a lot of overhead for no real reason; you can directly transform the results in your wire method or just use the field names as-is. Consider simplifying your code design.

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; 
    }
}