Function: SQL Queries and Array Functions

When working with SQL queries in FileMaker, especially in the absence of native array functionality, SQL query results are typically returned as delimited lists. Handling these results requires parsing to access individual record fields. This process involves:

• Cleaning up the result to remove line breaks and applying substitutions as needed.
• Replacing record separators with line breaks to separate records.
• Iterating through the records and replacing field separators with line breaks to retrieve each field using the GetValue function.

This approach can be somewhat tedious and error-prone.

The array concept in ACF simplifies this process significantly. You no longer need to worry about line breaks in the result set as long as you use delimiters that don't exist in your retrieved data. A common practice is to use "||" for field separation and "|*|" for record separation.

With ACF, you can use array functions like explode to convert the result set into arrays effortlessly.

Example:

string resultSet = ExecuteSQL("........"; "||"; "|*|");
array string fields, recs = explode("|*|", resultSet); 
int i, noRecs = sizeof(recs); 

for (i = 1, noRecs)
    fields = explode("||", recs[i]); 
    // Now you can access each field in the record individually using fields[1], fields[2], ... fields[n]...
end for

This method makes it much easier to work with SQL query results, eliminating the need for complex parsing and simplifying your code. For more examples of SQL functions in the plugin, refer to the article about SQL functions.