- Lesson 2 - Creating files in the analysis
- Next steps
- Creating a data file
- Creating a data file: using a preset data file
- Creating a data file: creating the data file and items
- Importing a CSV file
- Importing existing data files directly
- Creating links
- Generating the analysis
- To sum up
- Further information
Tutorial - Creating a WINDEV project and its analysis
Lesson 2 - Creating files in the analysis We will cover the following topics: - Creating a data file description
- Creating items
- Creating links between files.
- Generating the analysis.
![Durée de la leçon Durée de la leçon](https://doc.windev.com/en-US/images/image.awp?langid=3&name=dur%E9e.png&0) 40 mn In the previous lesson, we created the project and the associated analysis. For this example, we will continue with the creation of the analysis. It can be used in a customer and order management application. This analysis includes five different data files (tables): - Customer,
- Order,
- PaymentMethod,
- OrderLine,
- Product.
We are going to: - Create the data files of this analysis using the different methods available in WINDEV:
- use a preset file.
- create a file from scratch.
- import a CSV file.
- import an existing HFSQL data file.
- Generate the analysis to use it in the various elements of the project.
Creating a data file: using a preset data file First, we will create the Customer data file. To do so, we will use a preset file available in WINDEV: - In the data model editor, on the "Analysis" tab, in the "Creation" group, click "New file". The data file creation wizard opens.
- In the wizard, select "Select a preset data file description".
Proceed to the next step of the wizard.
- 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.
- The wizard shows the list of items that will be included in Customer data file.
![](https://doc.windev.com/ext/images/us/note_v2.png?478445243) This list allows you to manage several types of Customer data files. We will only keep some items: - Click "None" to deselect all items.
- Check the following items: CustomerID, Company, LastName, FirstName, Address, PostalCode, City, StateCounty, Country, Phone, Cell, Email.
- Finish the wizard.
The "Customer" data file is automatically created in the data model editor.
Creating a data file: creating the data file and items Now, let's create the Order file from scratch: - On the "Analysis" tab, in the "Creation" group, click "New file". The data file creation wizard starts.
- In the wizard, select "Create a new data file description".
Proceed to the next step of the wizard.
- Enter the name of the "Order" file in the wizard. This name will be used:
- to handle 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.
In "A record represents", enter "An order".
![](https://doc.windev.com/ext/images/us/note_v2.png?478445243) The name you enter in this field is used to generate a clear description of the links between data files. A description is automatically created based on the data file name.
- In the "Automatic identifier" section, keep "8-byte automatic identifier".
![](https://doc.windev.com/ext/images/us/note_v2.png?478445243) If an automatic identifier is defined on the data file, it means that the data file includes a unique key, 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. - 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. Click "Finish" to confirm.
The data file is automatically created in the analysis. The description window of the items and indexes appears.
We are going to create the items of the "Order" data file. In the data file description window, you will notice that an item was automatically created: "OrderID". This item corresponds to the automatic identifier of the data file. This item consists of the name of the data file and the letters "ID". We are going to create the other items of this data file. First, let's create the "Date" item. This item will contain the date of the order. - In the item description window, double-click in the "Name" column of the first empty row. This column automatically becomes editable. Type "Date".
- 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.
- This item will be a key item (index) in the data file.
![](https://doc.windev.com/ext/images/us/note_v2.png?478445243) Keys are used access and sort data faster. A key is part of the item characteristics. An item can be: non-key, unique key, key with duplicates or primary key. For more details on the different types of key, see Key items. - In this example, the "Date" item is a key with duplicates: the value of the key can be multiple times in the data file.
- Select the row of the "Date" item again to see its description on the right of the screen.
- Specify the type of key used: key with duplicates.
- You can also define the default sort order of the key. In our case, the default sort order of this key will be "ascending".
We will create the "Status" item to get the order status. - 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.
- 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".
- Validate the description window of the control linked to the item.
| It's your turn: Similarly:- Move the cursor to a new row and create the "TotalBT" item. Select the "Currency" type for this item.
- Move the cursor to a new row and create the "TotalIOT" item. Select the "Currency" type for this item.
|
That's it, the description of the "Order" data file is complete. Validate the description window of items. The "Order" data file appears in the data model editor. To enlarge the data file in the diagram, click the data file and drag the bottom handle down. Importing a CSV file A different method will be used to create the "PaymentMethod" data file, which contains the payment characteristics: importing a CSV file. ![](https://doc.windev.com/ext/images/us/note_v2.png?478445243) 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). From the CSV file containing the data, WINDEV will create: - 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: - In the ribbon, on the "Analysis" tab, in the "Creation" group, expand "Import" and select "Import file/table descriptions...".
- The file import wizard starts.
- Select the format of the files to import. In this case, select "Text file".
Proceed to the next step of the wizard.
- Specify the path of the file to import: "\Tutorial\WD\Exercises\WD My First Database\PaymentMethod.csv" in the WINDEV installation directory.
- Proceed to the next step of the wizard.
- 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>"
- Don't forget to check "The first row contains the names of the columns".
- Go to the next step.
- The structure of the data file that will be created is displayed.
Keep the default options. Proceed to the next step of the wizard.
- 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.
- Finish the wizard. WINDEV creates the data file.
Let's examine the description of the imported data file: - Select the "PaymentMethod" data file.
- Right-click to open the context menu and select "Data file description".
- Change the data file caption in the window that appears: delete "(Imported)".
- Click
(bottom left) to open the description of the data file items. - This data file does not have 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".
- We get the following data:
- Validate the item and data file description windows.
Importing existing data files directly The last method for creating data files consists in importing the existing HFSQL data files. We will use this method to create the "Product" data file.
To import HFSQL data files: - In the Windows file explorer, open the following WINDEV subdirectory: "\Tutorial\WD\Exercises\WD My First Database".
- Select the "Product.fic" file.
- Drag the file "Product.fic" and drop it onto the WINDEV data model editor.
- The data file appears in the data model editor.All necessary data files are now in the data model editor.
Warning
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: - Open the project directory: on the "Home" tab, in the "General" group, click
. - Open a new file explorer and go to the "\Tutorial\WD\Exercises\WD My First Database" directory.
- 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.
All data file descriptions required by the account management application have been created. Now we will create the links between the data files. ![](https://doc.windev.com/ext/images/us/note_v2.png?478445243) A link is used to define the integrity constraints (cardinalities) between two data files. First, let's create the link between the "Customer" and "Order" data files. We can consider that: - a customer can have one or more orders.
- each order is linked to a customer.
To create the link: - In the ribbon, on the "Analysis" tab, in the "Creation" group, click "New link". The mouse cursor turns into a pen.
- Click the "Customer" data file, then the "Order" data file.
- The link creation wizard starts.
- Answer the questions that appear in 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
![](https://doc.windev.com/ext/images/us/note_v2.png?478445243) If you know the cardinalities: You can also specify the link cardinalities directly in the wizard.
- Go to the next step. The wizard automatically shows the key used by the link (CustomerID).
- Proceed 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.
- Accept this option by going to the next step.
- 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.
- Validate the integrity rules by going to the next step of the wizard.
- Click "Finish". The link is automatically created in the data model editor.
| It's your turn: Likewise, create a link between the "PaymentMethod" and "Order" data files. These two data files are linked as follows:- An order must have a payment method.
- A payment method can be used in several orders. In the wizard:
- The cardinalities are as follows: PaymentMethod (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. - Likewise, create the link between the data files.
- 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
- 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.
- The wizard proposes the unique key of the Product data file. Select "Reference". Go to the next step.
- Validate the key creation by going to the next step.
- Keep the default options of the integrity rules and go to the next step.
- The wizard proposes the unique key of the Order data file to use: "OrderID". Go to the next step.
- Validate the key creation by going to the next step.
- Keep the default options of the integrity rules and go to the next step.
- 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. - Select the "Product_Order" data file.
- Right-click to open the context menu and select "Data file description".
- 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. - Click
(at the bottom left of the file description window) to open the description of the data file items. - This relation file has 3 items. Select the "Reference_OrderID" item. This item is a composite key.
![](https://doc.windev.com/ext/images/us/note_v2.png?478445243) 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. - 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 will add 3 new items to this relation file: Quantity, TotalIOT and TotalBT. - Move the cursor to a new row and create the "Quantity" item. Select the "Numeric" type for this item.
- Move the cursor to a new row and create the "TotalBT" item.
- 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").
- Move the cursor to a new row and create the "TotalIOT" item. Once again, use the existing description.
- The description of the "OrderLine" data file items is as follows.
- Validate the item description and the data file description (click "OK").
The analysis is as follows:
The generation of the analysis consists in validating the changes made to the analysis (create data files, add or delete items, etc.) and applying them to the entire project (pages, bound controls, reports, etc.). The project will thus "know" all the data files, items and links defined in the analysis. It will be very easy to handle these elements in the different editors of WINDEV. If changes have been made to the analysis, a synchronization dialog box appears automatically when the data model editor is closed. You can also generate the analysis manually. That's what we are going to do. To generate the analysis: - In the ribbon of the data model editor, go to the "Analysis" tab, "Analysis" group and click "Generation".
- The analysis generation starts automatically.
- Since we modified the description of the data files in the analysis, WINDEV automatically starts the data file update procedure. This operation updates the data files (".fic" files) based on to their description in the analysis.
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 PaymentMethod 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. Finish the wizard.
The data files are updated. Close the data model editor. We have covered the main steps to create an analysis: - Creating a data file using different methods.
- Creating simple links between files.
- Creating a complex link.
![](https://doc.windev.com/ext/images/us/note_v2.png?478445243) Completed project If you want to check the end result of the steps described here, you can open a completed version of the project. This project illustrates the expected end result. To open the completed project, go to the WINDEV home page and click "Tutorial", then in "Tutorial - Create a WINDEV project and its analysis", double-click "My first database - Answers". Once your analysis has been defined and generated, you can:
|
|
|