• Creating a project.
• Creating an analysis.

	Estimated time: 40 mn

• Create the project linked to the application. This project will group all application elements (windows, codes, queries, reports, etc.).
• Create the analysis linked to the project. The analysis is used to describe all data files handled by the application.

	Note	This lesson will show you how to create an application with an HFSQL database. As already seen in the previous lesson, WINDEV allows you to create applications that handle other databases. The "Appendix 2. Using data from an external database" lesson presents the operations that must be performed to create a project and an analysis based on an SQL database (via OLE DB or via Native Connectors).

• To create the project:
  1. Start WINDEV.
  2. Go to the WINDEV home page (Ctrl + <).
  3. On the home page, click "Create a project" and select "Windows or Linux application". The project creation wizard starts. The different steps of the wizard will help you create your project. The information specified in this wizard can be modified later.
     

     	Tip	To create a project, you can also: Click in the quick access buttons. The new element window appears: click "Project".
  4. The first step of the wizard allows you to select the operating system for which the project is intended. Select "Windows platform" and go to the next step of the wizard ("Next").
  5. For this lesson, we are going to create a "blank" project. You can also create a project based on an existing example, provided with WINDEV. Select "Create a blank project" and go to the next step of the wizard.
  6. WINDEV then prompts you to enter the platform description. Keep the default options ("64-bit Windows executable" and "Executable with windows"). Go to the next step of the wizard.
  7. The wizard prompts you to enter the project name, location and description. In our case, name the project "WD My First Database".
  8. By default, WINDEV creates this project in the ".\My projects\WD My First Database" directory. You can keep this location or change it via the [...] button. For the project summary, type "The purpose of project is to manage customers and their orders".
     
  9. The different steps of the wizard appear on the left. These steps can be clicked directly. Since the other steps in the "Description" are not strictly necessary, you can click "Guidelines" directly.
  10. This step is used to define the code style. Don't change the suggested options. Go to the next step.
  11. This step is used to define the style book. Select "Phoenix".
      
      Go to the next step.
      

      	Note	The style book is defined based on skin templates. Skin templates allow you to quickly create outstanding interfaces. A skin template defines the window style as well as the style of all controls that will be used in this window. Thus, the interface will always be cohesive and beautiful.
  12. Click the "Database" step. We are now going to specify the information regarding the database.
  13. Select "Yes, create a new database" and validate. The analysis creation wizard starts.

	Note	To better understand the different lessons of this part and optimize your training, we advise you to create the "WD My First Database" project. A completed example is available at any time to check the validity of the operations performed. To open the completed project, go to the WINDEV home page (Ctrl + <), click "Tutorial" and select "My first database (Answers)".

