ChatGPT for FileMaker: Practical Applications

---

An Introduction to ChatGPT

By now, you’ve likely encountered ChatGPT in one form or another as it continues to make headlines and captivate audiences worldwide.

As a quick refresher, ChatGPT is a groundbreaking AI language model developed by OpenAI, boasting exceptional natural language processing capabilities. It can comprehend and generate human-like language, answer questions, engage in conversations, summarize text, and even generate text and code based on given prompts.

For instance, I can ask it a question about Soliant:

My first encounter with OpenAI models was in 2020, following the release of GPT-3. It is amazing to see how far the model has progressed. I remember being fascinated by GPT-3 at the time, and now it seems insignificant in comparison to GPT-3.5-turbo (the current public ChatGPT) and GPT-4 (the upcoming model of ChatGPT).

From Data to Insights

This blog post will explore the exciting possibilities of building AI-powered experiences into FileMaker applications, focusing on boosting the user’s and developer’s productivity.

Most of the focus in the community has been around how to use ChatGPT to help write code (scripts and calculations mostly), and we will show some of that in this blog post. First, though, we want to focus on an example of how it can benefit the end user.

Most of the solutions created on the FileMaker platform are somewhat transactional in nature: they follow a particular workflow to get to a result. Taking something out of the warehouse and getting it delivered. Moving from a quote through an order to produce an invoice.

Along the way, a lot of valuable data is created and stored, but that information is not usually mined well enough to unlock all of its potential.

Part of the problem is knowing how to ask questions of your data. And that is a good example of where we can use something like what ChatGPT delivers.

For the User

We want the user to write or speak a sentence in plain English to query the FileMaker application for data and get an accurate response.

Our demo lets the user type their request in, but we could certainly use a service like AWS Transcribe to do the speech-to-text transformation if needed. (This is the reverse of what we did with Billy Bass for the 2018 Devcon, where we transformed text to speech with AWS Polly) Here’s how it works:

1. The user enters a question or command and triggers a script.
2. OpenAI’s model reads the system message and the plain English message (what the user wants) and produces a SQL query.
   1. Retrieves the schema of the file’s tables using the FileMaker system tables (meta-tables) accessible through the ExecuteSQL() function. The result we get back uses table and field names that are those of the system and not some generic ones.
   2. Builds a system message that contains several guidelines in addition to the file’s schema. The system message sets the behavior of the AI assistant and aligns it with how we want it to read and respond to our messages.
   3. Sends the user’s request to OpenAI’s API along with the system message.
3. OpenAI’s model reads the system message and the plain English message (what the user wants) and produces a SQL query.
4. FileMaker receives and executes the SQL query generated by the AI, which results in a set of record IDs.
5. FileMaker shows the data for that list of records that match the user’s request.

Tables, Charts, and Dashboards

And while our demo shows these records in a simple list, it is not far-fetched at all to go a few steps further and display the data in a polished table in a web viewer and/or create quick charts and dashboard widgets. Get the DataTables Carafe Widget

The conversation that the user has with the FileMaker solution can be contextual; it has a conversational memory. For instance, the user can ask: “Show me the clients who live in Nebraska.” FileMaker will then show them the list.

The user can then follow up with, “Of those, show me the clients that have open invoices.” And the AI assistant will create a SQL query that narrows down the original list.

Functionality like this makes it a lot simpler for the user to dive into the data and produce insights by using their own words to explain what they want to see. Asking the right questions now becomes a reality.

For the Developer

Schema

In the example below, we have a simple schema: two tables, Person and Invoice, related on ID to ID_Person (one person can have many invoices).

For the OpenAI model to work accurately and consistently, it needs to be able to understand your schema. If you are using a conventional naming convention, then that is easier.  If you do not have a predictable naming convention, then it becomes much harder to ‘explain the rules’ to the AI model. That’s one more reason to adopt a solid set of development standards and naming conventions.

Examples of conventional database schema naming conventions of field names and base tables: Person, ID, ID_Invoice

Examples of unconventional naming conventions of field names and base tables: PER_PERSON, T10_PERSON, _person_id_pk, fk_invoice_id…

Security

