It's no secret that I'm not a big fan of repeating fields. I do everything that I can to avoid them. However, some developers do use them, and so I try to support repeating fields as much as possible.
I find myself using ExecuteSQL quite often, and in doing so I've found that working with repeating fields takes a little extra effort.
Here's what I've found...
To refer to a specific repetition, you specify the repetition by placing it behind the field name and in brackets. For example, suppose that you have a repeating field named "Repeater" and want to find all records where the third repetition isn't empty. You could use this query:
SELECT First_Name, Last_Name FROM Surveys WHERE Repeater IS NOT NULL
Similarly, you can choose to return specific repetition values like this:
SELECT Repeater, Repeater, Repeater FROM Surveys WHERE Last_Name = 'Smith'
By default, FileMaker assumes that you're referring to the first repetition of a field. In other words, if you refer to a repeating field in your SELECT statement, and you don't specify what repetition you are referring to, then FileMaker assumes that you mean repetition 1.
Similarly, when selecting all fields with using the "*" operator, only the first repetitions of any repeating fields will be returned. For example, the following query will only return the value of the first repetition of the "Repeater" field, regardless of whether there are values in the other repetitions:
SELECT * FROM Surveys WHERE Last_Name = 'Smith'
If you want to determine whether or not a field is setup as a repeating field, you can query FileMaker's "FileMaker_Fields" system table. For example:
SELECT FieldReps FROM FileMaker_Fields WHERE ( TableName = 'Surveys' ) AND ( FieldName = 'Repeater' )
The value of "FieldReps" is the maximum number of repetitions the field is configured to support. But be careful when using this value. The value that is returned will be treated as a text value by default, and unless you convert it to a number, you could run into problems. I've found this to be especially true when using the value to iterate through field repetitions, and especially so in cases where there are 10 or more repetitions.
In summary, working with repeating fields in ExecuteSQL can be tricky. But with a little extra effort, it can be done. I hope you'll find that these tips will make doing so a little easier.