[Article] Mimicking FileMaker's Replace Function

by Sean Mills

One feature that I miss from FileMaker is its ability to replace the contents of a field in a foundset, so I decided to see if I could reproduce this functionality in Servoy. If a picture is worth a thousand words, then perhaps the following video is worth...watching :o)?

View video here

How It Works (or Why the eval() Function is Your Friend)

When coding the method to replace the field contents, I first looked at Servoy's getFoundSetUpdater() function, which, as the name implies, can be used to update all of the records in the foundset. However, I ran into problems when trying to apply a formula like field.contact_first_name + " " + field.contact_last_name (I used this formula in the video), so I needed to look for another solution. The only alternative to updating all the records in the foundset (that I know of) is to perform a loop.

Here is the method I ended up with:

// When performing a replace, we are looping thru the entire foundset.
// There are two different replacement methods ("step" and "calc").

// As a reminder, both the form name and field name are stored in globals.
var formName = globals.replace_form_name;
var fieldName = globals.replace_field_name;
var record   = null;

// Disabling the transaction elements
forms.replace_dialog.elements.lbl_trans_status.visible = false;
forms.replace_dialog.elements.btn_save.visible         = false;
forms.replace_dialog.elements.btn_cancel.visible       = false;

// Actually doing the replacement
var foundCount   = databaseManager.getFoundSetCount(forms[formName].foundset);
var replaceMethod = globals.replace_method;
if (replaceMethod == "step")
{
    var startValue = globals.replace_step_start;
    var increment = globals.replace_step_increment;
    
    globals.transactionStart();
    for (var i = 1; i <= foundCount; i++)
    {
        record = forms[formName].foundset.getRecord(i);
        record[fieldName] = startValue;
        startValue += increment;
    }
}
else if (replaceMethod == "calc")
{
    var evalFormula = globals.replace_calc;
    // This substitution is for the benefit of the developer, so that they don't see
    // terms like "record.contact_last_name" in the calc field.
    // Terms like "field.contact_last_name" seem a bit more user-friendly.
    evalFormula = utils.stringReplace(evalFormula, "field.", "record.");
    
    globals.transactionStart();
    for (i = 1; i <= foundCount; i++)
    {
        record = forms[formName].foundset.getRecord(i);
        record[fieldName] = eval(evalFormula);
    }
}

// So you can see the changes to the data
databaseManager.saveData();

// Enabling the transaction elements
forms.replace_dialog.elements.lbl_trans_status.visible = true;
forms.replace_dialog.elements.lbl_trans_status.text   = "SAVE CHANGES?";
forms.replace_dialog.elements.lbl_trans_status.fgcolor = "#ff0000";

forms.replace_dialog.elements.btn_save.visible         = true;
forms.replace_dialog.elements.btn_cancel.visible       = true;

I'm going to focus on the if-else block where the replacement method is "calc" (that's where someone typed a formula). There are several ways to loop through a foundset, and my understanding is that using the getRecord() function is an efficient way of doing so because we avoid using the controller object, which affects the GUI, triggers scripts, etc. As proof, the foundset in the video was 500 records, and it didn't take too long to update them all, even on a relatively slow Mac mini.

In the above code I have set the oddly named variable record to the output of the getRecord() function. What exactly does this variable contain? Well, you can think of it as an associative array, where each "key" is the field name, and each value is the data stored in that field. For example, during the first iteration of the above for loop, the variable record might contain something like:

record["contact_id"] = 12
record["contact_name_first"] = "Bob"
record["contact_name_last"] = "Cusick"
:
:

We can also refer to the same data like so:

record.contact_id = 12
record.contact_name_first = "Bob"
record.contact_name_last = "Cusick"
:
:

The first method of referring to the data is called bracket notation, and the second method is called dot notation. If you are familiar with the Servoy method editor, then you are certainly familiar with the dot notation (in fact, most of the above code contains dot notation). However, bracket notation is much more flexible because we can use variables for the keys, which is exactly what is needed because we have absolutely no idea what field name will be selected. So, if "contact_name_last" is selected, then record[fieldName] becomes record["contact_name_last"]. If "contact_phone_work" is selected, then record[fieldName] becomes record["contact_phone_work"], and so on. So, at this point we can update any field that is selected. The only challenge now is to determine how to update the selected field.

The secret lies with a JavaScript function that doesn't even appear in the Servoy method editor (check the JS Lib section—you won't find it). This mysterious function is called eval(), and it does the following: it takes a string and executes it as if you typed it in the Servoy method editor. I lack the words to fully express how powerful this function is, but try typing the following in the method editor and then click the Run button:

2 + 2
forms.. // like forms.contacts.contact_name_last
globals.variable_here> // like globals.row_color_selected
utils.dateFormat(new Date(), "MM/dd/yyyy")

NOTE: to view the results of what you just typed, you'll probably want to wrap each formula in an application.output() function.

All of the above formulas are valid, inside the method editor, so if someone types similar formulas in a text field on a form, then we are technically outside the method editor, so we need to wrap the formula in an eval() function to make any sense of it. So, the following code will work:

record[fieldName] = eval(evalFormula);

but the following code will not:

record[fieldName] = evalFormula;

In other words, for the current record in the foundset, set whatever field was selected to the result of the formula, not the actual formula itself. That line of code almost seems too simple, but that is a testament to the power of the eval() function.

OK, this is how we update the selected field. Hooray! We're done! Time for a cruise with cheesy entertainment!

NOT...SO...FAST.

There is a problem when entering formulas that contain other fields. In the video I typed a formula involving the first name and last name. What exactly should the formula be? My first thought was this:

forms.contacts.contact_name_first + " " + forms.contacts.contact_name_last

Unfortunately, this formula does not work because of the getRecord() function I chose to use, so we need to use a slightly different syntax. To understand what the proper syntax should be, let's "hard-code" the following line:

record[fieldName] = eval(evalFormula);

By "hard-code" I mean "forget about trying to be so fancy and just type a specific example". Suppose the field that should be updated is called "contact_name_full", then the hard-coded version becomes (refer to the earlier discussion about dot notation):

record.contact_name_full = record.contact_name_first + " " + record.contact_name_last;

Aha! So that is the formula that needs to be typed in the text field.

NOT...SO...FAST.

In my opinion, that formula looks really unfriendly. What if, when a field is selected as part of the formula, we substitute something more friendly? What about this?

field.contact_name_first + " " + field.contact_name_last;

Much better.

Wrapping Up (Finally!)

To make the entering of formulas a little less painful, I have a few valuelists that contain: all of the dataproviders associated with the given form, all of the global variables in a solution, all of the global methods in a solution, and all of Servoy's built-in utils functions. To populate these valuelists (except for the utils one), I take advantage of a few special objects provided by Servoy, one of which is called alldataproviders, which oddly enough contains the names of all the dataproviders for a given form. Here is the code I used:

// building a list of the dataproviders associated with a given form
var formName = arguments[0];
var allDP     = forms[formName].alldataproviders;

// sorting the array alphabetically
allDP.sort();

// updating the appropriate valuelist
application.setValueListItems("dataproviders_all", allDP);

The code to populate the other valuelists is very, very similar.