Choosing the Proper Fields
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). A 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 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 be 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 once.
You don’t have to worry about importing duplicate data into the list. Before importing data from a 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 the 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 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 import every value into the list, click on the Actions button and select Append All Database Values. This scans through the entire table and adds each value to the list. If you selected a field for the Description column, then the descriptions get imported as well.
If you don’t need to import every single value, you can import them individually. This is more work, but it’s a practical solution if you only need a few of the values. To select values individually, click on the row that says Click Here to Add Item. This displays a dropdown arrow on the right side of the column. Click on the arrow to see a list of all the values in the table. Select the one you want and it gets added to the list. Repeat this procedure for every item to be added to the list.
If you need to import a lot of the values from the table, but not every one, a better idea is to import the entire list and then delete the ones you don’t need. Depending upon how big the list is, this can be much faster than selecting the values on an individual basis.
A word of caution when using a database to add individual items to the list of values: when adding individual items, the Description column doesn’t get filled in for you. Even if you have a field specified for the Description column, nothing gets inserted. Only the Values column gets populated. You have to type entries in the Description column manually. Hopefully, this problem will be corrected in a future maintenance release.
Building a static list using a database field doesn’t create a live connection to the database. If the values in the database field change, then this will not be reflected in the list of values. You will have to go back and add the entries yourself. This is where dynamic prompts are beneficial and this is covered later in the chapter.
The final method of populating the list of values is by importing them from a text file. If you have a text file that lists each value and its description, this can save you a lot of typing. This can be useful if you are importing values from a third-party application and it has the option to export data. It can also be useful to manually create a list of values in a report and export that list to a text file. Then you can reuse it in other reports by importing it. The format of the text file is that the Value is listed first and the Description should be separated with the TAB key. Each Value/Description pair should be on its own line.
Editing the List of Values
After you populate the values and descriptions that the user chooses from, you need to know how to revise the list and keep it updated. After importing or entering the values, you might notice that they are listed in no particular order. The first thing you might want to do is sort the list in either ascending or descending order. There are two arrow buttons shown directly to the left of the Actions button. Clicking on the first arrow sorts the values in descending order and clicking on the second arrow sorts in ascending order. If you need to delete an item, click the delete button located directly to the right of the Insert button (it looks like an X).
If you make a mistake during the process of adding values, you can clear the entire list by clicking on the Actions button and selecting Clear.
