4 Replies Latest reply on Apr 9, 2014 2:15 AM by Fred(CH)

    CREATE/ALTER TABLE fail if repeating fields

    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 )