Author: Yuri Gee
Date: 22 Jun 2025
10 minute read time
As artificial intelligence continues to revolutionize enterprise software, the opportunities to embed AI into CRM applications are growing rapidly. AI can be integrated natively into the CRM platform and as part of custom solutions that use your preferred AI models—be it a cloud service or a self-hosted open-source model.
This article presents a prototype dashlet that converts a natural language prompt into a SQL-like structure, enabling it to retrieve and display related records (e.g., tasks and opportunities) tied to an Account. The conversion leverages an AI model to generate structured metadata without exposing sensitive customer data.
While this example uses Google's Gemini 2.0 Flash model (available through its free tier), the same approach is broadly applicable across AI models trained on API or SQL patterns.
Introduction
At its core, an AI model can be thought of as a high-dimensional mathematical function that transforms encoded text, pixels, or other inputs—into semantically rich vector representations. This semantic understanding unlocks use cases like contextual search, pattern recognition, insight generation, and action recommendations based on linked CRM data.
While semantic understanding holds incredible potential within CRM systems, it also brings important challenges—such as maintaining alignment with user permissions, ensuring the accuracy and clarity of generated outputs, managing the operational costs of API usage and prompt tokens, and safeguarding data privacy within defined boundaries.
A promising approach to address some of these complexities can be the translation of natural language prompts into structured queries (like REST API filters or SQL-style conditions). Many general-purpose language models have been extensively trained on these patterns, allowing them to understand and generate CRM-friendly queries with minimal extra tuning.
Scenario: A Personalized Insight Dashlet
Imagine a user wants a quick, interactive report within the Account record view. Instead of building complex filters or navigating report builders, the user simply types:
"Show 3 latest task names and descriptions with status in progress"
"List top 5 opportunities by amount with assigned seller names"
The system interprets the request using an AI model and displays the appropriate related records using standard SugarCRM APIs—respecting the user’s permissions and limiting exposure to only what’s required.
This enables instant insight generation and offers flexibility to incorporate custom formulas, enhance the user interface with contextual elements, personalize interactions, and assist in triggering meaningful actions.
Dashlet Code
The dashlet implementation includes both a PHP definition and a JavaScript file. Please make sure to insert your AI model’s api_key into the JavaScript file.
The files should first be deployed in a test environment to validate their functionality. Performing a Quick Repair and Rebuild is necessary for the new dashlet to appear in the list of available dashlets in the Accounts module’s record view.
custom/modules/Accounts/clients/base/views/superdashlet/superdashlet.js
({
plugins: ['Dashlet'],
//custom/modules/Accounts/clients/base/views/superdashlet/superdashlet.js
rel_records: [],
module_name: '',
relationshipName: 'tasks',
fieldNames: ['name', 'status', 'date_due'],
sortField: 'date_due',
sort_up: true,
limitx: 100,
where_fieldname: '',
where_value: '',
initialize: function(options) {
this._super('initialize', [options]);
},
//Note: refresh not implemented
_renderHtml: function() {
this._super('_renderHtml');
this.$el.empty();
this.$el.append(`
<div class="dashlet-container">
<input type="text" class="ai-input" placeholder="Show 3 latest tasks with status in progress" style="width:70%; margin-right:8px;" />
<button class="ai-send btn btn-sm btn-primary">Ask AI</button>
<div class="ai-loading" style="margin-top:10px; display:none;">Loading...</div>
<div class="ai-reply" style="margin-top:10px;"></div>
</div>
`);
this.$('.ai-send').on('click', this.onAIPrompt.bind(this));
},
onAIPrompt: function() {
const input = this.$('.ai-input').val().trim();
if (!input) return;
//Note: A more rigorously defined schema should be applied based on the specifications of the AI model.
const prompt = `Convert this request into JSON: { "select": "field1,field2,...", "from": "relationship", "where": "field=value", "order_by": "field asc|desc", "limit": "number" } Allowed: - from: tasks, opportunities - tasks fields: id, name, description, date_due, assigned_user_name, date_entered - opportunities fields: id, name, amount, sales_stage, assigned_user_name, date_entered If any value is invalid, respond with: "not found" Only return JSON — no extra text or formatting. Request: ${input}`;
this.$('.ai-loading').show();
this.$('.ai-reply').empty();
//Note: Content-Security-Policy for your AI model url: connect-src https://generativelanguage.googleapis.com
fetch("https://generativelanguage.googleapis.com/v1beta/models/gemini-2.0-flash:generateContent?key=Your_Key", {
method: "POST",
headers: { "Content-Type": "application/json" },
body: JSON.stringify({
contents: [{ parts: [{ text: prompt }] }]
})
})
.then(res => res.json())
.then(data => {
const text = data?.candidates?.[0]?.content?.parts?.[0]?.text || '';
const jsonMatch = text.match(/```json\n([\s\S]*?)\n```/);
if (!jsonMatch) throw new Error("Invalid format");
const parsed = JSON.parse(jsonMatch[1]);
this.fieldNames = parsed.select?.split(',').map(f => f.trim()) || [];
this.relationshipName = parsed.from || '';
this.sortField = parsed.order_by?.split(' ')[0] || '';
this.sort_up = parsed.order_by?.toLowerCase().includes('asc') || false;
this.limitx = parseInt(parsed.limit, 10) || 5;
this.where_fieldname = '';
this.where_value = '';
if (parsed.where) {
const wm = parsed.where.match(/^([^=]+)=['"]?(.+?)['"]?$/);
if (wm) {
this.where_fieldname = wm[1].trim();
this.where_value = wm[2].trim();
}
}
this.validateAndRender();
})
.catch(() => {
this.showMessage("Response could not be parsed.");
})
.finally(() => {
this.$('.ai-loading').hide();
});
},
validateAndRender: function() {
//Note: Schema validation should be more rigorously enforced here using Sugar model relationships and field type definitions.
console.log(
`fieldNames: ${this.fieldNames.join(', ')}\n` +
`relationshipName: ${this.relationshipName}\n` +
`sortField: ${this.sortField}\n` +
`sort_up: ${this.sort_up}\n` +
`limitx: ${this.limitx}\n` +
`where_fieldname: ${this.where_fieldname}\n` +
`where_value: ${this.where_value}`);
const self = this;
if (!self.sortField || !self.sortField.trim()) {
self.sortField = 'date_entered';
}
var model = this.model || App.controller.context.get('model');
if (!model || !model.cid) {
this.showMessage('<div>No related records found.</div>');
return;
}
model.getRelatedCollection(this.relationshipName).fetch({
relate: true,
limit: 100, // Note: resolve next_offset
success: function(collection) {
self.rel_records = collection.sortBy(function(record) {
const raw = record.get(self.sortField);
if (typeof raw === 'string') {
const num = parseFloat(raw);
if (!isNaN(num) && isFinite(num)) return Number.isInteger(num) ? parseInt(raw, 10) : num;
return raw.toLowerCase();
}
if (typeof raw === 'number') return Number.isInteger(raw) ? raw : parseFloat(raw);
return raw || '';
});
self.module_name=collection.module;
if (!self.rel_records || self.rel_records.length === 0) {
self.showMessage('<div>No related records found.</div>');
return;
}
let html = '<table class="table"><thead><tr>';
self.fieldNames.forEach(field => {
const fieldMeta = App.metadata.getModule(self.module_name).fields[field] || {};
const label = App.lang.get(fieldMeta.vname || field, self.module_name);
html += `<th>${label}</th>`;
});
html += '</tr></thead><tbody>';
if (!self.sort_up) {
self.rel_records.reverse();
}
if (self.where_fieldname) {
self.rel_records = self.rel_records.filter(record => {
const value = record.get(self.where_fieldname);
const target = self.where_value;
if (typeof value === 'string' && typeof target === 'string') {
return value.trim().toLowerCase() === target.trim().toLowerCase();
}
return value === target;
});
}
self.rel_records = self.rel_records.slice(0, self.limitx);
self.rel_records.forEach(record => {
html += '<tr>';
self.fieldNames.forEach(field => {
html += `<td>${record.get(field) || ''}</td>`;
});
html += '</tr>';
});
html += '</tbody></table>';
self.showMessage(html);
},
error: function () {
self.showMessage('<p>Error loading related records.</p>');
}
});
},
showMessage: function(message) {
this.$('.ai-reply').html(`<div class="alert alert-warning">${message}</div>`);
}
});
custom/modules/Accounts/clients/base/views/superdashlet/superdashlet.php
<?php
//custom/modules/Accounts/clients/base/views/superdashlet/superdashlet.php
$viewdefs['Accounts']['base']['view']['superdashlet'] = [
'dashlets' => [
[
'label' => 'SuperDashlet',
'description' => 'Displays related records based on a relationship.',
'config' => [
],
'preview' => [
],
'filter' => [
'module' => [
'Accounts',
],
'view' => [
'record',
'records',
],
],
],
],
];
Implementation Highlights and Design Constraints
Data Transfer: Only schema metadata (e.g., field names, modules) is transmitted to the model—no sensitive data leaves the system.
Model Flexibility: Any LLM (Large Language Model) that supports structured prompt-to-JSON or SQL conversion can be used. You must supply your own API key to connect to the AI model. Ensure your Content Security Policy (CSP) includes your AI model url in connect-src generativelanguage.googleapis.com;
Validation: Input must be validated against your system's schema and relationships to ensure safe and correct execution. SugarCRM's APIs natively enforce access controls.
Dashlet UI: A minimal but expandable UI accepts user prompts, fetches AI responses, and renders results accordingly. Only tasks and opportunities modules are supported for now, with a limited field set for each. Default record limit is capped at 100 results per request. Prompt structure is simple but extensible via schemas.
Logic processing: The default sortBy function in a Sidecar collection may not handle all data types accurately or consistently. AI response caching is currently not implemented. A REST API proxy endpoint is needed to allow users to share the same API key for the AI model.
Final Thoughts
This prototype demonstrates how AI can be integrated into Sugar CRM system for smarter, more natural user interactions, where natural language becomes a powerful new interface layer. Whether you're exploring semantic search, contextual insights, or AI-assisted field population, SugarCRM’s flexible architecture combined with structured AI prompting gives you a strong foundation.
I'd love to hear how you're exploring AI within SugarCRM! What use cases have you already tackled, or are excited to try next?
