Log In    |    Knowledge Base    |    4D Home
Tech Tip: Subtable support in 4D v11 SQL
PRODUCT: 4D Developer | VERSION: 11 | PLATFORM: Mac & Win
Published On: June 19, 2008

Subtables have limited support n 4D v11 SQL.

  • New subtables cannot be created.
  • Existing subtables will be converted, but only at the first level.
  • Subtables beyond the first level are removed during conversion. 4D will display a dialog with information about the lost data during the conversion process. Here is an example:

When converting a database, all fields of type Subtable are converted into standard, related tables and linked to the one table by a special relation. A field is added to the new related table, which is used to represent the foreign key field. This implies the following:

  • All commands related to subfields will still work in a converted database. However, if the special relation is deleted any subtable-related commands will no longer work. It is not possible to recreate the special subtable relation. Once it is deleted, all code that previously referred to that subtable must be changed to instead work with the related table.
  • Since the subtables are converted into “normal” tables this means the total number of tables in the database will be increased. These tables will be created after all “normal” tables have been converted. I.e. if the database contained 10 tables, the first converted subtable will be table 11.
  • In previous versions of 4D, loading a record would also load all subrecords. With 4D v11 SQL, subrecords are not loaded (since they are now, really, related records in another table). This is why commands such as DUPLICATE RECORD and SEND RECORD no longer handle subrecords.
  • When using SEND RECORD, the 4D v11 SQL format can only be read by 4D v11 SQL databases. Older 4D versions cannot read this format.
    Sent records from prior versions can still be read by 4D v11 SQL as long as the table structure where the record came from does not contain any subtables.

For more information about converting databases with subtables to 4D v11 SQL, see Technical Note 07-42, Upgrading Subtables to 4D v11 SQL, which can be obtained from here:

Commented by Leslie Riley on February 20, 2010 at 6:52 AM
I also noted that when a record of a table that has a converted subtable is saved and the trigger gets fired, a TRANSACTION is automatically started.