GetFullTableQuery

Overview

GetFullTableQuery retrieves all data from custom BPM tables (created via the admin panel) with simple filtering capabilities. It's optimized for fast lookups on configuration tables, pricing rules, and lookup data.

When to Use

✅ Use GetFullTableQuery for:

• Reading from custom BPM tables
• Lookup tables (product codes, GL accounts)
• Configuration tables
• Pricing rules
• Fast single-table queries with simple filtering

❌ Don't use for:

• Core database tables (use RetrieveLoan/Deposit/ContactListQuery)
• Complex queries with JOINs (use DoSqlQuery)
• Aggregations like SUM, COUNT, AVG (use DoSqlQuery)

Syntax

doCmd() Usage

var result = doCmd('GetFullTableQuery', {
  Data: {
    table: 'tableName',
    filter: 'Field = Value AND Field2 >= Value2'
  }
});

JSON Configuration

{
  "commandName": "GetFullTableQuery",
  "parameters": {
    "Data": {
      "table": "tblPricingGLEV2",
      "filter": "Status = ACTIVE AND MinAmount >= 10000"
    }
  }
}

Parameters

Parameter	Type	Required	Description
Data.table	string	Yes	Name of the custom BPM table
Data.filter	string	No	Filter expression (use 1=1 for no filter)

Filter Operators

Operator	Description	Example
=	Equals	Status = ACTIVE
!=	Not equals	Status != INACTIVE
>	Greater than	Amount > 1000
<	Less than	Amount < 5000
>=	Greater or equal	MinAmount >= 10000
<=	Less or equal	MaxAmount <= 100000
AND	Combine conditions	Status = ACTIVE AND Amount > 1000

Note: Only AND operator is supported. OR is not available.

Return Value

Success Response

{
  "isSuccessful": true,
  "message": "Table data retrieved successfully.",
  "data": {
    "headers": ["Column1", "Column2", "Column3"],
    "rows": [
      {
        "Column1": "Value1",
        "Column2": "Value2",
        "Column3": "Value3"
      },
      {
        "Column1": "Value4",
        "Column2": "Value5",
        "Column3": "Value6"
      }
    ]
  }
}

Error Response

{
  "isSuccessful": false,
  "statusCode": "404",
  "message": "Table 'tableName' not found."
}

Examples

Example 1: Get All Active Products

var result = doCmd('GetFullTableQuery', {
  Data: {
    table: 'tblLoanProducts',
    filter: 'Status = ACTIVE'
  }
});

if (result.isSuccessful && result.data.rows.length > 0) {
  // Access the data
  result.data.rows.forEach(function(product) {
    console.log('Product:', product.ProductCode, product.ProductName);
  });
  
  // Store in context
  context.activeProducts = result.data.rows;
} else {
  console.warn('No active products found');
}

Example 2: Get Pricing for Amount Range

var requestedAmount = context.form.loanAmount;

var pricingResult = doCmd('GetFullTableQuery', {
  Data: {
    table: 'tblPricingGLEV2',
    filter: 'MinAmount <= ' + requestedAmount + ' AND MaxAmount >= ' + requestedAmount
  }
});

if (pricingResult.isSuccessful && pricingResult.data.rows.length > 0) {
  var pricing = pricingResult.data.rows[0];
  
  // Use pricing data
  context.interestRate = pricing.InterestRate;
  context.productCode = pricing.ProductCode;
  context.glAccount = pricing.GLAccount;
  
  return {
    success: true,
    interestRate: pricing.InterestRate
  };
} else {
  throw new Error('No pricing found for amount: ' + requestedAmount);
}

Example 3: Get All Configuration Settings

var configResult = doCmd('GetFullTableQuery', {
  Data: {
    table: 'tblSystemConfig',
    filter: '1=1' // Get all rows
  }
});

if (configResult.isSuccessful) {
  // Convert to key-value object
  var config = {};
  configResult.data.rows.forEach(function(row) {
    config[row.ConfigKey] = row.ConfigValue;
  });
  
  // Use configuration
  context.maxLoanAmount = parseFloat(config.MAX_LOAN_AMOUNT);
  context.minCreditScore = parseInt(config.MIN_CREDIT_SCORE);
  
  return config;
}

Example 4: Get GL Account Mapping

var productType = context.loan.productType;
var branchCode = context.user.branchCode;

var glResult = doCmd('GetFullTableQuery', {
  Data: {
    table: 'tblGLAccountMapping',
    filter: 'ProductType = ' + productType + ' AND BranchCode = ' + branchCode
  }
});

if (glResult.isSuccessful && glResult.data.rows.length > 0) {
  var glMapping = glResult.data.rows[0];
  
  context.principalGLAccount = glMapping.PrincipalAccount;
  context.interestGLAccount = glMapping.InterestAccount;
  context.feesGLAccount = glMapping.FeesAccount;
}

Example 5: Use in Decision Gateway

// Workflow Task: Get product eligibility criteria
var criteriaResult = doCmd('GetFullTableQuery', {
  Data: {
    table: 'tblLoanEligibility',
    filter: 'ProductCode = ' + context.form.productCode
  }
});

if (criteriaResult.isSuccessful && criteriaResult.data.rows.length > 0) {
  var criteria = criteriaResult.data.rows[0];
  
  // Store in context for gateway conditions
  context.minAge = parseInt(criteria.MinAge);
  context.maxAge = parseInt(criteria.MaxAge);
  context.minIncome = parseFloat(criteria.MinIncome);
  context.minCreditScore = parseInt(criteria.MinCreditScore);
  
  // Gateway will use: context.customer.age >= context.minAge && ...
  return { eligible: true };
} else {
  return { eligible: false, reason: 'Product not found' };
}

