Video script

In this video, we'll create a working database in just a few clicks and also take a moment to familiarize ourselves with the 4D environment.

Let's start with creating a table:

• By selecting "Table" in the Explorer
• Then clicking the "+" button on the bottom left of the window.
• Let's name this table "Interventions" and then click OK.

Automatically, this table is created and appears in the "Structure" window. This "structure window" represents the data model including all of the tables, fields and relations.

The Inspector palette also appears. This palette shows the properties of each object that is selected, namely: the structure window, a table, fields or, later on, a relation.

To add fields to this table, just double-click one of the rows in the table and then create the fields one by one.

We're going to create 4 fields:

• the intervention date
• the time
• the intervention object
• and the initials of the technician.

You can move a table in 4D by clicking its title or by dragging its lower right corner to widen it so that its fields appear.

Once these 4 fields are created, we are going to give each one of them a type so that 4D can perform any validity checks needed.
The date is a Date type and the time is a Time type and the two other fields are Alpha types.

We're now going to add records to this table:

• Click on the Records menu
• Then choose "Show Current Table"

Since we haven't yet created a form, 4D offers to create automatic forms for the current table or for all the tables you created.

Click Yes.

4D's default display is a list that shows the data as a spreadsheet where you can search, sort or print the selection.

To add a record, click on the "Records" menu and choose "New Record".

Just fill in each of the fields and then validate the record by clicking on the Accept button.

Each time you validate a record, another blank record appears to be filled in until you click the "Cancel" button and end data entry.

When we finish entering records and click Cancel, we go back to the list form. This list contains all the records that were created.

You can then use this small palette to navigate between the different tables.

To modify a record, just double-click it and then make your changes and save it. If you don't want to save your changes, click Cancel.

Of course, most of the time you already have data entered or even exported from another database, data from spreadsheets or in the form of a text file.

After this first short look around, we're going to import data so that we can quickly learn how to query and sort data, and to design easily customizable reports.

To import data:

• Click in the "File" menu and choose "Import => From File"
• Choose the text file that you want to import; in our case, this is Interventions
• The Import settings dialog box lets you choose the fields and import options
• When there is more than one table, we can select the table in question
• Then, in the lower part of the window, match up the fields
• And possibly indicate any character sets. It just so happens that here, we have deliberately chosen a file from non-Unicode macros, so we are going to convert it to Mac Roman and the accents are rectified
• In order to avoid importing the title row, we click on the "Header" tab and indicate that the import should start from row 2
• Then, we click on Import
• In just a few moments, we have 11,730 rows that were imported out of a total of 11,732, since we had already created 2 manually.

Once the import is finished, we can perform queries and sorts, and produce reports.

Let's start with queries:

• Click on the "Query" button
• Click on the criteria to evaluate (for example, "object")
• Then on the comparison operator (contains)
• And indicate the value (for example, "ware") and click on Query
• The search returns these records because "ware" is contained in both "software" and in "hardware".

Let's refine the query:

• Click on the "Query" button
• Change the value to "dware" and click on Query again
• This time we get a list of interventions concerning the "hardware".

Let's refine the query even more and only search for interventions by the technician MA01:

• "Query"
• Click on "Add" in the bottom right of the window (a 2nd row appears in the top part of the screen)
• Click on the criteria "Initials" then on the operator "contains" then indicate the value "MA" and lastly on the "Query" button
• We get 328 records.

To sort this information in the order it was carried out by the date and time of the intervention:

• Click on the arrow to the right of the "Query" button and choose "Order by..."
• Drag the intervention date from the left column to the right one and then do the same for the intervention time.
• The 2 triangles to the right show whether the sort is in ascending or descending order. Just click on this arrow to change from one order to the other.
• Next click on Order by.

The information is sorted in ascending order.

Now let's look for interventions during a particular time period:

• Click on "Query"
• Then click on the "Clear All" button
• Select the intervention date criteria, the "is greater than or equal to" operator and indicate "12/07/11" as the value
• We add a line and select the intervention date again, then the operator "is less than or equal to", enter the value "17/07/11" and then hit query.

We get 54 records that correspond to the interventions that fall within this period.