Tim Dietrich takes on ExecuteSQL and repeating fields, something most developers don’t use anymore. However, there are plenty of legacy databases out there using repeating fields, especially solutions written pre FileMaker 7.
For those times when a repeating field is needed, Tim has the answers:
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
One note: if you inherit a database with repeating fields, talk you client into updating the database structure to related tables. Reporting, data export and import, and support will all be much easier.