GetNthRecord

Troubleshooting

Text Formatting in Nontext Calculations

I want some of my dates to come out in red. I created some calculations that apply text formatting to certain dates, but they just don't work.

For a calculation containing text formatting functions to work correctly, the calculation must have an output type of Text or Number. Calculations defined to output a data type of Date, Time, or Timestamp will not show the effects of text formatting calculations.

Naming Variables in Let Functions

Can I use spaces in the names of variables used in Let functions? Are the variable names case-sensitive? What happens if I name a Let variable the same thing as an existing field name, variable name, or function name?

First off, yes, you can use spaces in the names of variables used in Let functions. Variable names can't begin with numbers, nor can they contain certain reserved characters (such as ; , / + - * = ( ) [ ] < > & and "). You can, however, use characters such as $ and % in variable names.

Some complexity arises when we look at the possible use of script variables (variables beginning with $ or $$) within Let statements. We explore this complex topic in the following chapter. For now, suffice it to say that because various parts of a Let statement can work with script variables, you should avoid using $ or $$ in naming any of your Let variables.

Variable names within Let statements are not case-sensitive. You can use a particular name several times within a function, and names can also be reused in separate functions.

There are no restrictions against naming variables with the same names used for fields and functions. Be aware that any subsequent use of the name within the function refers to the local variable, not the field or function. With most functions, you don't need to worry about this, but names of functions that don't take parameters, such as Random, WindowNames, and Pi, should not be used for variables within a Let function. For instance, the formula Let (Pi = "Hello"; Pi) would return the string Hello, not the trigonometric constant pi that you might expect. As a rule, it's wise to avoid any overlap of names with reserved FileMaker names, or names of objects elsewhere in the system. Even if the logic works, it may be confusing and hard to read.

Working with Arrays

I use arrays that have pipe characters as delimiters. Can I use the "values" functions to extract elements from these arrays?

The five "values" functions (ValueCount, LeftValues, MiddleValues, RightValues, GetValue) operate only on return-delimited lists of data. If you have lists that are delimited by other characters, such as pipes or tabs, you'd first need to do a substitution to change your delimiter into a return. For example, if myArray is a pipe-delimited array, you could count the number of values in it with the following formula:

Let (tempArray = Substitute (myArray; "|"; "¶"); ValueCount (tempArray))

Of course, one of the reasons you might not have used returns as your delimiter in the first place is that your data elements may possibly contain return characters. If that's the case, you can't swap in returns as your delimiters and expect the structure of the array to remain unchanged. Before turning pipe characters into carriage returns, you'd want to turn any existing carriage returns into something elsesomething that's guaranteed not to be found in an element and that's easy to turn back into a return character if necessary. You might, for instance, use the Substitute function to turn returns into the string ***RETURN***.