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.

In earlier versions of the plugin, we used array functions like explode to convert the result set into arrays effortlessly.

After the introduction of the INTO statement in the SQL itself, the population of arrays goes automatically.

Also: ChatGPT can help create the SQL; we have made a template to drop into ChatGPT with instructions on how to do it.

Drop this template into ChatGPT, and then you can easily create the SQL, declarations, and more.

// ---- Arrays (start empty by definition) ----
// Declare one ARRAY per field you want to SELECT.
// Use <alias><FieldName> naming for clarity.
// Adjust types as needed: STRING, DATE, LONG, FLOAT, BOOL, etc.

ARRAY STRING aliasField1, aliasField2;
ARRAY DATE   aliasDateField;
ARRAY LONG   aliasCountField;
ARRAY FLOAT  aliasAmountField;

// ---- Named placeholder input(s) ----
// Example: filter key for WHERE clause
STRING prefix;

// ---- SQL with table aliases ----
STRING sql =
"SELECT
    alias.Field1,
    alias.Field2,
    alias.DateField,
    alias.CountField,
    alias.AmountField
FROM TableName AS alias
-- Optional joins:
-- INNER JOIN OtherTable AS other ON other.FK = alias.PK
WHERE alias.PrimaryKey = :prefix
INTO
    :aliasField1,
    :aliasField2,
    :aliasDateField,
    :aliasCountField,
    :aliasAmountField";

Now when we have this in place, we can just ask ChatGPT to help create a SQL that populates the arrays automatically:

Could you create declarations, SQL, the INTO part to retrieve the values into the arrays? 
The variable "prefix" is used in the where clause using the named placeholder logic. 
Use list and cart as table aliases. 
ShoppingList::PrimaryKey 
ShoppingList::ShoppingDate 
ShoppingList::Description 
ShoppingCart::Item 
ShoppingCart::Num 
ShoppingCart::PriceEach 
ShoppingCart::ItemFK 
ShoppingCart::Shop 
ShoppingCart::ShoppingListFK 
ShoppingCart::LineSum

Response from ChatGPT:

// ---- Arrays (start empty by definition) ----
ARRAY STRING listPrimaryKey, listDescription;
ARRAY DATE   listShoppingDate;

ARRAY STRING cartItem, cartItemFK, cartShop, cartShoppingListFK;
ARRAY LONG   cartNum;
ARRAY FLOAT  cartPriceEach, cartLineSum;

// ---- Named placeholder input ----
// prefix = the PrimaryKey of the ShoppingList to filter on
STRING prefix;

// ---- SQL with table aliases ----
STRING sql =
"SELECT
    list.PrimaryKey,
    list.ShoppingDate,
    list.Description,
    cart.Item,
    cart.Num,
    cart.PriceEach,
    cart.ItemFK,
    cart.Shop,
    cart.ShoppingListFK,
    cart.LineSum
FROM ShoppingList AS list
INNER JOIN ShoppingCart AS cart
    ON cart.ShoppingListFK = list.PrimaryKey
WHERE list.PrimaryKey = :prefix
INTO
    :listPrimaryKey,
    :listShoppingDate,
    :listDescription,
    :cartItem,
    :cartNum,
    :cartPriceEach,
    :cartItemFK,
    :cartShop,
    :cartShoppingListFK,
    :cartLineSum";

After this call, all the results are nicely populated in the arrays, ready for more processing.