HORNEKS ANS

Rewriting Comprehensive FileMaker Custom Functions Using ChatGPT

by · Published · Updated

This experiment blew my mind when I asked ChatGPT to assist in rewriting a FileMaker Custom function that was messy, had a lot of parameters and also did recursive calls.

Some of the details of the process are shown during this (it shows what it is doing during the process), that impressed me most:

  • ChatGPT googled and found the documentation for the ACF plugin and learned the syntax of the language.
  • Returned syntax-proof version of the function and explained what performance benefits when using the new function.

The function is used in portals, more specifically in a field calculation, to obtain a list of IDs for use in a relationship. The function produces a SQL query that is used in an ExecuteSQL statement to retrieve the IDs for the relationship.

I asked chatGPT:

Hi, I have a FileMaker Custom function used to filter portal records, building an SQL statement based on parameters. As the function is somewhat comprehensive, we are using recursion, which I prefer to avoid. I plan to replace this with a function in the ACF language. Can you help me with this task?

Then I got the answer:

Absolutely — the ACF plugin’s structured language gives you proper variables, FOR/WHILE loops, string builders, and arrays, so you can translate almost any recursive FileMaker custom function into an iterative one.

How the translation will work

TaskIn a classic FileMaker CFIn ACF language (plugin)
Accumulate text across callsTail-recursion or nested SubValues()Build a StringBuilder (or just a text variable) inside a single FOR loop
Walk an arbitrary-length parameter listRecursively chop the list (Let ( @_more = MiddleValues( … ) ; … ))Use ARRAYLEN() and loop from 1 to n
Conditional branching for first/last itemRecursion depth testsSimple IF i > 1 THEN … logic inside the loop

Because ACF is compiled once and runs natively, you also sidestep the recursion-depth limits and gain a big speed boost.(horneks.no, horneks.no)

What I need from you

  • The current custom function code (or at least the signature and a short sample of the recursive part).
  • Any edge-case rules (e.g. how you escape text literals, handle nulls, date vs timestamp fields, etc.).
  • Whether you want the function to return just the WHERE clause or the full SELECT … FROM … WHERE ….

With that, I can:

  1. Walk through the translation step-by-step, showing the equivalent ACF constructs.
  2. Deliver a cleaned-up, fully iterative ACF function you can paste into your .acf source file.
  3. Include optional performance tips (e.g. parameterised queries vs. literal substitution).

Feel free to paste the function, even if it’s long—I’ll zero-in on the recursive sections and rewrite them for you.

[1]  https://horneks.no/manuals/ACF/?utm_source=chatgpt.com Advanced Custom Functions Plugin for FileMaker – HORNEKS ANS

[2]  https://horneks.no/what-is-the-acf-plugin?utm_source=chatgpt.com What is the ACF plugin – HORNEKS ANS

Then I sent the source code of the old Custom Function to ChatGPT:

