HORNEKS ANS

ACF Library

SendChatRequestMailDialogue

Back to List

Description: Complete Chat function to be built into an e-mail client having chatGPT assist in the mail composition.

FileMaker Prototype: 

Set Variable [$res; ACF_Run("SendChatRequestMailDialogue"; string_mailPriKey)]

Category: OPENAI

Dependencies

  • CreateAiJSON: Generate a parameterized request object with the standard OpenAi parameters
  • ScanDefenitions: Extract references in an ai-prompt, to generate ref abstracts
  • AIfetchImages: Ask DALL•E•3 API to get an image based on descriptiom, update image tag in document.

NOTE: This function serves as a reference template. To use it effectively, you may need to adjust certain parts of the code, such as field names, database structures, and specific data lists to fit the requirements of your system. This customization allows the function to work seamlessly within your environment.

SendChatRequestMailDialogue Function

The SendChatRequestMailDialogue function enables comprehensive chat handling using the OpenAI API to provide ChatGPT-like assistance in composing emails. This function offers the following capabilities:

  • Context Setting: Incorporates abstracts from customer information and referenced products.
  • Reference Integration: Uses @-notation to include abstracts from a separate definitions table.
  • Chat History Maintenance: Retains chat history for each email individually, making subsequent prompts for changes intuitive and context-aware.
  • Language Flexibility: Allows selection of a target language and supports dynamic language switching if the user prefers another language.
  • Multilingual Contexts: Handles product abstracts or customer contexts in mixed languages seamlessly.
  • Response Segmentation: Separates the API's response into email content, comments, and title, storing these in distinct fields in the email table.
  • Image request handling: When user request an image as part of the request, an image description is returned in an image tag. We then call the AIfetchImages function to ask DALL•E•3 API to create the image, and reference it in the mail content.

Function source:

// Global defenitions

string g_json_history, g_emailtext;

// Accessor from FM to the global variables
function getjsonHistory ()
    return g_json_history; ;
end

function getEmailtext ()
    return g_emailtext; 
end

