Example: Dynamic Value List in FileMaker

Many individuals, including myself, have encountered challenges when dealing with the concept of dynamic Value Lists in FileMaker. Consider a scenario where you need to scan a folder and populate a Value List with the files from that folder. This allows users to select a file from a popup or dropdown list in a layout. While FileMaker is incredibly flexible when it comes to creating Value Lists based on relational structures, it lacks a straightforward method for creating Value Lists from a text source. Even using a field with an unstored calculation doesn't always work as expected.

Some have attempted to create a relationship to a second instance of a table with an "X" relationship, which, despite being considered unconventional, has sometimes worked. However, relying on such an approach can be risky, as it may not be supported in future FileMaker versions.

Fortunately, there's a more reliable solution. We can create a ValueList table with fields like Primary Key, ListName, and ListValue. Optionally, you can include a user-ID field if you wish to customize lists for different users. I've developed an ACF function that populates the ValueList table with items from a text-based list. It creates a record in the table for each list item, associating each record with the appropriate ValueList name. When you need to update the list later, you can remove unwanted records and add missing ones.

The function I'm about to introduce accomplishes precisely that. To use it, you'll need a stored calculation field that contains the list name, ensuring it's indexed as it won't change. Create a second instance of the ValueList table in the relationship graph, let's call it "ValueList_MyList1" as an example. Establish a relationship (=) to this instance from your calculated field. When defining the Manage ValueLists dialog, select only the records that follow this relationship, and you're all set.

Now, whenever you need to update a Value List from a script, run our ACF function with the items you want in a variable or a text field. The selection in your Value List will be instantly updated, making it versatile for various selection tasks with the right relationship and necessary values in your table.

In this specific example, we have a folder containing files, and we want to create a Value List that mirrors the files in that folder. We're using the List_files function to retrieve a list of files from the folder, returning a text variable with one line per file. These files serve as templates for a document system, and we want users to select a template from a dropdown list.

Here's the ACF code used for this purpose:

// Update the ValueList table to stay in sync with our File List
function UpdateValueList(string listName, string listItems)

    // Create an SQL recordset of all the currently present values.
    string sql = format("SELECT ValueText, PrimaryKey FROM ValueLists WHERE ListName = '%s'", listName);
    int rs = SQL_Query(sql);

    // Get the row count
    int rc = SQL_GetRowCount(rs);
    array string row;

    // Get an array of the items we want.
    array string NewList = explode("\r", listItems);
    string item, pk, res;
    int i, j, nlc = sizeof(NewList);
    bool found;

    // Loop through all list items we have
    for (i = 1, rc)
        row = SQL_GetRow(rs, i - 1); // First row is number 0.
        item = row[1];
        found = false;
        
        // Check if we have it in our new list.
        for (j = 1, nlc)
            if (NewList[j] == item)
                found = true;
                NewList[j] = "";  // If found, clear the item to avoid duplication.
            end if
        end for
        
        if (!found)
            // If not found, delete the item from the ValueList table.
            res = ExecuteSQL(format("DELETE FROM ValueLists WHERE PrimaryKey='%s'", row[2]));
        end if
    end for
    
    // We have removed unwanted list items; now, create those that are missing.
    for (i = 1, nlc)
        if (NewList[i] != "")
            // Adjust the SQL statement if you want to include user or function fields.
            sql = format("INSERT INTO ValueLists (ListName, ValueText) VALUES('%s', '%s')", listName, NewList[i]);
            res = ExecuteSQL(sql);
            if (res != "")
                print "\n" + sql + "\n" + res; // Print errors to the console if any.
            end if
        end if
    end for

    // Close the result set from the first Query.
    SQL_Close(rs);
    return "OK";
end

// Call this function from FileMaker using ACF_Run("UpdateTemplatesList") in a Set Variable script step.
function UpdateTemplatesList()

    string fileList = list_files ( Preferences::TemplateDir ) ; 
    string res = UpdateValueList("Templates", fileList);
    return res;
end

With this approach, you can easily update the ValueList table whenever necessary, modifying only the entries that require changes.