Walkthrough: Creating Tables with Visual FoxPro

  • Article

With Visual FoxPro, you can create and manage, individually and relationally, tables of data from many sources. You can use the Visual FoxPro language, or you can use the many tools to access or manipulate all kinds of data quickly, whether it is on a local disk, a network, or the Internet.

Contents

Introduction

What is Data?

Importing Data into Tables

Arranging Data in Tables

Accessing Data in Tables

Introduction

Microsoft Visual FoxPro is an application, a language, and a set of application development tools. Visual FoxPro tools are written in the Visual FoxPro language and are themselves based on tables.

In Visual FoxPro, you can use wizards, builders, or commands to create or to manipulate tables of data quickly. You can use the Import and Table wizards to create tables, the Table designer to manage tables, and the Visual FoxPro Browse command to view tables.

For simple access to data, use Visual FoxPro directly from commands, menus, and the Data Session window. For more complex data access and manipulation, you can create forms, reports, or views to enter and display tables.

This topic describes how to access existing data with the Import wizard and how to create the structure for new data using the Table wizard and the Table designer from Visual FoxPro menus.

What is Data?

Visual FoxPro works with and manipulates data in tables. The data can be composed of any of the following types of information alone or in combinations: Character, Currency, Date, DateTime, Logical, Numeric, Memo, General, Float, and Double. Visual FoxPro also accesses objects (such as Word and Excel documents), graphics, or sound files, as data.

You can use data that resides in many different kinds of tables, including tab or comma-delimited text files or tables produced in other applications, such as Microsoft Excel or Microsoft SQL Server.

For more information on valid data types, see Data and Field Types.

Importing Data into Tables

To get data that is already in tabular format, open Visual FoxPro, and import the file using the Import wizard. If you want to create the data in Visual FoxPro, use the Table wizard.

The Import Wizard

The Import wizard gives you the option of importing text files in which data is delimited by commas, spaces, tabs, or other separators.

When you use the Import wizard to copy the data into a new Visual FoxPro table, you must know how the text is delimited. In addition, you must make sure the same character delimits all the fields or your data does not import correctly.

To use the Import wizard

  1. On the File menu, click Import.
  2. In the Import dialog box, click the Import Wizard button.

You can use this wizard for other data formats, but it is especially great for text files. For example, if your data is in a file created in Microsoft Excel, you can select Excel from the list provided in the Import wizard. If you are more familiar with another data-generating application, it might be easier to output a delimited text file from that application for importing into Visual FoxPro.

The Table Wizard

When you use the Table wizard to create a new Visual FoxPro table, you must know the type and maximum size of data that you plan to place in each field. In addition, you should consider how the data will be entered and used, so you can decide whether to allow null values.

The Table wizard provides many templates that you can use or customize:

  • **Business templates   **Twenty-six templates for data you might use in commercial environments and transactions.
  • **Personal templates   **Twenty templates for items or activities related to your hobbies, your household, or personal possessions.

To use the Table wizard

  1. On the File menu, click New.

  2. Click Table, and then click the Wizard button.

    **Note   **The New file option also makes it possible for you to create a table, but you must do more of the work unprompted.

After you select a template, follow the remainder of the wizard steps to select fields, specify whether and how the table is indexed, and specify whether the table is a free table or contained in a database. When you complete the wizard, Visual FoxPro creates the empty table structure you have specified. You then can save the table structure and go on to something else, view the table, or modify the new table in the Table designer.

The Table Designer

If you want to create a table without a wizard, use the Table designer. In the Table designer, you can enter the name, data type, and size of each field. In addition to these characteristics, you also have opportunities to specify decimal value width of numeric fields, whether there is an index, and whether NULL values are allowed.

**Note   **Don't confuse NULL values with blank or empty values. The NULL setting has nothing to do with whether blank or empty values can be stored in a field.