Of course, executing arbitrary SQL queries is a bad recipe in any system. Still, it is acceptable here under the assumption that the database’s security privileges are properly configured (that the user cannot ask for data that it has no rights to), and given that ExecuteSQL() is limited to SELECT statements only (no data can be created or manipulated this way).

The method that we are using here only shares the schema of the database without sharing its data. We are not sending data to the cloud for it to be queried. No data privacy best practices would be broken in this case. Other methods, such as summarizing or aggregating, might rely on sending the data to OpenAI for evaluation.

Post-processing

As you will see further down, the AI assistant will generate a SQL query that uses * to return all fields on the table.  We change the received SQL query to only give us the ID field.

Examples

Example #1 – A Simple One-table Query

User: Show me the clients who live in Nebraska.
AI Assistant: ­
SELECT ID, full_name FROM PERSON WHERE state = 'Nebraska'
FileMaker: Displays the records matching the SQL query above in a layout for the Person table.

{
    "messages" : 
    [
        {
            "content" : "As a T-SQL translator, ...",
            "role" : "system"
        },
        {
            "content" : "Show me the clients who live in Nebraska.",
            "role" : "user"
        }
    ],
    "model" : "gpt-3.5-turbo",
    "temperature" : 0
}

{
    "choices" : 
    [
        {
            "finish_reason" : "stop",
            "index" : 0,
            "message" : 
            {
                "content" : "SELECT * FROM PERSON WHERE state = 'Nebraska'",
                "role" : "assistant"
            }
        }
    ],
    "created" : 1682034155,
    "id" : "chatcmpl-0000",
    "model" : "gpt-3.5-turbo-0301",
    "object" : "chat.completion",
    "usage" : 
    {
        "completion_tokens" : 11,
        "prompt_tokens" : 591,
        "total_tokens" : 602
    }
}

---

Example #2 – A More Complex Query That Involves Two Tables (a JOIN)

GPT 3.5 series model and GPT 4 can join tables easily. Previous models, such as GPT3 davinci, which I tested back in 2020, could only do that after including at least four manually typed example queries.

User: Show me the clients who have an invoice that is due
AI Assistant:
SELECT DISTINCT person.*
FROM person
INNER JOIN invoice ON person.ID = invoice.ID_Person
WHERE invoice.Due_Date < CURRENT_DATE;
FileMaker: Displays the Person records of those who have an invoice that is due based on today’s date

---

Example #3 – Conversational Memory

You can also post messages and have the OpenAI evaluate them within the context of previous ones.

In this example, I asked about the list of clients in Nebraska and then asked which ones have an invoice that is due. By preserving and resending the conversation’s messages, the AI model evaluated the new question in the context of the previous messages and replied accordingly.

Message #1

User: Show me the clients who live in Nebraska
AI Assistant:
SELECT ID, full_name FROM PERSON WHERE STATE = ‘Nebraska’
FileMaker: Displays the list of Person records where state = Nebraska (case sensitive due to SQL being case sensitive, can be tuned to be case insensitive by adding a guideline in the system message).

Message #2

User: Of these, show me the ones with an invoice that is due.
AI Assistant:
SELECT * person.ID, person.full_name
FROM person INNER JOIN invoice ON person.ID = invoice.ID_Person
WHERE state = 'Nebraska' AND Due_Date <= CURRENT_DATE;
FileMaker: Displays the list of Person records where state = Nebraska (case sensitive) and with an invoice that is due based on today’s date.

The System Message

The system message helps set the behavior of the assistant. But keep in mind, per the OpenAI docs, “gpt-3.5-turbo-0301 does not always pay strong attention to system messages. Future models will be trained to pay stronger attention to system messages.”

Check the following OpenAI documentation page for more information: https://platform.openai.com/docs/guides/chat/introduction.

Here’s an example of the system message that we’re using in the demo file.

As a T-SQL translator, you need to convert natural language prompts into valid SELECT queries for the two tables provided below. The table details are as follows in the format of: COLUMN NAME(DATA TYPE):

- INVOICE:ID(decimal),ID_Person(decimal),Issue_Date(date),Due_Date(date),Amount(decimal),Invoice_Code(decimal)

