AnsweredAssumed Answered

CREATE/ALTER TABLE fail if repeating fields

Question asked by Fred(CH) on Apr 6, 2014
Latest reply on Apr 9, 2014 by Fred(CH)

Summary

CREATE/ALTER TABLE fail if repeating fields

Version

13.0v2

Operating system version

Mac OS X 10.8.5

Description of the issue

Although it is fully documented on the page #20 of the FileMaker 13 SQL guide, using CREATE TABLE or ALTER TABLE statements, it seems to be just impossible to automate the creation of a repeating field : a syntax error is displayed and the table is not altered nor created. It works correctly with a regular field.

However, the page #20 of the FileMaker 13 SQL guide, regarding CREATE TABLE statement states :

Within the statement, you specify the name and data type of each column.
[…]
field_name is the name of the field. No field in the same table may have the same name. You specify A FIELD REPETITION BY USING A NUMBER IN SQUARE BRACKETS. For example: lastDates[4].
[…]

Steps to reproduce the problem

STEP 1
Connect to a FileMaker Database within ODBC

STEP 2
Create and execute a script with an Execute SQL step that perform this :
CREATE TABLE "_Test" ( lastDates VARCHAR )
If it worked, go to STEP 3

STEP 3
Duplicate your script and modify the SQL statement like this :
CREATE TABLE "_Test2" ( lastDates[4] VARCHAR )

Expected result

Both STEP 2 and STEP 3 must works. In particular you should get a brand new table named "_test2" with a repeating field named lastDates and 4 repetitions on it.

Actual result

STEP 2 works. However, STEP 3 fails and you get an error message.

Exact text of any error message(s) that appear

[FileMaker][FileMaker] FQL0001/(1:34): There is an error in the syntax of the query.

Configuration information

--

Workaround

NONE FOUND. I tried theses syntax unsuccessfully :

CREATE TABLE "_Test2" ( "lastDates[4]" VARCHAR )
CREATE TABLE "_Test2" ( "lastDates"[4] VARCHAR )
CREATE TABLE "_Test2" ( "lastDates [4]" VARCHAR )
CREATE TABLE "_Test2" ( "lastDates" [4] VARCHAR )
CREATE TABLE "_Test2" ( "lastDates{4}" VARCHAR )
CREATE TABLE "_Test2" ( "lastDates"{4} VARCHAR )
CREATE TABLE "_Test2" ( "lastDates {4}" VARCHAR )
CREATE TABLE "_Test2" ( "lastDates" {4} VARCHAR )

Outcomes