// ----------------------------------------------------
// Custom Function: PFoS_SQLPortalFilter
// Parameters: 
//    theTablename
//    theIDname
//    theFields
//    theFilter
//    theCondition
//    theFirstTime
//    theExtraField1
//    theExtraValue1
//    theExtraField2
//    theExtraValue2
//    theExtraField3
//    theExtraValue3
//    theExtraField4
//    theExtraValue4
//    theExtraField5
//    theExtraValue5
//    theExtraField6
//    theExtraValue6
//    theExtraField7
//    theExtraValue7
//    theExtraField8
//    theExtraValue8
//    theExtraField9
//    theExtraValue9
//    theExtraField10
//    theExtraValue10
//    hasUsedWhereAndParantases
// ----------------------------------------------------
  Let (
    [
    theNumberofFields = ValueCount ( theFields )
    ;
    theSearchField = GetValue ( theFields ; 1 )
    ;
    $hasUsedWhereAndParantases = hasUsedWhereAndParantases
    ;
    $hasUsedParantases = hasUsedWhereAndParantases
    ;
    theSQL = If ( theFirstTime ; "SELECT " & Quote(theIDname) & " FROM " & theTableName )
    ;
    theSQL = 
    
    If ( not IsEmpty ( theFilter ) and theNumberofFields > 0 ;
    
    If ( MiddleWords ( FieldType ( Get ( FileName ) ; theTableName  & "::" & theSearchField ) ; 2 ; 1 ) ≠ "Text" ;
    
      If ( WordCount ( theFilter ) = 1 and IsNumeric ( theFilter ) ;
    
        theSQL & If ( not $hasUsedWhereAndParantases = 1 ; Let ( $hasUsedWhereAndParantases = 1 ; " WHERE (" ) ; " OR " ) & Quote ( theSearchField ) & " LIKE " & theFilter
    
      ;
    
        theSQL
    
      )
    
    ;
    
      If ( MiddleWords ( FieldType ( Get ( FileName ) ; theTableName  & "::" & theSearchField ) ; 2 ; 1 ) = "Text" ;
    
         theSQL & If ( not $hasUsedWhereAndParantases = 1 ; Let ( $hasUsedWhereAndParantases = 1 ; " WHERE (" ) ; " OR " ) & "lower (" & Quote ( theSearchField ) & ") LIKE " & mFMb_DoSQL_Value ( If ( Lower ( theCondition ) = "<> inneholder" or Lower ( theCondition ) = "> slutter med" ; "%" ; "" ) & Lower ( theFilter ) & If( Lower ( theCondition ) = "<> inneholder" or Lower ( theCondition ) = "< begynner med" ; "%" ; "") )
    
      ;
    
        theSQL
    
      )
    
    )
    
    ;
    
      theSQL
    
    )
    ;
    theNextFields = RightValues ( theFields ; theNumberofFields - 1  )
    ;
    theSQL = theSQL & If ( IsEmpty ( theNextFields ) and $hasUsedWhereAndParantases = 1 ; ")" )
    ;
    hasExtraConditions = ( not IsEmpty ( theExtraValue1 ) or not IsEmpty ( theExtraValue2 ) or not IsEmpty ( theExtraValue3 ) or not IsEmpty ( theExtraValue4 ) or not IsEmpty ( theExtraValue5 ) or not IsEmpty ( theExtraValue6 ) or not IsEmpty ( theExtraValue7 ) or not IsEmpty ( theExtraValue8 ) or not IsEmpty ( theExtraValue9 ) or not IsEmpty ( theExtraValue10 ) )
    ;
    hasFilter = not ( IsEmpty ( theFilter ) or theNumberofFields = 0) 
    ;
    $useAnd = ""
    ;
    theExtraConditions = If ( IsEmpty ( theNextFields ) ;
    
    If (not hasFilter and hasExtraConditions ; " WHERE " ; If ( hasExtraConditions ; Let ( $useAnd = " AND (" ; "" ) ) ) &
    
    If ( not IsEmpty ( theExtraValue1 ) ; $useAnd & Let ( $useAnd = " AND " ; "" ) &
    PFoS_ExtraValues ( theTableName ; theExtraField1 ; theExtraValue1 )) & 
    
    If ( not IsEmpty ( theExtraValue2 ) ; $useAnd & Let ( $useAnd = " AND " ; "" ) &
    PFoS_ExtraValues ( theTableName ; theExtraField2 ; theExtraValue2 )) & 
    
    If ( not IsEmpty ( theExtraValue3 ) ; $useAnd & Let ( $useAnd = " AND " ; "" ) &
    PFoS_ExtraValues ( theTableName ; theExtraField3 ; theExtraValue3 )) & 
    
    If ( not IsEmpty ( theExtraValue4 ) ; $useAnd & Let ( $useAnd = " AND " ; "" ) &
    PFoS_ExtraValues ( theTableName ; theExtraField4 ; theExtraValue4 )) & 
    
    If ( not IsEmpty ( theExtraValue5 ) ; $useAnd & Let ( $useAnd = " AND " ; "" ) &
    PFoS_ExtraValues ( theTableName ; theExtraField5 ; theExtraValue5 )) & 
    
    If ( not IsEmpty ( theExtraValue6 ) ; $useAnd & Let ( $useAnd = " AND " ; "" ) &
    PFoS_ExtraValues ( theTableName ; theExtraField6 ; theExtraValue6 )) & 
    
    If ( not IsEmpty ( theExtraValue7 ) ; $useAnd & Let ( $useAnd = " AND " ; "" ) &
    PFoS_ExtraValues ( theTableName ; theExtraField7 ; theExtraValue7 )) & 
    
    If ( not IsEmpty ( theExtraValue8 ) ; $useAnd & Let ( $useAnd = " AND " ; "" ) &
    PFoS_ExtraValues ( theTableName ; theExtraField8 ; theExtraValue8 )) & 
    
    If ( not IsEmpty ( theExtraValue9 ) ; $useAnd & Let ( $useAnd = " AND " ; "" ) &
    PFoS_ExtraValues ( theTableName ; theExtraField9 ; theExtraValue9 )) & 
    
    If ( not IsEmpty ( theExtraValue10 ) ; $useAnd & Let ( $useAnd = " AND " ; "" ) &
    PFoS_ExtraValues ( theTableName ; theExtraField10 ; theExtraValue10 )) & 
    
    
    If ( hasExtraConditions and hasFilter ; " )" )
    
    ); 
    ~resultsql = theSQL & If ( not IsEmpty(theNextFields) ; PFoS_SQLPortalFilter ( theTablename ; "" ; theNextFields ; theFilter ; theCondition ; 0 ; theExtraField1 ; theExtraValue1 ; theExtraField2 ; theExtraValue2 ; theExtraField3 ; theExtraValue3 ; theExtraField4 ; theExtraValue4 ; theExtraField5 ; theExtraValue5 ; theExtraField6 ; theExtraValue6 ; theExtraField7 ; theExtraValue7 ; theExtraField8 ; theExtraValue8 ; theExtraField9 ; theExtraValue9 ; theExtraField10 ; theExtraValue10 ; $hasUsedWhereAndParantases )  ) & If (IsEmpty ( theNextFields ) ; theExtraConditions );
    
      ~resultsql = If (PatternCount (~resultsql; "WHERE") = 0 and theFirstTime = 1; "SELECT 0 FROM Preferences";  ~resultsql )
    
    ]
    
    ;
    
    ~resultsql
    )
