Set Field by Name Exposed

Leave a comment

1-5-2009 by Geoff Coffey

In our article about FileMaker 10, we promised some tips on using the new Set Field by Name script step, which gives FileMaker the power of indirection. This is a complicated concept, and we’ll be posting some real world examples along the way, as well. But first, we’ll give you a full explanation of the new script step and function that make indirection possible.

Set Field by Name

The core of the new feature is the Set Field by Name script step. It works almost just like the Set Field script you know and love. But with Set Field you hard code the field you want to modify. Every time your script runs, the same field is “set.”

With Set Field by Name you get to use a calculation to determine not only the value you put in the field, but also which field gets set. An example will help. Imagine you have a database of the T-shirts you sell. Each T-shirt has 5 different prices, depending on the quantity someone orders. Your SHIRTS table, thus, has these five fields:

  • Price 1
  • Price 2
  • Price 3
  • Price 4
  • Price 5

Now imagine you have a scripted process that updates one of these price fields based on a parameter. For instance, you might call the script and pass a parameter of 3 to update the third price value. Such a script might look like this:

If [ #P("which") = 1 ] Set Field [ My Table::Price 1; #P("newPrice") ] Else If [ #P("which") = 2 ] Set Field [ My Table::Price 2; #P("newPrice") ] Else If [ #P("which") = 3 ] Set Field [ My Table::Price 3; #P("newPrice") ] Else If [ #P("which") = 4 ] Set Field [ My Table::Price 4; #P("newPrice") ] Else If [ #P("which") = 5 ] Set Field [ My Table::Price 5; #P("newPrice") ] End If

>Note: The above script uses our custom functions for passing multiple script parameters. You can of course use any method you want.

Using Set Field by Name, you can make this script a lot simpler:

Set Field by Name [ "My Table::Price " & #P("which"); #P(newPrice) ]

This version of the script gets the same job done with a lot less work. Even better, it is automatically expandable. If you add a sixth price field, you can call this script with a 6 and it will set the new price field automagically.

>Note: That probably bears repeating because it really illustrates the power of indirection. This script, written today, can seamlessly work with fields that don’t even exist yet. With indirection, the direct coupling of scripts and fields is broken, enabling previously impossible powers.

The key is that it uses a calculation to determine the field name being set:

“My Table::Price ” & #P(“which”)

It is simple really. Just a text formula that results in the name of the field we want, complete with its table occurrence name.

This ability to target a field and value works in both Browse mode and Find mode, so it can be used to manipulate data generically, and to do generic find scripting.

GetFieldName

The script above has one substantial weakness. If, for some reason, you ever rename your Price fields, your script will simply break. Normally in FileMaker, of course, renaming things is no problem. FileMaker just fixes things up for you (because it references fields by an internal ID rather than by name). But it has no way to know how to rewrite your calculation to accommodate a new name.

Many people (myself included) would consider this an unacceptable tradeoff. First and foremost, our code should not break. Luckily, the good folks at FMI have anticipated this problem, and they’ve come up with a clever solution in the GetFieldName function. When you pass a field to this function (a real field reference, not the name of a field in quotes) it will give you back its name. Assuming your Price fields will always use the same naming convention, you can make your script safer like this:

Set Field by Name [ Left(GetFieldName(My Table::Price 1), Length(GetFieldName(My Table::Price 1) - 1) & #P("which"); #P(newPrice) ]

This version uses the Price 1 field as an example of the appropriate field name. It removes the 1 from the end of the field name, then inserts the appropriate number. Now things will keep working, even if you decide to rename the fields thusly:

  • Price Per Unit 1
  • Price Per Unit 2
  • Price Per Unit 3
  • Price Per Unit 4
  • Price Per Unit 5

Parameters

The GetFieldName function isn’t only useful when hacking up field names like the example above. You can use its results directly as well. For instance, you might write a script that performs some text manipulation you often need. This script can manipulate any field using Set Field by Name:

Set Field by Name [ Get(ScriptParameter); /** my complicated calculation **/ ]

If you pass the name of a field as a parameter, this script will set the field you specify. The complicated calculation can even use the GetField function to reference the field in question. A simple example might be:

Set Field by Name [ Get(ScriptParameter); Upper(GetField(Get(ScriptParameter))) ]

This script converts the contents of a field to upper case, and you can pass any editable field’s name as a parameter, making it totally generic. (Of course the script could be much more complex than this sample, incorporating multiple calls to Set Field by Name, conditionals, looping, and so forth.)

To keep your code safe, don’t call it like this:

Perform Script [ My Script; "My Table::Some Field" ]

The script parameter in that example is fragile because it will break if the field name (or table occurrence name) changes. Instead, call it like this:

Perform Script [ My Script ; GetFieldName(My Table::Some Field) ]

This version is impervious to name changes because GetFieldName will always return the right field name, even if you change it later. You still get all the flexibility though — you can still pass any field you want to the script to operate on it.

Calculation Fields

In the comments to our overview article on FileMaker 10, one of our readers, Michel Vincent, asked us about using indirection inside self-referencing calculated fields. This is even more mind bending, and I don’t have a good example (I hadn’t even thought of it before), but the core capability is there. For instance, suppose you have a calculation field called “Mind Bender” with this formula:

GetFieldName ( Self )

If you look at this field in Browse mode, it will contain its own name. If you rename the field, the content will update accordingly. You could combine this with various as-yet-unimagined calculations to produce powerful results. The reader envisioned a system of field naming conventions to help manage XML formatting of data, as an example.

>Note: In fact, I think naming conventions combined with these indirection features will prove to be a powerful combination. In our next article on indirection, you’ll see how we use our primary key naming conventions to streamline some common script code we use ever day.

Wrapping Up

Indirection — the power to determine which fields are modified when the script runs rather than when it is written — is a feature of many very advanced programming environments. In fact, in a more evolved form, it is what makes object oriented programming so flexible.

It has been possible for years to get some semblance of indirection out of FileMaker using complicated, slow, and fragile layout-based scripting techniques. But now, these features are fully supported, reliable, and fast. We are excited to see what our readers come up with.

11 Comments

  1. Michel Vincent

    Hello Geoff,
    The idea of automatic xml tags creation works fine.
    I already know about xml exportation with FileMaker but this method, IMHO, combined with loops in scripts, gives a lot of flexibility. What I like is the fact that, since the formula is based on the FieldName, it never needs to be edited (that’s a complex solution for a lazy person). I hope that it will be useful to others as it would have been to me in some projects.
    Here is an example :
    Define 2 text fields : FirstName and LastName
    (make sure that their name is the same as the xml tag name).
    Then define a first calculation field: FirstName_
    (it has the very same name as the target source field followed by an underscore)
    The calculation is as follows:

    IIf ( not IsEmpty ( GetField(Left(GetFieldName ( Self);Length(GetFieldName ( Self)) -1)) ) ;
                            "<" & RightWords(GetFieldName ( Self);1) & ">" 
                            & GetField(Left(GetFieldName ( Self);Length(GetFieldName ( Self)) -1)) 
                            & "</" & RightWords(GetFieldName ( Self);1) & ">" ; "" )
    

    The result must be text and (unfortunately?) the result must be stored if we want anything to show.
    We can now duplicate the FirstName_ field and rename it LastName_ and we don’t have to modify the formula.
    As a result, if the FirstName field contains Geoff, the FirstName_ field will show:

    <FirstName>Geoff</FirstName>

    2 important details :
    in the xml tags, we don’t want to display the TableName so it is important to extract only the last word in the GetFieldName. Also note the use of the underscore character as a suffix to the name of the calculation fields. RightWords will ignore this special character so we have to be careful not to use a regular character as this would necessitate an unnecessary longer formula to extract the correct text.
    Also, since the calculation fields have stored results, it is better to create all the fieds before populating the database. If you have to add or rename fields afterwards, you can force the updating with a Replace command on the source field (replace the source field by the sourcefield calculation).
    Sorry for the long explanation.

  2. Larry

    Forgive me if this was mentioned already, but another thing I’ve just noticed in playing around with this is that you can include more Set Field By Name steps than you need. FileMaker gives an error 102 (Field Is Missing), but essentially skips right over it without giving a warning dialog. I have a generic “New Related Record Script” which, depending on the context, might need a number of additional fields set. So I can pass as parameters:

    
     #( "field1" ; GetFieldName ( TABLE::Field ) ) & #( "field1Value" ; "My Value" ) &
     #( "field2" ; GetFieldName ( TABLE::Field ) ) & #( "field2Value" ; "My Value" ) &etc.
    

    to the script which, for good measure, could have a half dozen or more appropriately numbered Set Field By Name steps in it.

    And I'll definitely be making a shorthand GFN() for that function since I can see I'll be using it a lot.

  3. Stephen Dolenski

    one way to reduce having to open both “Specify” dialog boxes (one for the name and one for the result ) when developing the calculation put all the logic in the first (default double clickable) “Specify” calc dialog. Wrapping it with a Let () statement and setting a local script variable $result, then putting that in to the second Calc field

    [code]
    Let ([

    $field = GetFieldName ( sscode::field ) ; // or a complex calc to determine your target field.
    $result = " XYZ" // or complex calc to provide dynamic results depending on target field.

    ];

    $field

    )//Let
    [/code]

    Then you only need to manage your code from one place.

    Or you can put two variables in each dialog box $target / $result and set the logic elsewhere in your script using variables.

    Alternatively Abstract your logic even further by creating Parameter based Scripts that are reusable. A single Script that handles ALL your Set Field by Names that expects two parameters or global variables, a Target field And Result you pass these to your script it does its job, in case of using global variables have it null the values on completion.

  4. Lorne Walton

    The awesome power of this will start to sink in better once I get my hands on FM 10. I just want to point out what I think is a typo in this line:
    “Set Field by Name [ Get(ScriptParameter); Upper(GetField(Get(ScriptParameter))) ]”

    That should be “GetFieldName”, right?

    If I’m right, please feel free to erase this massage after you make the fix. Thanks for SFR!

  5. Will M. Baker

    Something interesting I’ve found when using this to set repeating fields: often, when we reference a specific repetition of a repeating field, we’ll do so with some extra white space for padding, like this:
    FIELD [ 2 ]

    FileMaker in general is forgiving, and doesn’t care about the extra spaces. It can evaluate FIELD [ 2 ] just as easily as FIELD[2].

    ‘Set Field By Name’ is less forgiving. The following statement properly executes, inserting the value “Test” into the second repetition of the field.
    Set Field By Name [“FIELD[2]“; “Test”]

    But this statement results in error 102:
    Set Field By Name [“FIELD [ 2 ]“; “Test”]

  6. Mark

    Thanks so much! SO much better then filemaker’s help file. I spent so long until I read your article to figure out you have to ‘build’ the field name.

    “Customers::” & Get ( ScriptParameter )

    Script trigger on exit ‘FullName’ field, passing Get ( ActiveFieldName ) results in: “Customers::FullName”

    works beautifully now!! Cheers to short/smart scripts!
    1 liner, I love it!!

  7. David Tinoco

    I am having trouble using a script parameter and this script step.

    The field is called g_inventoryItem
    and has 10 repetitions.

    I pass g_inventoryItem as a script parameter and then use:

    Set Variable $counter = 1;
    Loop
    SetFieldByName: Get(ScriptParameter) & “[" & $counter & "]” ; “”
    Set Variable $counter = $counter + 1;
    Exit Loop If $counter > 10
    End Loop

    But it says the field is missing

  8. Chuck Pelto

    Wouldn’t it have been nice if these people at FMI had made it so that a variable could have been used to identify the field?

    Oh…but no….that would have required too much thought.

  9. Kieren MacMillan

    Hi David,

    To use a variable, do this instead:

    SetFieldByName
    [
    GetFieldName ( Evaluate ( Get ( ScriptParameter ) & “[" & $counter & "]” ) ) ;
    “”
    ]

    Hope this helps!
    Kieren.

  10. David Graham

    Why, when $datefield= “table::date”, do all the following generate a ‘field is missing’ error:

    Set Field By Name [$datefield ; $date]
    Set Field By Name [GetFieldName ($datefield) ; $date]
    Set Field By Name [GetFieldName (Evaluate ( $datefield ) ) ; $date]

    But this works fine:

    Set Field [table::date;$date]

  11. David Graham

    I misunderstood Kieren. I thought he was responding to the previous comment bemoaning FM’s inability to use a variable as the GetFieldName argument, and was showing that variables could indeed be used. His example does show using a variable as part of the GetFieldName argument, but I can’t get a variable alone to work.

Tell Us What You Think

*
* (will not be published)