The 'Country' list on my v9 MOTO form is broken, how can I fix it?

[KB]

If the MOTO country list on the 'New Order' tab shows as a long text list of countries rather than a drop-down box then the ContentLists table is broken.

The table is broken in 2 ways, the key is missing and so new lists are added as nID –1 plus the Design Object ID is incorrect.

The above is solved as follows: -

Create design reference tables
A reference table is required to determine the correct design objects for each content list of the site. The design object Ids will vary from one site to another.

* Close your ecommerce software
* Browse to your site folder and locate the 'ActinicCatalog.mdb' file
and take a backup
* Then browse to 'C:\Program Files\Actinic v9\Original\'
* Open 'ActinicCatalog.mdb' from this folder with MS Access.
* First we need to create a new table called 'ContentListsRef' by doing
the following:-

1. Go to the 'View' menu and select ' Database Objects | Queries'
2. Go to the 'Insert' menu and select 'Query'
3. Select 'Design View' from the list of options and click 'OK'
4. Click 'Close' on the 'Show Table' window if it appears
5. Go to the 'View' menu and select 'SQL View'
6. Replace the contents of this window with the following SQL:

SELECT ContentLists.nID, ContentLists.nDesignObjectID, 
DesignObjects.sName
INTO ContentListsRef
FROM ContentLists
INNER JOIN DesignObjects
ON ContentLists.nDesignObjectID = DesignObjects.nID;

7. Go to the 'Query' menu and select 'Run'
8. Click 'Yes' on the message that appears


* Now open the site ActinicCatalog.mdb with Access (so you have two
databases open at the same time)
* Copy the 'ContentListsRef' table from the 'Original' database (Structure
and data) to the site database using the same name
* Copy the 'ContentLists' table from the 'Original' database (Structure and
data) to the site database giving it a new name of ContentListsDefault
* Close the 'Original' database. The rest of the work is only on the site database.

Protect custom lists
It is possible that you have custom lists which use the same Ids as system defined lists. To make the fix easier we shall renumber the custom lists to be outside the range of the system lists.

* Create a new query to identify your custom lists by following steps 1 to 8 above but using the following SQL instead

SELECT ContentLists.nID, ContentLists.nDesignObjectID, DesignObjects.sName, DesignObjects.sSystemVersion
FROM ContentLists
INNER JOIN DesignObjects
ON ContentLists.nDesignObjectID = DesignObjects.nID
WHERE (((DesignObjects.sSystemVersion) Is Null))
ORDER BY ContentLists.nID;

* Make a note of the 'nID' and 'nDesignObjectID' of each list then open the 'ContentLists' table, locate the records with the noted IDs and set the nID to 200 for the first one, 201 for the second etc.

Remove the bad list

* Create a new query as per steps 1 to 8 above but use this SQL instead:-

DELETE ContentLists.nID
FROM ContentLists
WHERE (((ContentLists.nID)=-1));

Repair the keys on the table ContentLists

* Open the 'ContentLists' table by double-clicking on it
* Go to the 'View' menu
* Select 'Design View'
* Then go to the 'View' menu and select 'Indexes'
* If the table of indexes is not empty then delete each index
* Close the index dialog
* Still in the 'Design View', right click the 'nID' field and select 'Primary Key'
* Go to the 'File' menu and select 'Close’, you will need to confirm the
design change

Restore the missing system lists

* Create a new query as per steps 1 to 8 above but use this SQL instead:-