Use Cases

1. Product Configuration

Store and retrieve product-specific settings:

var productConfig = doCmd('GetFullTableQuery', {
  Data: {
    table: 'tblProductConfig',
    filter: 'ProductCode = LOAN001'
  }
}).data.rows[0];

2. Pricing Rules

Lookup pricing based on amount or tier:

var pricing = doCmd('GetFullTableQuery', {
  Data: {
    table: 'tblLoanPricing',
    filter: 'MinAmount <= ' + amount + ' AND MaxAmount >= ' + amount
  }
}).data.rows[0];

3. GL Account Mapping

Get GL accounts for transaction posting:

var glAccounts = doCmd('GetFullTableQuery', {
  Data: {
    table: 'tblGLMapping',
    filter: 'TransactionType = DISBURSEMENT'
  }
}).data.rows;

4. Fee Structure

Retrieve applicable fees:

var fees = doCmd('GetFullTableQuery', {
  Data: {
    table: 'tblFeeStructure',
    filter: 'ProductType = LOAN AND Status = ACTIVE'
  }
}).data.rows;

Error Handling

try {
  var result = doCmd('GetFullTableQuery', {
    Data: {
      table: 'tblPricingGLEV2',
      filter: 'Status = ACTIVE'
    }
  });
  
  if (!result.isSuccessful) {
    console.error('Query failed:', result.message);
    
    if (result.statusCode === '404') {
      throw new Error('Table not found: tblPricingGLEV2');
    } else if (result.statusCode === '400') {
      throw new Error('Invalid filter or table has no data');
    } else {
      throw new Error('Query failed: ' + result.message);
    }
  }
  
  if (result.data.rows.length === 0) {
    console.warn('No data found matching filter');
    return { hasData: false };
  }
  
  // Process results
  return { hasData: true, data: result.data };
  
} catch (error) {
  console.error('Exception in GetFullTableQuery:', error.message);
  return { hasData: false, error: error.message };
}

Common Error Codes

Code	Message	Resolution
404	Table not found	Verify table name exists in BPM admin
400	Table has no data	Check if table contains records
400	Invalid filter	Verify filter syntax and operators

Performance Tips

✅ Do's

• Keep custom tables small (less than 10,000 rows)
• Use specific filters instead of 1=1 when possible
• Index frequently filtered columns
• Cache frequently accessed data

❌ Don'ts

• Don't use for large datasets
• Don't fetch entire tables without filters repeatedly
• Don't use complex filter expressions (limited operator support)

Related Commands

• DoSqlQuery - For complex SQL queries with JOINs
• RetrieveLoanListQuery - For core loan data
• RetrieveDepositListQuery - For deposit account data
• QueryTableCommand - Alternative for parameterized queries

API Endpoint

Method: POST
URL: /api/bpm/execute-command

Request Body:

{
  "commandName": "GetFullTableQuery",
  "parameters": {
    "Data": {
      "table": "tblPricingGLEV2",
      "filter": "Status = ACTIVE"
    }
  },
  "context": {}
}

Response:

{
  "isSuccessful": true,
  "data": {
    "headers": ["Column1", "Column2"],
    "rows": [...]
  }
}

Best Practices

✅ Do

• Validate table name before querying
• Handle empty result sets gracefully
• Use specific filters to limit results
• Cache lookup data when appropriate
• Test filters with sample data

❌ Don't

• Don't trust user input in filters (validate first)
• Don't ignore error responses
• Don't query large tables without filters
• Don't use for real-time transaction data (use specific queries)

Complete Example: Loan Pricing Lookup

// Complete workflow example: Get loan pricing and apply to application

// Step 1: Get requested amount from form
var requestedAmount = context.form.loanAmount;
var productCode = context.form.productCode;

// Step 2: Query pricing table
var pricingResult = doCmd('GetFullTableQuery', {
  Data: {
    table: 'tblLoanPricing',
    filter: 'ProductCode = ' + productCode + 
            ' AND MinAmount <= ' + requestedAmount + 
            ' AND MaxAmount >= ' + requestedAmount +
            ' AND Status = ACTIVE'
  }
});

// Step 3: Validate results
if (!pricingResult.isSuccessful) {
  throw new Error('Failed to retrieve pricing: ' + pricingResult.message);
}

if (pricingResult.data.rows.length === 0) {
  return {
    success: false,
    reason: 'No pricing found for amount: ' + requestedAmount
  };
}

// Step 4: Extract pricing data
var pricing = pricingResult.data.rows[0];

// Step 5: Store in context for later use
context.interestRate = parseFloat(pricing.InterestRate);
context.processingFee = parseFloat(pricing.ProcessingFee);
context.insuranceFee = parseFloat(pricing.InsuranceFee);
context.maxTenure = parseInt(pricing.MaxTenure);

// Step 6: Calculate totals
var totalFees = context.processingFee + context.insuranceFee;
var monthlyPayment = calculateMonthlyPayment(
  requestedAmount,
  context.interestRate,
  context.form.tenure
);

// Step 7: Return result
return {
  success: true,
  pricing: {
    interestRate: context.interestRate,
    processingFee: context.processingFee,
    insuranceFee: context.insuranceFee,
    totalFees: totalFees,
    monthlyPayment: monthlyPayment
  }
};

Version History

• v1.0: Initial release
• v1.1: Added filter validation
• v1.2: Improved error messages