/*
    Main Entry SendChatRequestMailDialogue
*/
function SendChatRequestMailDialogue ( string mailPriKey )

   JSON msgSection, quest, background, defs; 
   string pre, sql, url, err,restxt, emailcontent, comments,res, backgroundRef;
   string history = Email_Messages::ChatHistoryJSON;
   bool FirstRound, SwitchedTarget=False; 
   string model = "gpt-4o";
   float temp = 0.7 ; 
   int maxtokens = 2000; 
   int i, x, j; 
   if (history != "" ) then
      msgSection = history; 
      FirstRound = false; 
   else
      FirstRound = True; 
   end if
   // Global initialization, as backup if sql fails. 
   g_json_history = ""; 
   g_emailtext = ""; 
   url = "https://api.openai.com/v1/chat/completions";   
   string prompt = Trimboth(Email_Messages::ChatPrompt); 
   string Language = Email_Messages::TargetLanguage; 
   if ( Language == "") then
      Language = "Norwegian";
   end if
   quest = CreateAiJSON ( 0,  maxtokens, model, 0, temp, 1.0); 
   if ( FirstRound ) then
      /*
         System message that instructs the chat endpoint to return the result in a certain way. 
         Asked to keep image description in english, so that we can switch language in the email without
         redoing the image request to DALL•E 3 later. 
      */
      
      quest["messages[]"] = JSON ( 
          "role", "system", 
          "content", "You are a professional copywriter crafting emails. 

Use placeholder ++name++ for the recipient name in the start. Exclude any closing statements or sign-offs. 
Wrap the email content in <email> tags, comments in <comments> tags, and title in <title> tags. 

Generate content within <image> tags **only** when explicitly requested by the user. Do not imply image generation based on context or description unless explicitly asked for. 

If an image is explicitly requested, provide a description within <image> tags. Always write the content of the <image> tag in English, regardless of the target language of the email. The <image> tag should contain only the description of the image and nothing else. Include a Markdown image tag in the email content like this: `![Description](++image_url++)`. Use ++image_url++ as a placeholder for the image URL. 

Do not nest the tags. Use Markdown for formatting. Target language is " + Language
      );
         
     msgSection["messages[]"] = quest["messages[1]"]; // Chat History
     msgSection["Language"] = Language;  
     // Add background references if any. Crafted in a separate JSON on the mail record before calling this. 
     backgroundRef = Email_Messages::BackgroundReferenceJSON;
     if (backgroundRef != "" ) then
      background = backgroundRef; // Parse JSON
        x = sizeof (background["messages"]); 
        for ( i = 1, x) 
         pre = format ("messages[%d]", i);
           quest["messages[]"] = background[pre]; // Abstract of product X: bla bla
         msgSection["messages[]"] = background[pre]; // Chat History
        end for
     end if
     // See if we have any @-references to defenitons from the prompt. 
     defs = ScanDefenitions ( prompt); 
     prompt = defs["prompt"]; // Removed the "@" prefixes for words from defenitons
     if ( defs["hits"] != "None") then
          x = sizeof (defs["messages"]); 
          for ( i = 1, x) 
              pre = format ("messages[%d]", i);
             quest["messages[]"] = defs[pre]; // Abstract of product X: bla bla
              msgSection["messages[]"] = defs[pre]; // Chat History
          end for
     end if
     
    else
      x = sizeof (msgSection["messages"]); 
      for ( i = 1, x) 
         quest["messages[]"] = msgSection[format ("messages[%d]", i)]; 
      end for
      if (msgSection["Language"] != Language ) then
         Prompt += ((Prompt == "" || Right(Prompt,1)==".")?"":"."); 
         Prompt += " We have switched target language to " + Language; 
         Prompt = trimleft(Prompt);
      end if
   end if
   
   quest["messages[]"] = JSON ( "role", "user", "content", prompt); 
   quest["n"] = 1; 
   
   print "\nRequest:" + string (quest); 
   
   string APIkey = ExecuteSQL ("SELECT AI_APIKey FROM Preferences"); 
   string hdr = "Content-Type: application/json\nAuthorization: Bearer "+APIkey;
   string title, imageDesc; 
   string respons = HTTP_POST ( url, string (quest), hdr);
   JSON resp; 
   resp = respons; 
   // Check for errors 
   if ( list_keys (resp) == "error") then
      err = resp["error.message"];
      print "\nError responce: " + err; 
      alert (err);
      return "Error"; 
   else
      // Handle the result
      restxt = resp["choices[1].message.content"];
      print "\n"+restxt;
      // Update the history 
      msgSection["messages[]"] = JSON ( "role", "user", "content", prompt); 
      msgSection["messages[]"] = resp["choices[1].message"];
      history = string ( msgSection);
      // Update the database with the result. 
      emailcontent = trimboth( between ( restxt, "<email>", "</email>") );  
      if (emailcontent == "" ) then
         emailcontent = restxt; 
      end if
      comments = trimboth( between ( restxt, "<comments>", "</comments>") );  
      title = trimboth( between ( restxt, "<title>", "</title>") );  
      imageDesc = trimboth(between(restxt, "<image>", "</image>"));
      if ((Email_Messages::Subject != "New Email" && ! SwitchedTarget ) || (title=="")) then
         title = Email_Messages::Subject; // retain original subject if altered in the layout. 
      end if
      
      if ( imageDesc != "" ) then
         emailcontent = AIfetchImages (imageDesc,  emailcontent, "++image_url++");
      end if
      // Update the mail record in FM. 
      try
         res = ExecuteSQL ( "UPDATE Email_Messages SET ChatHistoryJSON = :history , ChatResult=:emailcontent, 
         ChatComments = :comments, Subject = :title, ImageDescription = :imageDesc
         WHERE PrimaryKey=:mailPriKey");
      catch
      // Having issues with emoticons in the update message. Set backup fields. 
      // And try updating the other fields. 
         g_json_history =  history; 
         g_emailtext = emailcontent; 
         res = ExecuteSQL ( "UPDATE Email_Messages SET  
         ChatComments = :comments, Subject = :title, ImageDescription = :imageDesc
         WHERE PrimaryKey=:mailPriKey");
         throw "Error updateing result: "+last_error; 
      end try
      
   end if
   
   return "OK"; 
   
end

Example Usage

Set Variable [$res; ACF_Run("SendChatRequestMailDialogue"; mail::PrimaryKey)]

A demo application including this functionality will be provided.

Requirements

To use this function, you need an API key from OpenAI, obtainable from their platform:
https://platform.openai.com

Additionally, a small deposit is required to activate the account. In extensive testing, the cost was minimal—for example, $0.08 over a few days of frequent use.

For detailed information and a practical example, visit:
Using OpenAI in FileMaker Applications to Assist in Writing Emails

Issue solved, Emoticons in SQL update

I discovered when testing those routines, that the final update SQL to the FileMaker record sometimes failed because the email content or the chat history contained emoticons. This is an issue with the FileMaker SQL engine, and has nothing to do with the plugin itself. However, I implemented this handling.

First, I implemented two global variables containing the email content and the chat history, and two getter functions. 

string g_json_history, g_emailtext;

function getjsonHistory ()
    return g_json_history; ;
end

function getEmailtext ()
    return g_emailtext; 
end

Then the part in the function where we do the SQL update: 

// Oppdater e-postposten i FM.
try
    res = ExecuteSQL ( "UPDATE Email_Messages SET ChatHistoryJSON = :history , ChatResult=:emailcontent, 
    ChatComments = :comments, Subject = :title, ImageDescription = :imageDesc
    WHERE PrimaryKey=:mailPriKey");
catch
    // Problemer med emoticons i oppdateringsmeldingen. Sett backup-felt.
    // Og prøv å oppdatere de andre feltene.
    g_json_history =  history; 
    g_emailtext = emailcontent; 
    res = ExecuteSQL ( "UPDATE Email_Messages SET  
    ChatComments = :comments, Subject = :title, ImageDescription = :imageDesc
    WHERE PrimaryKey=:mailPriKey");
    throw "Error updateing result: "+last_error; 
end try

This means, that if the SQL update fails, control is passed to the CATCH block, we assign the global variables with the content and history. Then do another update where we leave out those fields, and throw the exception again.

And finally, in the FileMaker script: 

Commit Records/Requests [With dialog: Off ]

Set Variable [ $$res ; Value: ACF_run("SendChatRequestMailDialogue"; Email_Messages::PrimaryKey) ]

If [ $$res <> "OK" ]
    Set Variable [ $history ; Value: ACF_run("getjsonHistory") ]
    Set Variable [ $emailContent ; Value: ACF_run("getEmailtext") ]

    If [ $history <> "" ]
        Set Field [ Email_Messages::ChatHistoryJSON ; $history ]
    End If

    If [ $emailContent <> "" ]
        Set Field [ Email_Messages::ChatResult ; $emailContent ]
    End If
End If

Set Field [ Email_Messages::ChatPrompt ; "" ]
Go to Field [ Select/perform ]
Set Field [ Email_Messages::ChatResult ; Substitute(Email_Messages::ChatResult; "++name++"; Contacts::FirstName) ]

If [ $$ImgURL <> "" ]
    Set Field [ Email_Messages::ImagePath ; $$ImgURL ]
    # for future change in image size
    Set Field [ Email_Messages::ActiveImage ; $$ImageName ]
    Set Field [ Email_Messages::GeneratedImage ; $$ImageName ]
    # The WEB viewer
    Set Field [ Email_Messages::WebViewerImage ; ACF_run("base64WebViewerImage"; $$ImgURL) ]
End If

Set Field [ Email_Messages::Console ; ACF_GetConsoleOutput ]

Commit Records/Requests [With dialog: Off ]
Back to List