The Places of meeting & discussion custom field group is the next to be converted to a one-to-many fieldI'vee scripted this entire conversion, based on what was done with the previous one. These are the preliminary details:

• There are 11 fields, 10 boolean and one longText. They're numbered 67 through 77, and the longtext is the last one.
• We need a meetPlaces table, with mp_id and mp_desc.
• We need a docs_to_meetPlaces table, with dtm_dtm_id, dtm_doc_id_fk, and dtm_meetPlace_id_fk, the last two foreign keys into the table above and the documents table.

This script was used to accomplish the change:

/* First, we drop the two new tables, in case they've been converted before. */

DROP TABLE IF EXISTS `meetPlaces`;
DROP TABLE IF EXISTS `docs_to_meetPlaces`;

/* Next, we recreate them: the meetPlaces first, because the 
   second table has a key into it. */
   
CREATE TABLE IF NOT EXISTS `meetPlaces` (
  `mp_id` int(11) NOT NULL auto_increment,
  `mp_desc` varchar(128) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`mp_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

   
CREATE TABLE IF NOT EXISTS `docs_to_meetPlaces` (
  `dtm_dtm_id` int(11) NOT NULL auto_increment,
  `dtm_doc_id_fk` int(11) NOT NULL,
  `dtm_meetPlace_id_fk` int(11) NOT NULL,
  PRIMARY KEY  (`dtm_dtm_id`),
  KEY `dtm_meetPlace_id_fk_idx` (`dtm_meetPlace_id_fk`),
  KEY `dtm_doc_id_fk_idx` (`dtm_doc_id_fk`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

/* Now we create the foreign key constraints for the second table. */

ALTER TABLE `docs_to_meetPlaces`
  ADD CONSTRAINT `docs_to_meetPlaces_ibfk_1` FOREIGN KEY (`dtm_doc_id_fk`) REFERENCES `documents` (`doc_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `docs_to_meetPlaces_ibfk_2` FOREIGN KEY (`dtm_meetPlace_id_fk`) REFERENCES `meetPlaces` (`mp_id`) ON DELETE CASCADE ON UPDATE CASCADE;
  
/* Now we create the new fields from the old. */
INSERT IGNORE INTO `meetPlaces` (`mp_desc`)  ( SELECT `cf_name` FROM `customFields` WHERE (`cf_group_1` LIKE "Places of meeting%") AND (`cf_to_fieldTypes_id` = "101") ORDER BY `cf_name`);

/* Now we go through copying the data over from the old custom fields. */

/* This is the mapping data:
70 1 	After church / in front of church / on place publi...
71 2 	During civic assembly, reading of laws etc.
67 3 	Foire/marché; chef-lieu, market town
69 4 	In church
76 5 	Other
73 6 	Place of work (fields, artisan's shop)
72 7 	Political assembly (mun. council, electoral, pop. ...
75 8 	Private home or space
74 9 	Roads, streets
68 10 	Taverne/cabaret/auberge

77 (string field).
*/

INSERT INTO `docs_to_meetPlaces` (`dtm_meetPlace_id_fk`, `dtm_doc_id_fk`) SELECT "1", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "70";

INSERT INTO `docs_to_meetPlaces` (`dtm_meetPlace_id_fk`, `dtm_doc_id_fk`) SELECT "2", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "71";

INSERT INTO `docs_to_meetPlaces` (`dtm_meetPlace_id_fk`, `dtm_doc_id_fk`) SELECT "3", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "67";

INSERT INTO `docs_to_meetPlaces` (`dtm_meetPlace_id_fk`, `dtm_doc_id_fk`) SELECT "4", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "69";

INSERT INTO `docs_to_meetPlaces` (`dtm_meetPlace_id_fk`, `dtm_doc_id_fk`) SELECT "5", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "76";

INSERT INTO `docs_to_meetPlaces` (`dtm_meetPlace_id_fk`, `dtm_doc_id_fk`) SELECT "6", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "73";

INSERT INTO `docs_to_meetPlaces` (`dtm_meetPlace_id_fk`, `dtm_doc_id_fk`) SELECT "7", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "72";

INSERT INTO `docs_to_meetPlaces` (`dtm_meetPlace_id_fk`, `dtm_doc_id_fk`) SELECT "8", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "75";

INSERT INTO `docs_to_meetPlaces` (`dtm_meetPlace_id_fk`, `dtm_doc_id_fk`) SELECT "9", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "74";

INSERT INTO `docs_to_meetPlaces` (`dtm_meetPlace_id_fk`, `dtm_doc_id_fk`) SELECT "10", `cfd_to_doc_id` FROM `customFieldData` WHERE `cfd_to_cf_id` = "68";

/* Now the Z - Description/comments field (# 81). This data has to be added to the end of the notes field. */

UPDATE `documents` SET `doc_notes` = concat(`doc_notes`, '\r\r', (SELECT `cfd_text` FROM `customFieldData` WHERE `customFieldData`.`cfd_to_cf_id` = "77" AND `customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id`)) WHERE EXISTS (SELECT * FROM `customFieldData` WHERE `customFieldData`.`cfd_to_cf_id` = "77" AND `customFieldData`.`cfd_to_doc_id` = `documents`.`doc_id` AND LENGTH(`customFieldData`.`cfd_text`) > 0);

/* Then, we delete the original custom field data. WARNING: IRREVOCABLE! */

DELETE FROM `customFieldData` WHERE `cfd_to_cf_id` IN (67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77);

/* Finally, we delete the original custom field definitions. WARNING: IRREVOCABLE! */

DELETE FROM `customFields` WHERE `cf_id` IN (67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77);