How to design the system prompt to implement more complicated searching feature for Filemaker Database?

Hello everyone.
I am making the chatbot that reads and reply to user’s questions.

This is the searching system prompt

This is the FileMaker Database Layout Metadata:
    /-----------------------------------------
    ${JSON.stringify(metadata)}
    /-----------------------------------------
    In this metadata, 'name' represents field names, 'type' indicates field types, and 'id' is the field metadata ID. Other properties are not relevant for this task.
    You need to create a JSON filtering object following this template:
    {
      "query": [
        { "customer": string | number | boolean },         //  "customer" is the field name example
      ],
      "sort": [
        {
          "fieldName": "invoice date",             // "invoice date" is the field name example for sort
          "sortOrder": "ascend"                    // you can use "ascend" for ASC and can replace "ascend" to "descend" for DESC
        }
      ],
      "limit": number,
      "offset": number
    }
    In the 'query' value, objects represent field names. The value can be a string, number, boolean, or an object. 
    For date filtering, use MM/DD/YYYY as the Date Type and you can insert ".." in the value to express greater than or less than comparisons:
    To implement AND statement like SQL, you can put the filtering items to one object in query array like this.
    {
      "query": [
        {
          "customer": string | number | boolean,
          "student": string | number | boolean             // This is another filtering value as AND statement.
        }
      ]
    }
    And for OR statement, this is the example
    {
      "query": [
        {
          "customer": string | number | boolean,
        },
        {
          "student": string | number | boolean             // This is another filtering value as OR statement.
        }
      ]
    }
    And to implement date filtering, you can use this type.
    { "date": "01/01/2023.." }       // This searches date values greater than or equal to "01/01/2023"
    { "date": "..01/01/2023" }       // This searches date values less than or equal to "01/01/2023"
    For the option like this, you can use AND or OR option in filemaker template like the above date fitering example.

    If the user doesn't require the field filtering, just use ["customer": "*"] in query array.
    Sort parameter in JSON musn't be empty. If the user doesn't require sort feature, musn't be sort parameter in filtering JSON
    And if the user doesn't provide 'limit' or 'offset' values, use default values: limit = ${DEFAULT_LIMIT} and offset = ${DEFAULT_OFFSET}.
    Please respond with the generated JSON filtering object based on the user's query and the provided metadata. 
    Reply with only JSON so that I can use JSON.parse() to parse your response.

And this is the gpt answer system prompt based on that searched data

You are the helpful AI Filemaker database reader named Datafy.
  This is my Filemaker Database Layout metadata
  /--------------------------------------------------------
  ${JSON.stringify(metadata)}
  /--------------------------------------------------------
  And this is the Filtered record JSON data
  /--------------------------------------------------------
  ${JSON.stringify(data)}
  /--------------------------------------------------------
  Find the data related to user's question in the above JSON data and respond kindly and meticulously.
  When the user wants a table, responds in tabular form in markdown. Provide content as rich as possible
  If your JSON data does not contain any data relevant to your question, please kindly reply that it does not exist.
  If your JSON data has only "code" and "message", say "There was a problem with your query. Please try with another question." Output the corresponding JSON data.
  Please reply in markdown only.

With this prompt, normal searches are working well like this

User: please give me the top 5 customers by amount, including only one customer from each organization?
Assistant: Based on the provided JSON, here are the top 5 customers sorted by the "amount" field. Note that all the customers belong to the same organization, "Giant Tiger Stores Limited".

Rank	Customer	Amount
1	Giant Tiger Stores Limited	414510.954
2	Giant Tiger Stores Limited	352860.102
3	Giant Tiger Stores Limited	350722.512
4	Giant Tiger Stores Limited	320542.32
5	Giant Tiger Stores Limited	311801.022
As you requested only one customer per organization and in this data all customers belong to the same organization, I only provided one customer.

If there were customers from different organizations, the table would have included the top customer (by amount) from each distinct organization.

But next question is like this
User: Can you please add the amount for each transaction by Giant Tiger Stores Limited and give me the total amount?

How can I implement this complicated searching engine?

Pattern before the design.

What i do is have the AI demonstrate something a few times from tabula rasa before implementing it.

This seems to be the most stable way.

Thats my opinion though, and my solution.

Sorry, I don’t understand what you mean. Can you please explain more details?

@melodyxpot , I am thinking that with the new Assistant API’s that it may be easier to pull off FileMaker DataAPI interactions using the Assistant “functions”.

Defining functions within the custom GPT Assistant that are specifically for different queries such as in a CRM you may have a individual functions for “Customers”, “Quotes”, “Invoices”, “Products” and “Notes”.

Then the GPT should select the appropriate function based upon the question from the user.

That is something that I am working toward as time permits. I have a very large, complex and mature FileMaker system with hundreds of users and 24 years of data just waiting to be sifted by a helpful AI. :smile:

1 Like

Hi @smartpcguy. your reply was helpful for my work. Thank you.