- PERSON:last_name(varchar),street_address(varchar),secondary_address(varchar),city(varchar),county(varchar),state(varchar),zip(varchar),first_name(varchar),phone(varchar),phone_formatted(varchar),ID(decimal),job_title(varchar),full_name(varchar)

When crafting your response, adhere to these guidelines:
1. Instead of using TOP and LIMIT in your queries, always use FETCH FIRST.
2. Provide only SELECT statements in your responses.
3. Return just the SQL statement code without any explanation or introduction.
4. If you are asked to limit rows, use FETCH FIRST n ROWS ONLY instead of LIMIT n
5. To get the current time, use: CURRENT_TIME
6.  To get the current timestamp, use: CURRENT_TIMESTAMP
7. To get the current user, use: CURRENT_USER
8. To get the current date, use: CURRENT_DATE
9. To add n days to dates, use: CURRENT_DATE + n
10. To subtract n days from dates, use: CURRENT_DATE - n
11. To query dates, always use CAST() for date strings. Example: CAST( '2020-01-01' AS DATE)
12. Only use date functions when needed
13. Today's date is 2023-04-20
14. When writing joins, never use aliases. When writing joins, always use full table names. Example: SELECT person.full_name, invoice.Invoice_Code FROM Invoice INNER JOIN Person ON invoice.ID_Person = person.ID;
15. When searching full names, use the full_name column if it exists.
16. Always select the primary key column (usually called \"ID\") in the main table. Don't select any other column
17. Do not append ";" at the end of the SQL statement
18. When asked about next week or other weeks. Search for the range between the Monday of that week and the Sunday of that week. To get the dates of next week: SELECT (CURRENT_DATE - MOD(NUMVAL(CURRENT_DATE) , 7-2) )+7, CURRENT_DATE - MOD(NUMVAL(CURRENT_DATE) , 7-2) )+13

Upon receiving a natural language prompt, respond ONLY with the corresponding SQL statement code that accurately fulfills each prompt's requirements while following the given guidelines. Ensure accuracy and precision in constructing your query.

Things to note about constructing guidelines:

• Guidelines are used to adjust the behavior of the AI assistant or “fix bugs”.
• Use the OpenAI playground to quickly test your system messages and their guidelines to iterate faster.
• You can ask the AI model why it responded unexpectedly; it will tell you why and mention the guideline’s number so you can improve your system message accordingly.

Summarizing Data

You can also share the data that the SQL statements generate if your application’s privacy guidelines allow it. For example, this feature would be helpful in chat-based interfaces for your custom application. The user can then click a button next to the summary to open the summarized invoices. Below is an example:

Other Use Cases

While traveling recently, I started stacking up a few receipts that I needed to submit for reimbursements later. I started thinking about some way to automate that. I got curious to see how much ChatGPT can do here, and after a few tests with prompting, I arrived at this flow:

1. FileMaker Go: Take a photo of the printed receipt + GetLiveText() to get the Text written on the receipt via Text Recognition.
2. OpenAI API: Send the system message and the scanned receipt’s text.
3. OpenAI API: Returns everything I need neatly in JSON.

The entire integration took about 20 minutes to complete. Building the same thing within FileMaker’s native logic would have taken a few days and perhaps wouldn’t have arrived at the same accuracy level. Below is an example of a receipt I took to buy an HBR magazine at the airport. The AI model was able to understand the receipt’s text, items purchased, budget category, etc.

• II
II Hudson
SEA TAC INT'L AIRPORT
17801 PACIFIC HUY SOUTH
SEATTLE, WA 98158
SALE TRANSACTION
3551653
HARVARD BUS REV
$19.95
SALES TAX
$19.95 @ 10.1%
$2.02
Items in Transaction: 1
Balance ta pay
MasterCard
CARD#:************[redacted]
CARD:Mastercard CREDIT EMV
APPROVAL CODE:[redacted]
AID: [redacted]
TVR: [redacted]
IAD: [redacted]
TSI: [redacted]
ARC: [redacted]
APPLICATION CRYPTOGRAM: [redacted]
APPLICATION LABEL: Mastercard
HUDSON
The Traveler's Best Friend
Customer Service Inquiries
www.hudsongroup.com/customers
Return Policy
www.hudsongroup.con/return-policy
$21.97
521.97
STORE TILL OP NO. TRANS. DATE
[redacted]04-16-23 08 13
[redacted] 

