This is an excerpt from the book Crystal Reports Encyclopedia. Click to read more chapter excerpts.

Creating a List of Values

Before deciding which fields to use in the list of values and its description, you should consider the database structure first. For the purpose of importing values, you need to figure out whether the field you want to display has a primary table or not (it may not exist). The primary table lists every possible value for a field and has a single field that is a unique identifier for each row (called the Primary Key). If you aren't familiar with the database then you should consult the database administrator for help.

If you have a basic familiarity with databases then you can probably identity the primary table yourself. One way to determine if a table is the primary table is that there is exactly one record for each value you want to display (i.e. there are no duplicate records). You can usually tell this if there are related fields that store its name, description, etc. For example, a Product table would list each Product Id (the primary key) as well as listing its name, price, and possibly size or weight.

A table that isn't the primary table usually lists a field as a secondary field within another table and the values are duplicated. For example, consider a Customer table which is the primary table for customer data. It lists exactly one record per customer. Within the Customer table are fields such as the Address and Country. Both the Address and Country fields are secondary fields within the table. They give additional information about the Customer record, but aren't the primary information. Consequently, the values in the secondary fields can be repeated multiple times within the table. For example, many customers are going to have a Country field with values such as USA, Canada, Australia, etc.

Depending upon the data that you are displaying, there isn't always a primary table in the database. For example, the data source for a a sales report that lets a user filter by Country probably doesn't have a Country table available. Instead, you have to use the Country field from another table. If you aren't working with the primary table then there can be a problem for you when you import the data. The table might not have a complete list of values. If the table doesn't have much data in it yet then it is very likely that it won't have an exhaustive list. If this is the case, after you import the data from the table you will have to add the missing values manually. That is why it is best to first determine if the database has a primary table for your data because this will make sure that you load every possible value at one time.

Note
Don't worry if the database doesn't have a primary table for your data and instead you have to select a table that repeats a value multiple times. Before importing data from the table, Crystal Reports examines the table and generates a unique list of values. This ensures that each value is only imported once.

Just telling Crystal Reports which field to populate the list of values isn't enough to get the values there. There are two ways of importing the values. You can either add them one at a time or add the entire list at once. Of course, adding the entire list is most efficient way if there are a lot of values. But if you only need a subset of all the possible values then you can pick and choose which ones to import on an individual basis.

To read all my books online, click here for the Crystal Reports ebooks.