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:

Photo showing an example of asking ChatGPT a question to generate an anwser
Asking ChatGPT a question

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


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).

Photo of the Person and Invoice tables
Simple schema; Person records and their 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…


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.


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.


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
Image showing the list of
The list of ‘Person’ records where state = Nebraska

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:
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

Image showing an example of a table join
Example of a table join

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:
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.

Image showing contextual replies
Contextual Replies

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:

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.
Photo of debugging

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:

Image showing a data summarization example
Summarization 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
$19.95 @ 10.1%
Items in Transaction: 1
Balance ta pay
APPROVAL CODE:[redacted]
AID: [redacted]
TVR: [redacted]
IAD: [redacted]
TSI: [redacted]
ARC: [redacted]
The Traveler's Best Friend
Customer Service Inquiries
Return Policy
[redacted]04-16-23 08 13
  "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

Image showing the OpenAI GPT and FIleMaker Demo
OpenAI GPT and FileMaker demo

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


Get the demo File

By submitting the form, I agree to the Privacy Policy.

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.”
Covert To SQL script
Convert To SQL script

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.


10 thoughts on “ChatGPT for FileMaker: Practical Applications”

  1. Hello and thanks for the demo, unfortunately the file needs a name and password which I can’t find in the email or on your website.


    1. Allison Arthur

      Hi Ian, the demo credentials are in the blog post, but it’s a lot of content and gets lost. Here you go: demo/demo

  2. This is an outstandingly creative use of ChatGPT to aid FileMaker development and I honestly had never imagined such would be possible. Thank you for your generosity in providing the in-depth explanation and a sample file. Amazing!

    1. Thank you, Tom! Let me know if there’s a particular use case that you’re interested in having a demo file for.

  3. Muy buena idea la de utilizar LiveText para escanear facturas o notas de pedido. He creado algo parecido y funciona fantástico, lo único es que hay que hacerlo en dos pasos: 1 pasar la imagen por LiveText, añadir la orden copiar todo y pegarlo en la WEB ChatGPT, 2 copiar el resultado y pegarlo en la solución Filemaker. Pero aun y todo es muy rápido el proceso tarda 1 minuto, y hacerlo a mano 5 minutos. Todo un avance. Lo siguiente hacerlo todo en un paso.
    Gracias por la idea.
    Un saludo

    English Translation:
    Very good idea to use LiveText to scan invoices or order notes. I have created something similar and it works fantastic, the only thing is that you have to do it in two steps: 1 pass the image through LiveText, add the command copy everything and paste it in the WEB ChatGPT, 2 copy the result and paste it in the Filemaker solution. But even and everything is very fast, the process takes 1 minute, and doing it by hand takes 5 minutes. Quite an advance. The following do it all in one step.
    Thanks for the idea.
    All the best

  4. Hey Soliant team – I was having a heck of a time getting an API call to OpenAI working until I discovered this article. I was banging my head against the wall because my response was consistently saying that my JSON payload (the data portion of the request) wasn’t valid, despite using the native FM/JSON functions and running my data variable through JSON validators.

    Can someone explain to me the presence of the @ symbol in the cURL options variable? I couldn’t get this to work until I added this symbol and POOF, we’re up and running! But when I check my cURL options variable in the data viewer, it displays as “@$data”, not the actual contents of the $data variable:
    -X POST -H “Content-Type: application/json” -H “Authorization: Bearer ” –data @$data –dump-header

    But the contents of the $data variable are clearly getting passed when the request is made. I hope this isn’t a dumb question but can someone explain this to me?

Leave a Comment

Your email address will not be published. Required fields are marked *

Are You Using FileMaker to Its Full Potential?

Claris FileMaker 2023 logo
Scroll to Top