Salesforce Table Filtering And Creating New Records On Other Objects

In this guide, we will share an example form template to list Price Book Entry records with editable quantities. The table rows where quantity is added will create new Opportunity Line Item records on Salesforce. The additional table functionality allows for table row filtering if you have hundreds or thousands of records to display.

Form Template to import - https://app.formyoula.com/templates/import

[Guide Form] Offline Salesforce Product Table With Images.json16.4KB

Table Filter Custom JavaScript.

// On render table record
formyoula.form_fields[ 'Price Book Entry Table' ].on( 'table:results:render:success', function( options ) {
  // Get data table object
  var table = $( '#salesforce_table_39a2-8bd5-de7d' ).DataTable();
  // Get all unique table column value
  var product_code_filter = _.uniq( table.column( 2 ).data().toArray() );
  // Set field select options
  window.formyoula.form_fields[ 'Product Code Filter' ].set( 'select_options', product_code_filter.join( '\n' ) );
  // Get all unique table column value
  var product_family_filter = _.uniq( table.column( 3 ).data().toArray() );
  // Set field select options
  window.formyoula.form_fields[ 'Product Family Filter' ].set( 'select_options', product_family_filter.join( '\n' ) );
} );

// Listen to field changes
formyoula.form_fields[ 'Product Code Filter' ].on( 'input:set:success', function( options ) {
  // Filter table
  filter_salesforce_table({
    search_column: 2,
    search_value: formyoula.form_fields[ 'Product Code Filter' ].get('value').join( '|' )
  });
} );  

// Listen to field changes
formyoula.form_fields[ 'Product Family Filter' ].on( 'input:set:success', function( options ) {
  // Filter table
  filter_salesforce_table({
    search_column: 3,
    search_value: formyoula.form_fields[ 'Product Family Filter' ].get('value')
  });
} );  

// Listen to field changes
formyoula.form_fields[ 'Updated At Filter' ].on( 'input:set:success', function( options ) {
  // Get date value
  var updated_at_filter = formyoula.form_fields[ 'Updated At Filter' ].get('value') ? moment( formyoula.form_fields[ 'Updated At Filter' ].get('value') ).format('ll') : '' ;
  // Filter table
  filter_salesforce_table({
    search_column: 4,
    search_value: updated_at_filter
  });
} );  

// Main table filter function
function filter_salesforce_table( options ) {
  // Get data table object
  var table = $( '#salesforce_table_39a2-8bd5-de7d' ).DataTable();
  // Search based on selected column value
  table.columns( options.search_column ).search( options.search_value, true, false ).draw();
}

Multiple Fields Per Row Custom JavaScript.

set_fields_into_a_row('fc3b-91e3-9519, c6ff-eb35-2d90, 32ec-3475-d185');
// Set fields into a row function 
function set_fields_into_a_row(row_fields) {
	// Get row field size
	var row_field_size = 12 / row_fields.split(',').length;
  // Get row field component ids
  var row_field_ids = row_fields.split(',').map(a => `#component-${a.trim()}`).join(",");
  // Set fields into a row
	$(row_field_ids)
	    .wrapAll('<div class="row">')
	    .addClass('col-xs-' + row_field_size )
			.find(".input_label")
        .removeClass("col-sm-2")
        .addClass("col-xs-12")
        .css("text-align", "left")
	    .next()
		    .removeClass("col-sm-10")
	      .addClass("col-xs-12");
}

Total Column Custom Javascript.

// Remove event
formyoula.form_fields['7e24-a8e8-c5bc'].off("salesforce:row:change:success");
// Index of column
var total_col_index = 17;
var qty_col_index = 16;
// On row change event
formyoula.form_fields['7e24-a8e8-c5bc'].on("salesforce:row:change:success", function(object){
    //Get row
    var current_row = object.salesforce_table.row($(object.current_row)).data();
    if (!current_row) return; // Return if no row available
    //Get total of Unit Price and Quantity
    var total = parseInt(current_row['ccrz__Price__c'])  * parseInt(current_row['ccrz__Product__r.B2B_Brand__c']); 
    //Get element of total coloum
    var total_el = object.salesforce_table.cell($(object.current_row),total_col_index).node();
    //Set total value
    $(total_el).html(total);
    // Get the array of total column element
    var total_price = object.salesforce_table.columns(total_col_index).nodes().toArray();
    var temp_data_total = [];
    // Calculate the value
    total_price = Object.values(total_price)[0].reduce(function(a,b){
       
        b = $(b).text();
        b = b || 0;
        temp_data_total.push(parseInt(b));
    },0)
    total_price = temp_data_total.reduce(function(a,b){return a+b;})
    // Total qty calculation 
    var total_qty = object.salesforce_table.column(qty_col_index).data().toArray().reduce(function(a,b) {
        a = a || 0; 
        b = b || 0;
        return parseInt(a) + parseInt(b);
    });
    //Total Pirce
    formyoula.form_fields['b6bd-9533-39db'].set('value',total_price);
    //Total qty
    formyoula.form_fields['16fa-b18c-abbe'].set('value', ( !isNaN(total_qty) ) ? total_qty: 0);
})

// After table render success event
formyoula.form_fields[ '7e24-a8e8-c5bc' ].on( 'table:results:render:success', function( options ) {
    // Get the table instance
    let table = $( '#salesforce_table_7e24-a8e8-c5bc' ).DataTable();
    let price_col = table.column(total_col_index).data().toArray();
    // Price col
    let qty_col = table.column(qty_col_index).data().toArray();
    // Get total price
    let total_price = ( price_col.length > 0 ) ? price_col.reduce(function(a,b){
        a = a || 0; 
        b = b || 0;
        return parseInt(a) + parseInt(b);
     }) : 0;
    // Get total qty
    let total_qty = ( qty_col.length > 0 ) ? qty_col.reduce(function(a,b){
        a = a || 0; 
        b = b || 0;
        return parseInt(a)  + parseInt(b);
    }) : 0;
    //Total Pirce
    formyoula.form_fields['b6bd-9533-39db'].set('value',total_price);
    //Total qty
    formyoula.form_fields['16fa-b18c-abbe'].set('value',total_qty);
})

For any questions, please contact us - [email protected] or the Formyoula in-app chat 🙂