• The steps of the analysis creation wizard are as follows:
  1. Specify the analysis name and directory. By default, the analysis name corresponds to the project name and the analysis directory is a ".ana" directory in the project directory. Keep the default parameters.
     
     Go to the next step of the wizard.
  2. Next, you can choose the types of databases used by the project. Select "HFSQL Classic" (WINDEV's default database).
     
     Go to the next step of the wizard.
  3. Validate. The data file creation wizard starts automatically.

• Data file: The analysis allows you to define the structure of the database files. A "Data file" corresponds to a "table" in some databases.
  In WINDEV, "Table" represents a graphic object used to see the content of a data file in a table and/or to enter rows. A table can be used to type the order details for example.
• Record: A record is sometimes called row. A data file record corresponds to all items defined for the data file.
• Item: In the analysis, an item represents a section of a data file. All items in a data file are used to define the structure of a record.
• Key/Index: With WINDEV and its HFSQL database, the concept of index is linked to the concept of key. A key is part of the item characteristics. Keys allow fast access to data and simplify loop through operations on data files. In WINDEV, if an HFSQL data file includes several key items, a single index file will be created at runtime.

• Customer,
• Order,
• PaymentMode,
• OrderLine,
• Product.

Creating a data file: using a preset data file

• The steps of the data file creation wizard are:
  1. In the wizard, select "Select a description among the preset data files".
     
     Go to the next step of the wizard.
  2. The list of preset data files is displayed.
     
     We are going to create the "Customer" data file. In the list of data files, select "Customer". Go to the next step.
  3. The wizard shows the list of items that will be included in Customer data file. This list allows you to manage several types of Customer data files.
     
  4. Click "None" and check only the following items: CustomerID, Company, LastName, FirstName, Address, PostalCode, City, StateCounty, Country, Phone, Cell, Email.
  5. Validate the wizard ("Finish").
  6. The "Customer" data file is automatically created in the data model editor.
     
  7. The new element window appears. We are now going to create the data file containing the orders.

Creating a data file: creating the data file and items

• To create a data file from the element creation window:
  1. Click "Data" then "Data file".
     

     	Note	You can also create a data file from the data model editor: in the ribbon, on the "Analysis" tab, in the "Creation" group, click "New file".
  2. The data file creation wizard starts.
  3. In the wizard, select "Create a new data file description".
     
  4. Go to the next step of the wizard.
  5. We are going to create the "Order" data file. Name the data file "Order" in the wizard. This name will be used:
     • to manipulate the data file in the code. The variable associated with the data file will be "Order".
     • to build the name of the associated physical data file ("Order.fic" file). The caption and description of the elements represented by the records in the data file appear automatically.
       

       	Note	In the wizard, you can enter a clear description for the links between data files in the "A record represents" field. A description is automatically created based on the data file name. In our case, type "An order".

       
  6. In the "Automatic identifier" section, keep "8-byte automatic identifier". If an automatic identifier is defined on the data file, it means that the data file includes a unique key, automatically managed by WINDEV.
     

     	Note	To create the identifier (an identifier is a unique key), you can create an item of type "Automatic ID". This identifier is automatically managed by WINDEV. Whenever a record is added to the data file, WINDEV automatically assigns a value to the data file identifier. This value is unique.
  7. Go to the next step and select the type of database associated with the data file. We are going to work on HFSQL Classic data files. Go to the next step.
  8. The data file is automatically created in the analysis. The description window of the items and indexes appears.
     

• First, we are going to create the "Date" item. This item will contain the order date.
  1. In the item description window, double-click in the "Name" column of the first empty row. This column automatically becomes editable. Type "Date".
  2. Click the "Caption" column. The item name is automatically displayed. Click the caption of the item to modify it: type "Order date". In the "Type" column, the "Text" type is automatically selected. Expand the list and select the "Date" type.
     
  3. This item will be a key item (index) in the data file: keys are used access and sort data faster.
     • In the case of an SQL database, the engine optimizes the use of indices.
     • To loop through a data file in ascending or descending order, you can simply specify a sequential key.
       

       	Note	A key is part of the item characteristics. When an item is created, you can specify whether it is: not key, unique key: the value of this key will be unique in the entire data file (i.e. in all the data file records). key with duplicates: the value of the key can be multiple times in the data file.
  4. The key can be defined as follows: re-select the row of the "Date" item to activate the description controls on the right-hand side of the window. Then, simply specify the type of key used. In our case, the date is a key with duplicates.
     
  5. You must also define the key sort order. The sort order defines the default order of the item. In our case, the default sort order of this key will be "ascending".

• We will create the "Status" item to get the order status.
  1. Position the cursor on a new table row. Enter:
     • the name: Status
     • the caption: Status
     • the type: Radio Button, List Box, Combo Box. In the window that appears, you can select the type of control to be created by default for this item. In this case, select Radio Button. Validate the window.
  2. In the lower section of the screen, click the link to display the parameters of the control bound to the selected item.
     
     The information typed in the new window that is displayed will be automatically used when creating windows linked to the data file. This window displays the type of control and the caption.
     We are going to type the following options in the "Content" tab:
     • Click the "Content" tab.
     • Click the "+" button to add the first option.
     • The option 1 corresponds to Pending. Type "Pending" in the first Edit control.
     • Click the "+" button to add the second option.
     • Type "Validated" instead of "Option 2".
     • Click the "+" button again.
     • Type "Canceled" instead of "Option 3".
       
  3. Validate the description window of the control linked to the item.
  4. Similarly:
     • Move the cursor to a new table row and create the "TotalBT" item. Select the "Currency" type for this item.
     • Move the cursor to a new table row and create the "TotalIOT" item. Select the "Currency" type for this item.
  5. That's it, the description of the "Order" data file is complete. Validate the description window of items.
  6. The "Order" data file appears in the data model editor. It is possible to enlarge the displayed data file. Simply click the data file, select the black handle at the bottom of the data file and move the mouse down.
     

Importing a CSV file

	Note	A CSV file is a text file that uses a specific format. This file contains data on each line. Data is separated by a delimiter character (a comma, semicolon or tab).

• the description of the data file in the analysis,
• the HFSQL data file with the data in the CSV file.

• To import a CSV file into the analysis:
  1. In the ribbon, on the "Analysis" tab, in the "Creation" group, expand "Import" and select "Import file/table descriptions...".
     

     	Tip	To import a CSV file (or any other file) into the analysis, you can also drag the CSV file from Windows explorer and drop it onto the data model editor. This will be presented in the next paragraph.
  2. The file import wizard starts.
  3. Go to the next step.
  4. Select the format of the files to import. In this case, select "Text file".
     
     Go to the next step of the wizard.
  5. Specify the path of the file to import: "\Tutorial\WD\Exercises\WD My First Database\PaymentMode.csv" in the WINDEV installation directory.
  6. Go to the next step of the wizard.
  7. Specify the following import options:
     • The records are delimited by: "<Carriage Return/Line Feed>".
     • The columns are delimited by: "<Semicolon>".
     • The strings are delimited by: "<None>".
     • Decimal separator: "<Automatic: dot or comma>"
       
  8. Don't forget to check "The first row contains the names of the columns".
  9. Go to the next step.
  10. The structure of the data file that will be created is displayed.
      
      Keep the default options. Go to the next step of the wizard.
  11. The content of the CSV file will be automatically converted to HFSQL. The wizard proposes to create the HFSQL file in the project directory.
      
      Keep the default options and go to the next step.
  12. Validate the wizard. WINDEV creates the data file.

• Let's examine the description of the imported data file:
  1. Select the "PaymentMode" data file.
  2. Right-click to open the context menu and select "Data file description".
  3. Change the data file caption in the window that appears: delete "(Imported)".
  4. Validate the file description window.

• Let's look at the description of the items in the imported data file:
  1. Select the "PaymentMode" data file.
  2. Right-click to open the context menu and select "Description of items".
  3. The "PaymentMode" data file does not contain an automatic identifier or a unique key. We will set the "Code" item as a unique key:
     • Select the code item if necessary.
     • On the right side of the screen, click "Unique key".
       
  4. We get the following data:
     
  5. Validate the item and data file description windows.

Importing existing data files directly

• To import HFSQL data files:
  1. In the Windows file explorer, open the following WINDEV subdirectory: "\Tutorial\WD\Exercises\WD My First Database".
  2. Select the "Product.fic" file.
  3. Drag the file "Product.fic" and drop it onto the WINDEV data model editor.
  4. The data file appears in the data model editor.

	Caution!	Only the description of the "Product" data file was imported into the analysis of our project. The data from the "Product" data file has not been imported into the project yet.

• To manipulate the data from the file that has been just imported:
  1. Open the project directory: on the "Home" tab, in the "General" group, click .
     
  2. Open a new file explorer on the "\Tutorial\WD\Exercises\WD My First Database" directory.
  3. Copy the "Product.fic", "Product.mmo" and "Product.ndx" files from "\Tutorial\WD\Exercises\WD My First Database" to the EXE subdirectory of your project directory.
     Remark: Three files must be copied:
     • "Product.fic": file containing the data,
     • "Product.mmo": memo file containing the product images,
     • "Product.ndx": file containing the indices to optimize searches in the data file.

• First, let's create the link between the "Customer" and "Order" data files: a customer can have one or more orders and each order is linked to a customer.
  1. In the ribbon, on the "Analysis" tab, in the "Creation" group, click "New link". The mouse cursor turns into a pen.
  2. Click the "Customer" data file, then the "Order" data file.
  3. The link creation wizard starts.
  4. Answer the questions asked by the wizard:
     
     • Each Customer has at least one Order: No
     • Each Customer can have several Orders: Yes
     • Each Order has at least one Customer: Yes
     • Each Order can have several Customers: No
       

       	Note	You can also specify the link cardinalities directly in the wizard.
  5. Go to the next step. The wizard automatically shows the key used by the link (CustomerID).
     
  6. Go to the next step of the wizard. The wizard proposes to create the "CustomerID" key in the Order data file to store the customer corresponding to the order.
     
  7. Accept this option by going to the next step.
  8. This step defines the integrity rules that will be automatically applied. In our case, you can set the behavior when deleting a customer and when modifying the customer identifier.
  9. Validate the integrity rules by going to the next step of the wizard.
  10. Click "Finish". The link is automatically created in the data model editor.

• Likewise, create a link between the PaymentMode and Order data files. These two data files are linked as follows:
  • An order must have a payment mode.
  • A payment mode can be used in several orders.
  In the wizard:
  • The cardinalities are as follows: PaymentMode (0,n), Order (1,1)
  • The link key corresponds to the "Code" item.

• Now we will create a link between the "Product" and "Order" data files. This link will be used to create a link file, the file of order lines.
  1. Likewise, create the link between the data files.
  2. Answer the questions asked by the wizard:
     • Each Product has at least one Order: No
     • Each Product can have several Orders: Yes
     • Each Order has at least one Product: No
     • Each Order can have several Products: Yes
       
  3. Go to the next step. The wizard proposes to create a relation file. Keep "Create the relation file automatically" and go to the next step.
  4. The wizard proposes the unique key of the Product data file. Select "Reference". Go to the next step.
  5. Validate the key creation by going to the next step.
  6. Keep the default options of the integrity rules and go to the next step.
  7. The wizard proposes the unique key of the Order data file to use: "OrderID". Go to the next step.
  8. Validate the key creation by going to the next step.
  9. Keep the default options of the integrity rules and go to the next step.
  10. Click "Finish". The relation file is automatically created in the data model editor.
      

• We will modify the relation file created by WINDEV. This relation file will contain the order lines. We are going to:
  • Modify the name of the data file.
  • Modify the name of its items.
  • Add items to get the quantity of products ordered and the total of the order line.

• First, we are going to rename the data file. We already did something similar when we changed the caption of the imported data file. However, in this case, not only the caption will be modified: we will also rename the physical file linked to the data file description.
  1. Select the "Product_Order" data file.
  2. Right-click to open the context menu and select "Data file description".
  3. In the window that appears, go to the "General" tab and change:
     • the name of the data file to "OrderLine".
     • the name on the disk to "OrderLine".
       

• We will now modify the items of this relation file.
  1. Click (at the bottom left of the file description window) to open the description of the data file items.
     
  2. This relation file has 3 items. Select the "Reference_OrderID" item. This item is a composite key.
     

     	Note	A composite key is a set of items that constitute an index. This type of key is used to loop through the data file based on complex criteria or to perform specific searches on several items at the same time.
  3. To rename this item:
     • Click the "Name" column.
     • Replace "Reference_OrderID" by "OrderLineID".
     • Click the "Caption" column.
     • Replace the current caption with "OrderLine ID".

• Now we are now going to add 3 new items to this relation file: Quantity, TotalIOT and TotalBT.
  1. Move the cursor to a new row and create the "Quantity" item. Select the "Numeric" type for this item.
  2. Move the cursor to a new row and create the "TotalIOT" item. Select the "Currency" type for this item.
  3. A window indicates that the item already exists in the analysis and offers you to reuse its characteristics:
     
     Keep the selected options and validate ("OK").
  4. Move the cursor to a new row and create the "TotalBT" item. Select the "Currency" type for this item. Once again, use the existing description.
  5. The description of the "OrderLine" data file items is as follows.
     
  6. Validate the item description and the data file description (click "OK").

• The analysis is as follows:
  

• To generate the analysis:
  1. In the ribbon of the data model editor, go to the "Analysis" tab, "Analysis" group and click "Generation".
  2. The analysis generation starts automatically.

• The automatic modification wizard starts. Validate the different steps until you reach the screen that lists the data files to take into account.
  • WINDEV detects that the PaymentMode data file must be updated. Keep this data file selected.
  • WINDEV detects that the Product data file is out of sync. Select this data file.
    
  • Go to the next step.
  • The wizard proposes to make a back up copy of the existing data files, don't change anything and go to the next step.
  • The wizard allows you to define a password to protect the data files that were modified during the automatic modification. Keep the default options and go to the next step.
  • The list of data files to modify is displayed. Validate the wizard.
  • The data files are updated.

• Close the data model editor.

• Create a full application via the RAD (Rapid Application Development).
• Create a full custom application. This method will be used to develop a full application based on a database in part 4 of this tutorial. This application will be based on an analysis corresponding to the one that was created in this part.