I forgot to add a Pre-defined List before adding field data

No problem - just go to Admin mode and in the Admin dialog's Custom Fields palette, select your field and click the Edit button. Tick the PDL tick-box and the Edit List button to set the list and indicate whether only list values are to be used. You can also rename the field if you like from the same field Edit dialog.

If you're cautious and want to start over with a new field but with the same name, here are the steps - if you've got a lot riding on the database, best take a backup of the whole Catalogue first:

• Select all existing records - or certainly those with data that will be affected.
• Export the filename, and all the fields you wish to create pre-defined lists for, to text file.
• Take a copy of the exported data and open in Excel, Access, or similar.
• Parse the data to give a list of unique values - create a list for each set of field data exported. Don't know how? See below.
• Now delete the custom field(s) you're going to make the list(s) for (remember - you've already saved the data).
• Re-create the old field(s) again but this time select the Pre-Defined List (PDL) option.
• Import the PDLs you've made from the previously exported data.
• The Catalogue's records will now have the data as before, in the same named field but with a new feature. Pop the Window -> Master Palettes menu. All the fields with pre-defined lists now have a master palette you can use to find or assign data as with an MKL.

Parse? If that section of the procedure above leaves you a little confused, here's a VBA module you can add to an Excel file that you'll import your data to. Select a cell in the column of data from which you want unique values and watch it make a TXT file list of unique entries from your data. Here's the file - right-click to download - called MakeList.bas.

You'll need to open the Excel Visual Basic Editor (Alt+ F11) to add the module. Once there, display the code. Set the variables 'strFirstRow' and 'strLastRow' for the row number of the first and last rows of data (default values are "2" and "3"). I use the code with data where row #1 is the name of the field in Portfolio and the SaveList function uses this info as the name of the file it creates - helps when you come back to adding several pre-defined lists.

If you have data several field's data in your export you can save time by simply getting Excel to iterate through each column and spit out a text file.

The code should work fine in Excel 97/2k/XP on Windows. Mac users may need to check the path handling code and set a Mac text editor.

Question: I forgot to add a Pre-defined List before adding field data [FAQ00166.htm]
Last Update:- 31 May 2006

This FAQ is created and maintained using

Friday - The Automatic FAQ Maker