Creating folders that support e-Marketing Spots

In this lesson, you are creating folders to group your marketing e-Marketing Spots. You must run a SQL insert statement with the appropriate values to create sample folders in your WebSphere Commerce Developer database. You are going to use these folders in the following lessons to store and view e-Marketing Spots in the Marketing tool as you complete this tutorial.

Attention: This tutorial is based on the folder support that is included in Feature Pack 5 and is not recommended if you are on a newer feature pack level. Beginning with Feature Pack 6, support is provided by default for grouping e-Marketing Spots, activities, customer segments, marketing content, promotions, and attribute dictionary attributes. This support includes the capability of creating, changing, and deleting folders within the Management Center Catalogs, Marketing, and Promotions tools. You are not able to group folders created by following this tutorial with folders that are created by using Management Center. If you create Management Center folders by following the steps included in this tutorial, delete those folders before you create folders by using Management Center. For more information about folders, see Folders.

Feature Pack 7 or later Support is provided by default for grouping content pages and layouts in the Commerce Composer tool. This support includes creating, changing, and deleting folders with the Commerce Composer tool.

About this task

For more information about the requirements for creating a folder in your database, see Creating promotion folders.

Procedure

Determine the unique primary key for your folder. Each new folder requires a unique identifying key. You can retrieve this key from the WebSphere Commerce KEYS database table.
1. Open a connection to your database to run SQL statements against your database.
2. In the input box, run the following SQL query:
```
SELECT COUNTER FROM KEYS WHERE tablename='folder' AND columnname='folder_id';
```
  Make note of the value. This value is used for the FOLDER_ID of your folder.
3. Update the KEYS table to set the counter to the next value to be available for use.
4. Run the following SQL statement:
```
UPDATE KEYS SET COUNTER=COUNTER + 1 WHERE tablename='folder' AND columnname='folder_id';
```
5. Repeat this step to retrieve the FOLDER_ID primary key values for each folder you are going to create. In this tutorial, you are creating 4 folders, and require 4 FOLDER_ID values. Make note of each of these values.
Run the following SQL query to determine the unique Id of your extended site store to associate with the e-Marketing Spot folder:
```
SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER='YourStoreName';
```
Where YourStoreName is the name of the extended site store that you are adding a sample folder into. Make note of your extended site store STOREENT_ID value. This value is used for the STOREENT_ID of your promotion folder.

Create a folder in your database by running the following SQL statement:

INSERT INTO FOLDER (FOLDER_ID, STOREENT_ID, MEMBER_ID, PARENTFOLDER_ID, IDENTIFIER, DESCRIPTION, TYPE) 
VALUES (YourFolderId, YourStoreId, NULL, YourParentFolderId, 
'Folder Name', 'Folder Description', 'Folder Type');

Where YourFolderId is the value your retrieved in step 1. If you are creating multiple folders, retrieve a unique primary key value as demonstrated in step 1 for each folder you want to create. If you are creating a top-level folder, the value of YourParentFolderId is NULL. If you are creating a sub folder, the value of YourParentFolderId is the unique ID value of the parent folder. The value for MEMBER_ID is NULL as this field is reserved for future usage. The value for TYPE is used to identify the object that the folder is associated with, for instance, e-Marketing Spots. If folders exist for multiple object types and have the same folder name for the different object types, use the value of the TYPE column to help identify a folder.

For example, the following SQL insert statements creates sample top-level folders.

INSERT INTO FOLDER (FOLDER_ID, STOREENT_ID, MEMBER_ID, PARENTFOLDER_ID, IDENTIFIER, DESCRIPTION, TYPE) 
VALUES (YourFolderId, YourStoreId, NULL, NULL ,'EMarketingSpotFolder',
'The folders created for eMarketing spots', 'EMarketingSpotFolder');

INSERT INTO FOLDER (FOLDER_ID, STOREENT_ID, MEMBER_ID, PARENTFOLDER_ID, IDENTIFIER, DESCRIPTION, TYPE) 
VALUES (YourFolderId, YourStoreId, NULL, NULL, 'My Favorite Spots',
'MyFavoriteSpots', 'EMarketingSpotFolder');

The following example SQL insert statements create sample sub folders under the top-level folders that are created in the previous SQL statements:

INSERT INTO FOLDER (FOLDER_ID, STOREENT_ID, MEMBER_ID, PARENTFOLDER_ID, IDENTIFIER, DESCRIPTION, TYPE) 
VALUES (YourFolderId, YourStoreId, NULL, YourParentFolderId,
'JanuarySpots',' eMarketing spots for January', 'EMarketingSpotFolder');

INSERT INTO FOLDER (FOLDER_ID, STOREENT_ID, MEMBER_ID, PARENTFOLDER_ID, IDENTIFIER, DESCRIPTION, TYPE) 
VALUES (YourFolderId, YourStoreId, NULL, YourParentFolderId,
'MarchSpots',' eMarketing spots for March', 'EMarketingSpotFolder');

Where YourParentFolderId is the YourFolderId value of the folder you want to set as the parent folder for the folders you are creating. For this tutorial, use the value for the FOLDER_ID of the top-level folder named EMarketingSpotFolder that is created with your first SQL insert statement. Replace YourFolderId for each of your folders with one of the values you retrieved in step 1. Do not reuse any of these values. For example:

INSERT INTO FOLDER (FOLDER_ID, STOREENT_ID, MEMBER_ID, PARENTFOLDER_ID, IDENTIFIER, DESCRIPTION, TYPE) 
VALUES (1, 10001, NULL, NULL ,'EMarketingSpotFolder',
'The folders created for eMarketing spots', 'EMarketingSpotFolder');

INSERT INTO FOLDER (FOLDER_ID, STOREENT_ID, MEMBER_ID, PARENTFOLDER_ID, IDENTIFIER, DESCRIPTION, TYPE) 
VALUES (101, 10001, NULL, NULL, 'My Favorite Spots',
'MyFavoriteSpots', 'EMarketingSpotFolder');

INSERT INTO FOLDER (FOLDER_ID, STOREENT_ID, MEMBER_ID, PARENTFOLDER_ID, IDENTIFIER, DESCRIPTION, TYPE) 
VALUES (102, 10001, NULL, 1,
'JanuarySpots',' eMarketing spots for January', 'EMarketingSpotFolder');

INSERT INTO FOLDER (FOLDER_ID, STOREENT_ID, MEMBER_ID, PARENTFOLDER_ID, IDENTIFIER, DESCRIPTION, TYPE) 
VALUES (3, 10001, NULL, 1,
'MarchSpots',' eMarketing spots for March', 'EMarketingSpotFolder');

Repeat the previous step to make as many folders as needed.