Configure SQL Prompt
Customize the system prompt used by ProofChat's database query tool to generate SQL statements.
Overview
The Configure SQL Prompt script allows you to customize the system prompt that guides ProofChat's AI when generating SQL queries for your database. This gives you control over SQL generation rules, syntax preferences, and query patterns specific to your FileMaker solution.
Optional Configuration
This script is optional. If not implemented, ProofChat uses a default SQL prompt optimized for FileMaker's SQL dialect.
When to Use This Script
Consider implementing this script if you need to:
- Enforce specific SQL syntax rules or conventions
- Add custom business logic constraints to generated queries
- Include solution-specific table or field naming patterns
- Provide additional context about your data model
- Restrict certain types of queries or operations
Script Requirements
Script Name
Configure SQL PromptParameters
- None required — This script takes no input parameters
Return Value
The script should return a text result containing your custom SQL prompt instructions.
Implementation
Basic Implementation
// Return your custom SQL prompt as text
"You are an expert in FileMaker's version of SQL. Generate ONLY the SQL query - no explanations, comments, or additional text.
CRITICAL RULES (MUST FOLLOW):
- Always include qualified ROWID in SELECT statements: \"tablename\".ROWID
- ROWID exception: Omit only when using aggregate functions (COUNT, SUM, AVG, etc.)
- Use unquoted ROWID after the table name: \"invoices\".ROWID NOT \"invoices\".\"ROWID\"
- Single queries only - no nested queries or subqueries
SYNTAX RULES:
- Enclose all table and field names in double quotes: \"table\".\"field\"
- Date format: DATE 'YYYY-MM-DD'
- Time format: TIME 'HH:MM:SS'
- Timestamp format: TIMESTAMP 'YYYY-MM-DD HH:MM:SS'
- Use FETCH FIRST n ROW ONLY instead of LIMIT
- Use LIKE for pattern matching (case-sensitive, no ILIKE)
- No semicolon at the end of the query"Advanced Implementation
For more sophisticated control, you can use any FileMaker scripting or calculation techniques to build dynamic prompts based on:
- Current user context or permissions
- Active layout or solution area
- Environment variables or global settings
- Business rules specific to different modules
- Time-based or conditional logic
Use standard FileMaker functions like Case(), Let(), Get() functions, custom functions, or even call other scripts to construct your prompt dynamically.
Layout Navigation
The script can use any FileMaker scripting techniques, but should not change the current layout. ProofChat calls this script in the context of the user's current session.
## Best Practices
### Keep It Focused
- Focus on SQL generation rules, not general chat behavior
- Be specific about FileMaker SQL dialect requirements
- Include examples for complex patterns
### Business Logic Integration
- Add context-specific rules based on current layout or user
- Include data validation constraints
- Specify required filters or security rules
### Testing Your Prompt
1. Implement the script with basic rules first
2. Test with various query types in ProofChat
3. Refine based on generated SQL quality
4. Add business-specific constraints as needed
## Common Use Cases
### Enforce Security Constraints
```javascript
"Always include security filters:
- Users table: Only show current user's records
- Financial data: Require date range within last 5 years
- Sensitive fields: Never include SSN or credit card fields"Solution-Specific Patterns
"Table naming conventions:
- All tables use 'tbl_' prefix
- Junction tables use '_join_' pattern
- Calculated fields end with '_calc'
Always prefer these field names when available:
- Use 'created_date' instead of 'date_created'
- Use 'pk_id' for primary keys"Performance Optimization
"Performance rules:
- Always include indexed field constraints when possible
- Limit results to 1000 rows unless specifically requested
- Avoid queries on large text fields without WHERE clauses"Troubleshooting
Script Not Being Called
- Verify the script name matches exactly: "Configure SQL Prompt"
- Check that the script is accessible to the ProofChat user account
- Ensure the script returns a text result, not empty
Poor SQL Generation
- Review your prompt for clarity and specificity
- Test with simple queries first, then add complexity
- Check FileMaker's calculation log for script errors
Conflicting Rules
- Avoid contradictory instructions in your prompt
- Test edge cases where multiple rules might conflict
- Prioritize critical business rules over style preferences
Related Configuration
- Chat System Prompt — Controls overall assistant behavior
- Chat Tools — Manages available tools including database queries
- DDL Extraction — Provides table structure information used in SQL generation