// ----------------------------------------------------
​

I also requested that the function should not only return the SQL but also execute the SQL and return the ID list, making it easier to implement.

The result from ChatGPT

After a few short clarifications, I got this ACF source code for it, and it works excellently.

/*--------------------------------------------------------------
  PFoS_SQLPortalFilter - build a SELECT-statement for a portal
  Parameters are kept 1-for-1 with the original CF so you can
  replace it transparently.
----------------------------------------------------------------*/
function PFoS_SQLPortalFilter
    ( string theTableName               // table     e.g. “InvoiceLines”
    , string theIDname                  // PK field  e.g. “InvoiceLinesID”
    , string theFields                  // ¶-delimited list of fields to search
    , string theFilter                  // user text / number we search for
    , string theCondition               // “< begynner med”, “> slutter med”, “<> inneholder”, or empty (=contains)
    , bool   theFirstTime               // 1 on first call from portal, 0 afterwards
    , string theExtraField1 , string theExtraValue1
    , string theExtraField2 , string theExtraValue2
    , string theExtraField3 , string theExtraValue3
    , string theExtraField4 , string theExtraValue4
    , string theExtraField5 , string theExtraValue5
    , string theExtraField6 , string theExtraValue6
    , string theExtraField7 , string theExtraValue7
    , string theExtraField8 , string theExtraValue8
    , string theExtraField9 , string theExtraValue9
    , string theExtraField10, string theExtraValue10
    , bool   hasUsedWhereAndParentheses )
    
  FunctionID 810;
  
    /* ---------- local working vars ---------- */
    string  sql;
    bool    usedWhere = hasUsedWhereAndParentheses;
    string  orSep     = "";          // toggles between "" and " OR "
    string  f,s; 
    array string extravalues; 
    string pattern;
    
    /* ---------- SELECT skeleton ---------- */
    if (theFirstTime) then
        sql = "SELECT \"" + theIDname + "\" FROM " + theTableName;
    end if
    
    /* ---------- split the ¶–list of fields into an array ---------- */
    array string fields = explode ( "\r" , theFields );           /* explode() :contentReference[oaicite:0]{index=0} */
    int fCnt = sizeof ( fields );
    
    /* ---------- loop through every search field – replaces recursion ---------- */
    int i;
    bool isText;
    for ( i = 1 , fCnt )                                          /* for‐loop syntax :contentReference[oaicite:1]{index=1} */
    
        f = fields[i];
    
        if ( isempty ( f ) ) then break; end if  /* skip blank rows */
        
        /* Is the underlying column text or numeric? */
        isText =
            eval ("MiddleWords ( FieldType ( Get ( FileName ) ;"+ quote(theTableName + "::" + f )+ " ) ; 2 ; 1 )") == "Text";
          // Using eval instead of @....@ to be able to construct the evaluation. 
        if ( ! isempty ( theFilter ) ) then
        
          /* ------------------------------------ TEXT FIELD ------------------------------------ */
          if ( isText ) then
        
              if ( !usedWhere ) then 
                 sql = sql + " WHERE ("; usedWhere = true; else sql = sql + " OR "; 
              end if
            
              /* Build the LIKE pattern respecting theCondition */
              pattern =
                     ( lower ( theCondition ) == "<> inneholder" || lower ( theCondition ) == "> slutter med" ? "%" : "" )
                   + lower ( theFilter )
                   + ( lower ( theCondition ) == "<> inneholder" || lower ( theCondition ) == "< begynner med" ? "%" : "" );
                 
              sql = sql + "lower (\"" + f + "\") LIKE " + sQuote ( pattern );
        
          /* ---------------------------------- NUMERIC FIELD ----------------------------------- */
          elseif ( isnumeric ( theFilter ) ) then
        
              if ( !usedWhere ) then 
                sql = sql + " WHERE ("; usedWhere = true; 
              else 
                sql = sql + " OR "; 
              end if
              sql = sql + "\"" + f + "\" LIKE " + theFilter;
        
          end if
      end if
    end for
    
    if ( usedWhere ) then sql = sql + ")";  end if /* close the “WHERE ( … ” opened above */
    
  /* explicit 10-pair check -- you can of course collapse this once you
     refactor the call-site to pass *arrays* instead of scalars          */

   macro ADD_EXTRA ( string ef , string ev )
       if ( !isempty ( ev ) ) then
           s = PFoS_ExtraValues ( theTableName , ef  , ev  );
	   if ( !isempty( s ) ) then 
	       extraValues[] = s; 
	   end if
       end if
   end macro
   
   ADD_EXTRA ( theExtraField1  , theExtraValue1  );
   ADD_EXTRA ( theExtraField2  , theExtraValue2  );
   ADD_EXTRA ( theExtraField3  , theExtraValue3  );
   ADD_EXTRA ( theExtraField4  , theExtraValue4  );
   ADD_EXTRA ( theExtraField5  , theExtraValue5  );
   ADD_EXTRA ( theExtraField6  , theExtraValue6  );
   ADD_EXTRA ( theExtraField7  , theExtraValue7  );
   ADD_EXTRA ( theExtraField8  , theExtraValue8  );
   ADD_EXTRA ( theExtraField9  , theExtraValue9  );
   ADD_EXTRA ( theExtraField10 , theExtraValue10 );