INSERT INTO ContentLists ( nID, nLastRowColumnCount, bUseDifferentHtml, nLayoutChoice, sStartOfList, sEndOfList, sEmptyCell, sEmptyItem, sBeforeSingleItem, sAfterSingleItem, sFirstRowFirstColBefore, sFirstRowFirstColAfter, sFirstRowMidColBefore, sFirstRowMidColAfter, sFirstRowLastColBefore, sFirstRowLastColAfter, sMidRowFirstColBefore, sMidRowFirstColAfter, sMidRowMidColBefore, sMidRowMidColAfter, sMidRowLastColBefore, sMidRowLastColAfter, sLastRowFirstColBefore, sLastRowFirstColAfter, sLastRowMidColBefore, sLastRowMidColAfter, sLastRowLastColBefore, sLastRowLastColAfter, sOddRowsOddColBefore, sOddRowsOddColAfter, sOddRowsEvenColBefore, sOddRowsEvenColAfter, sEvenRowsOddColBefore, sEvenRowsOddColAfter, sEvenRowsEvenColBefore, sEvenRowsEvenColAfter, sFirstRowBefore, sFirstRowAfter, sMiddleRowBefore, sMiddleRowAfter, sLastRowBefore, sLastRowAfter, nMidRowColumnCount, nDesignObjectID, sSystemVersion, nFirstRowColumnCount )
SELECT ContentListsDefault.nID, ContentListsDefault.nLastRowColumnCount, ContentListsDefault.bUseDifferentHtml, ContentListsDefault.nLayoutChoice, ContentListsDefault.sStartOfList, ContentListsDefault.sEndOfList, ContentListsDefault.sEmptyCell, ContentListsDefault.sEmptyItem, ContentListsDefault.sBeforeSingleItem, ContentListsDefault.sAfterSingleItem, ContentListsDefault.sFirstRowFirstColBefore, ContentListsDefault.sFirstRowFirstColAfter, ContentListsDefault.sFirstRowMidColBefore, ContentListsDefault.sFirstRowMidColAfter, ContentListsDefault.sFirstRowLastColBefore, ContentListsDefault.sFirstRowLastColAfter, ContentListsDefault.sMidRowFirstColBefore, ContentListsDefault.sMidRowFirstColAfter, ContentListsDefault.sMidRowMidColBefore, ContentListsDefault.sMidRowMidColAfter, ContentListsDefault.sMidRowLastColBefore, ContentListsDefault.sMidRowLastColAfter, ContentListsDefault.sLastRowFirstColBefore, ContentListsDefault.sLastRowFirstColAfter, ContentListsDefault.sLastRowMidColBefore, ContentListsDefault.sLastRowMidColAfter, ContentListsDefault.sLastRowLastColBefore, ContentListsDefault.sLastRowLastColAfter, ContentListsDefault.sOddRowsOddColBefore, ContentListsDefault.sOddRowsOddColAfter, ContentListsDefault.sOddRowsEvenColBefore, ContentListsDefault.sOddRowsEvenColAfter, ContentListsDefault.sEvenRowsOddColBefore, ContentListsDefault.sEvenRowsOddColAfter, ContentListsDefault.sEvenRowsEvenColBefore, ContentListsDefault.sEvenRowsEvenColAfter, ContentListsDefault.sFirstRowBefore, ContentListsDefault.sFirstRowAfter, ContentListsDefault.sMiddleRowBefore, ContentListsDefault.sMiddleRowAfter, ContentListsDefault.sLastRowBefore, ContentListsDefault.sLastRowAfter, ContentListsDefault.nMidRowColumnCount, DesignObjects.nID, ContentListsDefault.sSystemVersion, ContentListsDefault.nFirstRowColumnCount
FROM ((ContentListsRef
LEFT JOIN ContentLists
ON ContentListsRef.nID = ContentLists.nID)
INNER JOIN ContentListsDefault
ON ContentListsRef.nID = ContentListsDefault.nID)
INNER JOIN DesignObjects
ON ContentListsRef.sName = DesignObjects.sName
WHERE (((ContentLists.nID) Is Null));

* Exit Access
* Restart the software.

If you do not have Access or do not feel confident enough to apply the above instructions to your database then please contact support who will do it for you, however, note that you will not be able to make any changes to your store (maintenance or order processing) while SellerDeck have the database.