{
  "merchant": "Hudson",
  "location": "SEA TAC INT'L AIRPORT, 17801 PACIFIC HWY SOUTH, SEATTLE, WA 98158",
  "items": [
    {
      "name": "Dasani 20 oz",
      "price": 3.19,
      "category": "Beverage"
    }
  ],
  "tax_amount": 0.32,
  "tip_amount": 0,
  "total_amount": 3.51,
  "payment_method": {
    "type": "Card",
    "last_4_digits": "0000"
  },
  "date": "2023-04-16"
}

The system message that I used is this:

You are a system that receives the text obtained through OCR scans of paper receipts. Your job is to read and understand the scanned text and answer the following questions about the receipt:
0- Reply in JSON
1- Who is the merchant who generated the receipt?
2- What is the location of the merchant who generated the receipt?
3- What item or items were purchased? What was the price of each item? Which financial budget category does it belong to?
4- What is the total amount of the receipt?
5- What was the method of payment? If a card (mastercard, visa...) was used, what were the last 4 digits of the card?
6- What is the date of the receipt? The date format should be YYYY-MM-DD
7- If you don't know the answer or aren't confident about the answer for a NUMBER, reply 0.
8- If you don't know the answer or aren't confident about the answer for a STRING, reply "".
Be concise and accurate. ALWAYS REPLY IN JSON.

More guidelines might be needed when testing with many different types of receipts.

Demo file

We’ve spent many hours learning and researching this topic to make the best use of AI for the custom applications we build and maintain. We want to share a smaller version of the tool with you to help you start your AI journey if you haven’t started yet.

This demo file can be used to explore the OpenAI API for SQL translation, but it can also help you build SQL queries faster. Please don’t forget to avoid indirection references by dynamically referencing the field name instead of hard coding it in the generated SQL query. You can probably get that done through additional guidelines with the AI model or post-processing in FileMaker.

Demo file credentials

demo/demo

Get the demo File

How to use the demo file

1. Download the demo file by completing the form above.
   • Credentials: demo/demo
2. Create a new OpenAI account here or log in if you already have an account.
3. You should get sign-up trial credits. If you didn’t receive trial credits, set up a payment method and set the soft and hard usage limits to avoid unexpected billed amounts (Usage limits can be set here).
4. Create a new API token here → Click on Create New Secret Key.
5. Add the newly created key to the script “Convert To SQL” on line 31 in the demo file.
6. In the demo file, insert you’re your query under “Natural Language Query.”

Moving Forward with AI and ChatGPT in FileMaker

Integrating AI-powered language models like ChatGPT into FileMaker applications has opened up many opportunities to enhance the user experience, streamline workflows, and automate complex tasks. From chat-based interfaces to receipt scanning, AI has proven to be a game-changer in the world of custom applications.

As we continue to explore and push the boundaries of what AI can do for FileMaker and other applications, it’s crucial to stay abreast of the latest advancements and use cases. The examples and use cases shared in this blog post are just the tip of the iceberg, and there are undoubtedly numerous other ways to harness the power of AI to revolutionize your software.

If you’re excited about the possibilities that AI-powered experiences can bring to your FileMaker application, or if you’re interested in implementing any of the ideas shared in this blog post, don’t hesitate to reach out to us. Our team of experts is eager to help you take your software to the next level with cutting-edge AI integrations. Contact us today to discuss your project and discover how we can make AI work for you.

---

Resources

• Overview of OpenAI’s models: https://platform.openai.com/docs/models/overview
• OpenAI API References – Chat Completion: https://platform.openai.com/docs/api-reference/chat/create
• OpenAI pricing page: https://openai.com/pricing
• Prompt engineering #1: https://help.openai.com/en/articles/6654000-best-practices-for-prompt-engineering-with-openai-api
• Prompt engineering #2: https://en.wikipedia.org/wiki/Prompt_engineering
• OpenAI API Data Usage policies: https://openai.com/policies/api-data-usage-policies
• Prompt injection: https://en.wikipedia.org/wiki/Prompt_engineering#Malicious
• FileMaker SQL query builder: https://www.soliantconsulting.com/labs/sql-query-builder-for-filemaker/