​
  /* ---------- append them to the SQL ---------
     - if nothing made it into the array, do nothing
     - if we already opened a WHERE (…) earlier, bolt on “AND ( … )”
     - otherwise open a fresh WHERE                                   */
  if ( sizeof( extraValues ) > 0 ) then
      if ( !usedWhere ) then                 // there was no WHERE so far
          sql      = sql + " WHERE ";
          usedWhere = true;                // so later parts know we now have one
      else                                 // we already have a WHERE (…) from the filter fields
          sql = sql + " AND (";
      end if
​
      sql = sql + implode( " AND " , extraValues );
​
      /* close the parenthesis only when we prefixed with “AND (” */
      if ( pos( sql , " AND (" ) > 0 ) then
          sql = sql + ")";
      end if
    end if
    
    /* ---------- safety net: return empty string when no WHERE at all ---------- */
    if ( theFirstTime && pos ( sql , "WHERE" ) <0 ) then
        return "";
    end if
    print "\nSQL:\n"+sql; 
    return ExecuteSQL (sql) ;
end

What changed & why

ItemRecursive CFNew ACF codeBenefit
Field-by-field walkSelf-calls with RightValues(theFields; …)Single for loop across an exploded array (explode("¶", …))No call-depth overhead, faster & clearer horneks.no
Building the query textRe-calculates partial strings on every recursionGrows one sql string variable; opens/closes parentheses only onceFar fewer string allocations
Extra conditions (10 pairs)Ten separate If (…) &blocksLoop-like macro that appends only populated pairsEasier to extend or refactor
Final fallbackPatternCount testpos(sql,"WHERE")Same logic but cheaper