If your table is in a database, you can specify input and display formats, field validation rules, and value tip messages, as well as setup your table to behave in specific and powerful ways whenever you use fields from the table on a form.

To access the Table designer

  1. On the File menu, click New.
  2. In the New dialog box, select the Table file type, and click the New File button.

Arranging Data in Tables

Visual FoxPro tables can be standalone or associated with a database. A database acts as a container for one or more tables, views, connections, and stored procedures. Tables that are not contained in a database are called free tables.

When you open a database-contained table, the associated database is also opened. When a free table is opened, only that table is opened.

To open a table

  1. On the File menu, click Open
  2. In the Files of type drop-down list, select Table (*.dbf), and then select a table from the File name drop-down list.
  3. Click OK to open the table.

There are benefits to using a database-contained table instead of a free table. For example, database-contained tables can have field names exceeding 10 characters in length; free tables cannot.

Tables can be simple or complex. You can make using data in tables more efficient through good planning — carefully designing not only table structure but table relationships.

For example, if you were designing an invoice tracking system, you could create one table that held all information on each invoice. It would be more efficient, however, to create tables that contained information on customers (name, address, phone numbers, customer ID, and zip code), on inventory (item, cost, price, quantity on hand, and supplier), on suppliers (name, address, phone numbers, ID, discount info, and contact), and other groups of information (zip code, city, state, and so on). Then, these tables would combine to produce invoices at one time, vendor orders another time, and reports of various kinds, including billing statements, at other times. Each individual file would be more manageable, duplications could be eliminated, and daily or repetitive operations would take less computer and personnel time. Some data entry would be simplified, enhanced, or eliminated, because the related tables would get the information automatically.

For more information on designing tables, see Choosing Data Types.

Accessing Data in Tables

When you have the data in tables, there are countless ways to use or manipulate it, but first you must access it.

The Data Session window provides the most effective way of accessing tables. Each table opens in its own work area. The Data Session window provides information on table properties and any relationships you might set up between tables.

To clear the data environment

  1. In the Command window, type the following:

    CLOSE ALL TABLES

  2. Press ENTER.

To view Table properties

  1. On the Window menu, click Data Session.
  2. In the Data Session window, select a table

    • If a table is already open, it is listed in the Aliases drop-down box, so you can select it.

    • If no table is open, click Open to access the Open dialog box, which makes it possible for you to specify the table to open.

    • If the listed table is in a database, and you click Open, the Open dialog box displays a list of the other tables in that database. You can select one from the list or click the Other button to specify a free table.

      **Note   **The Open dialog box shows available table (.dbf) files. You can open as many of these as you like. If you select a table that is part of a database, a second dialog box opens for the next table Open selection. This dialog box displays the tables available in the database you opened by selecting the table within it. If you want to select more free tables, click Other, and then choose a table.

To access a table for viewing or editing

  • In the Data Session window, select an open table, and then click Browse. You can continue until all the listed (open) tables are displayed.

    Note   Each time you browse a table in the Data Session window, the Command window displays a new SELECT statement followed by a BROWSE statement.

To view and modify table characteristics

  • In the Data Session window, select an open table, and then click Properties. This opens the Work Area dialog box, which provides information and access to the table structure and indexes.

In this environment, you can select any table and edit it, append and delete records, or modify the table structure.

When it opens, you can navigate the table, change or delete record contents, or add new data. Use View menu options to select the type of access you want. Use the Table menu options to navigate or modify the table.

**Note   **If you want more access control or complex manipulation of data, or if you want to use remote views, and related tables, create a database for your table. If you want to create an application, COM Server, or Web Service, using more features, such as labels, forms, reports, objects and class libraries, create a Visual FoxPro Project (starting in a Project is the best way any time).

For more information on using tables, see Working with Tables. For more information on the Data Session window, see Using Data Sessions.

See Also

Visual FoxPro Data and Field Types | Choosing Data Types | Working with Tables | Using Data Sessions | Walkthroughs