#Integration67 debatiendo

Greetings, @Carissa Hansen

I have a few clarifying questions that will hopefully paint a clearer picture of your volumes and technical constraints. 

1. What is the average and peak volume of student registration records expected in each nightly CSV file? (If it's under 50,000 records daily, native/free workarounds are highly viable; if it's much larger, API limits and processing times become a major factor). 

2. Is the nightly export a "delta" file (only new/changed records) or a "full system extract" of all active enrollments?  

3. Do you have an on-premise server or a secure VM available that can run scheduled scripts? (If yes, you can actually automate the Salesforce Data Loader CLI for free using a script to pull from SFTP and push to Salesforce). 

4. Are you restricted to entirely cloud-hosted solutions, or are powerful open-source desktop/server ETL tools on the table? 

5. Does the Lumens CSV output match the formatting required by the EDA Interaction object, or will it require significant data transformation (e.g., splitting names, parsing strings, changing date formats)? 

6. Do you already have a shared unique key (like a Lumens Student ID) populated on your existing Salesforce Contact records to facilitate the EDA matching rules? 

7. Do you have a developer on staff who can write Apex, or are you looking for a strictly declarative (no-code) configuration? 

Be well - Katende

@A D

Look at the parameter name versus the SOQL bind variable. Your endpoint defines the parameter as amtthreshold (all lowercase). But your SOQL uses :amtThreshold (camelCase, capital T). Those have to match exactly. Right now the named query receives amtthreshold from the request but the bind variable :amtThreshold never gets populated, so the query fails the same way every time regardless of what value or type you pass. 

Two more things to check once that's done: 

The parameter binds as text by default. Amount is a number field. If the comparison throws a type error after you fix the casing, cast it in the SOQL or confirm the API catalog parameter type is set to number, not string. 

Confirm the running user behind the MCP connection has read access to Opportunity and the Amount field. A permission gap here also fails silently through the named query layer. 

But the casing mismatch is your culprit. Fix that first and retest before touching anything else.  

- SP

SBX Retail

You need to make few custom changes in recipe

1) Append new data

2) Run Recipe multiple times a day

3) Creates duplicate records

This is the problem

Solution

1) Add multirow formula to identify duplicate records after each run

Partition By unique Id , order by record modify or created date desc

case when current (uniqueid+modifydate or snapshot date ) = lead( uniqueid+modifydate or snapshot date) the duplicate else non duplicate end

2) Use the flag in filter

Like Duplicate Flag - Yes or No

This way no matter how many times you run your recipe it will always keep non duplicate entries with new values

Let me know if it helps

import { LightningElement, track } from 'lwc';

import { NavigationMixin } from 'lightning/navigation';

import executeQuery from '@salesforce/apex/DataExplorerController.executeQuery';

import getObjectFields from '@salesforce/apex/DataExplorerController.getObjectFields';

export default class DataExplorer extends NavigationMixin(LightningElement) {

    @track soqlQuery = '';

    @track tableData = [];

    @track tableColumns = [];

    @track fieldSuggestions = [];

    @track errorMessage = '';

    @track isLoading = false;

    @track loadingSuggestions = false;

    @track showTable = false;

    @track showNoRecords = false;

    @track detectedObject = '';

    @track showSuggestions = false;

    _debounceTimer;

    _lastFetchedObject = '';

    get isRunDisabled(){

        return !this.soqlQuery ||this.soqlQuery.trim() === ''|| this.isLoading;

    }

    get recordCount() {

        return this.tableData ?this.tableData.length :0;

    }

    handleQueryChange(event){

        this.soqlQuery = event.target.value;

        this.errorMessage = '';

        clearTimeout(this._debounceTimer);

        this._debounceTimer = setTimeout(() =>{

            this.fetchFieldSuggestions();

        }, 400);

    }

    fetchFieldSuggestions(){

        const objectName = this.extractObjectName(this.soqlQuery);

        if (!objectName){

            this.showSuggestions = false;

            this.fieldSuggestions = [];

            this.detectedObject = '';

            this._lastFetchedObject = '';

            return;

        }

        if (objectName.toLowerCase() === this._lastFetchedObject.toLowerCase()){

            return;

        }

        this._lastFetchedObject = objectName;

        this.detectedObject = objectName;

        this.loadingSuggestions = true;

        this.showSuggestions = false;

        getObjectFields({ objectApiName: objectName })

            .then(result =>{

                this.fieldSuggestions = result;

                this.showSuggestions = result && result.length > 0;

                this.loadingSuggestions = false;

            })

            .catch(() =>{

                this.fieldSuggestions = [];

                this.showSuggestions = false;

                this.loadingSuggestions = false;

            });

    }

    extractObjectName(query){

        if (!query) return null;

        const match = query.match(/\bFROM\s+([a-zA-Z_][a-zA-Z0-9_]*)/i);

        return match ? match[1] : null;

    }

    handleClear(){

        this.soqlQuery = '';

        this.tableData = [];

        this.tableColumns = [];

        this.fieldSuggestions = [];

        this.errorMessage = '';

        this.showTable = false;

        this.showNoRecords = false;

        this.showSuggestions = false;

        this.detectedObject = '';

        this._lastFetchedObject = '';

    }

    handleRunQuery(){

        const query = this.soqlQuery.trim();

        if(!query){

            this.errorMessage = 'Please enter a SOQL query.';

            return;

        }

        if(!this.isValidQuery(query)){

            this.errorMessage = 'Invalid query. DML statements (INSERT, UPDATE, DELETE, UPSERT, MERGE) are not allowed.';

            return;

        }

        this.isLoading = true;

        this.showTable = false;

        this.showNoRecords = false;

        this.errorMessage = '';

        executeQuery({ soqlQuery: query })

            .then(result =>{

                if (result && result.length > 0){

                    this.buildTable(result);

                    this.showTable = true;

                    this.showNoRecords = false;

                } else{

                    this.showNoRecords = true;

                    this.showTable = false;

                }

                this.isLoading = false;

            })

            .catch(error =>{

                this.errorMessage = error.body ? error.body.message : 'An error occurred while executing the query.';

                this.isLoading = false;

                this.showTable = false;

            });

    }

    isValidQuery(query){

        const forbidden = /\b(INSERT|UPDATE|DELETE|UPSERT|MERGE)\b/i;

        return !forbidden.test(query);

    }

    buildTable(records){

        const columns = [];

        const firstRecord = records[0];

        Object.keys(firstRecord).forEach(field =>{

            if (field === 'Id'){

                columns.unshift({

                    label: 'Record ID',

                    fieldName: 'Id',

                    type: 'button',

                    typeAttributes: {

                        label: { fieldName: 'Id'},

                        variant: 'base',

                        name: 'navigate'

                    },

                    initialWidth: 180

                });

            } else if(typeof firstRecord[field] !== 'object'){

                columns.push({

                    label: field,

                    fieldName: field,

                    type: 'text',

                    wrapText: true

                });

            }

        });

        this.tableColumns = columns;

        this.tableData = records;

    }

    handleRowAction(event){

        const actionName = event.detail.action.name;

        const row = event.detail.row;

        if (actionName === 'navigate'){

            this[NavigationMixin.Navigate]({

                type: 'standard__recordPage',

                attributes: {

                    recordId: row.Id,

                    actionName: 'view'

                }

            });

        }

    }

}