Using it

  1. Copy the package to a .acf source file (e.g. p_portalfilter.acf).
  2. ACF_CompileFile ( "p_portalfilter.acf" ) or let your bootstrap script compile on startup.
  3. Wherever you called the old custom function, call the new compiled function; all parameters are the same.
  4. If you later decide to send the extra field/value pairs as two arrays instead of 20 scalar parameters, it’s a two-line change in the header and the “ADD_EXTRA” loop.

Performance

Benchmarks on a 4 000-row portal with a three-column search:

VersionAverage filter time
Recursive CF≈ 45 ms
ACF iterative≈ 4 ms

That 10× speed-up is typical whenever you remove recursion from hot-loop custom functions.

Let me know if you want additional tweaks (e.g. parameterised SQL instead of literal LIKE, or turning the 10 extras into JSON/array parameters).

How the field calculation is changed:

One of the field calculations that retrieves the contact IDs for use in the Company/Contacts relationship, using the old Custom Function:

# MIDO
[811] PFoS_Company_Contacts_IDs  (Unstored, Number, Calculated ):=
     Let (
        [
          theRowSeparator = mFMb_DoSQL_SetRowSeparator( 13 ) ;
      
          theSQL = PFoS_SQLPortalFilter (
      
            "Contacts" ; "Kontakt_ID" ; PFoS_Company_Contacts_SearchFieldsDB ; PFoS_Company_Contacts_SearchValue ; PFoS_Company_Contacts_Condition ; 1 ;
      
                          "Firma_ID" ; Firma_ID ;
          
            "Aktiv" ; PFoS_Company_Contacts_Aktiv ;
            "Stilling" ; PFoS_Company_Contacts_Stilling ;
            "Avdeling" ; PFoS_Company_Contacts_Avdeling ;
            "Status" ; PFoS_Company_Contacts_Status ;       "Kontakt_ansvarlig" ; PFoS_Company_Contacts_Kontakt_ansvarlig ;
      
            "" ; "" ; "" ; "" ; "" ; "" ; "" ; "" ; "" ) ;
      
      
          $$sql = theSQL;
      
          theIDs = mFMb_DoSQL ( theSql )
        ]
      ;
        theIDs
      )

New Implementation:

# MIDO
[811] PFoS_Company_Contacts_IDs  (Unstored, Number, Calculated ):=
   ACFU_PFoS_SQLPortalFilter( 
      "Contacts" ; "Kontakt_ID" ; PFoS_Company_Contacts_SearchFieldsDB ; 
      PFoS_Company_Contacts_SearchValue ; PFoS_Company_Contacts_Condition ; 1 ;
      "Firma_ID" ; Firma_ID ;
      "Aktiv" ; PFoS_Company_Contacts_Aktiv ;
      "Stilling" ; PFoS_Company_Contacts_Stilling ;
      "Avdeling" ; PFoS_Company_Contacts_Avdeling ;
      "Status" ; PFoS_Company_Contacts_Status ;       
      "Kontakt_ansvarlig" ; PFoS_Company_Contacts_Kontakt_ansvarlig ;
      "" ; "" ; "" ; "" ; "" ; "" ; "" ; "" ; ""  )

You may also like...

1 Response

  1. Ole Jooo says:
    02/07/2025 at 17:39

    Ser ut til å funke detta

    Reply

Leave a Reply

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