QueryPilot - User Guide

Introduction

The SankalvaX AI Agent is a NetSuite SuiteScript-based AI chatbot that allows users to query business data using natural language. The system works by matching user queries to predefined keys, which are then mapped to SQL queries that retrieve the relevant data from NetSuite.

This document provides instructions on how to add new queries and keys to the SankalvaX AI Agent, allowing you to extend its functionality to answer additional business questions.

Understanding the Query-Key Structure

The SankalvaX AI Agent uses a mappings object that contains key-value pairs:

Keys: Natural language phrases that represent common business queries (e.g., "top customer", "customer Count")
Values: SQL queries that retrieve the relevant data from NetSuite

When a user asks a question, the system uses NetSuite's LLM (Large Language Model) module to match the user's input to the closest predefined key. If a match is found, the corresponding SQL query is executed, and the results are formatted and returned to the user.

Current Query Mappings

Key	Description
top customer	Returns the top customers by sales amount
customer Count	Returns the total number of customers
top vendor	Returns the top vendors by purchase amount
vendorCount	Returns the total number of vendors
top items	Returns the top items by sales amount
item Count	Returns the total number of items
arThisMonth	Returns accounts receivable for the current month
apThisMonth	Returns accounts payable for the current month
salesThisMonth	Returns sales orders for the current month
salesOrderDetails	Returns details for a specific sales order

Steps to Add New Queries

Step 1: Identify the Business Question

Example: "What purchase orders are currently open?"

Step 2: Create a Natural Language Key

"open purchase orders"

Step 3: Write the SQL Query

    SELECT transaction.tranid AS po_number,
           transaction.trandate AS date,
           vendor.entityid AS vendor_id,
           vendor.companyname AS vendor_name,
           transaction.amount AS amount
    FROM transaction
    LEFT JOIN vendor ON vendor.id = transaction.entity
    WHERE transaction.type = 'PurchOrd'
    AND transaction.status = 'PurchOrd:B'
    ORDER BY transaction.trandate DESC
    FETCH FIRST {count} ROWS ONLY;

Step 4: Add Parameters

Use {count}, {tranid} where applicable.

Step 5: Add to the Mappings Object

    const mappings = {
      // Existing mappings...
      "open purchase orders": "SELECT transaction.tranid AS po_number, transaction.trandate AS date, vendor.entityid AS vendor_id, vendor.companyname AS vendor_name, transaction.amount AS amount FROM transaction LEFT JOIN vendor ON vendor.id = transaction.entity WHERE transaction.type = 'PurchOrd' AND transaction.status = 'PurchOrd:B' ORDER BY transaction.trandate DESC FETCH FIRST {count} ROWS ONLY"
    };

Best Practices

Use lowercase for keys
Use consistent aliasing (e.g., Customer_name)
Include FETCH FIRST for limiting results
Use WHERE filters appropriately

Moving to External Files (Recommended)

Create a JSON file and store your mappings externally:

    {
      "top customer": "...",
      "customer Count": "...",
      "inventory value": "..."
    }

Then load it inside your script using:

    const queryFile = file.load({ id: 'SuiteScripts/SankalvaX AI Agent/queries.json' });
    const mappings = JSON.parse(queryFile.getContents());

Conclusion

By following these instructions, you can extend the SankalvaX AI Agent to answer additional business questions by adding new queries with keys. Always test your queries before production deployment and consider external mapping for maintainability.