Full Circle Magazine FR

Titre / LibreOffice Pt36: Base Views If you work with Base long enough, you will run into a situation where you need a table structured a little differently, or you’ll need the results of a query as a table you can use. The answer to these problems is ‘Views’. A view is a query which acts like a table you can use in other queries, forms, or controls. Today, I will show you an example of how this is useful when creating forms. Our Tables We are going to create a simple database with two tables. The database will track projects for a company and allow us to assign a team member to each project. Below is the structure of the tables we will create. Project Table Field|Type|Properties ID|Integer|Primary Key, Auto Increment Title|varchar(50)|Not Null Description|varchar(250) Due|Date MemberID|Integer|foreign key TeamMember Table Field|Type|Properties ID|Integer|Primary Key, Auto Increment FirstName|Varchar(25)|Not Null LastName|Varchar(25)|Not Null Shown right is the SQL to create the tables. Create a new database document, then go to Tools > SQL, and type the commands in by hand or copy and paste.

You can also get the above SQL commands on pastebin.com at http://pastebin.com/Wyb3R5Fz. The key to our task is the foreign key “MemberID” in the “Project” table, which connects to the “TeamMember” table's “ID” field. When we create our form we will create a drop-down list for selecting the team member who is responsible for the project. Notice that the “TeamMember” table provides first and last name fields. The list control allows us to use only one field in the list. We could display just the last name in the drop-down list, but what if two team members have the same last name. We will solve this problem by creating a view that will combine the first and last name into one field called “Name”. We will use our view to populate the drop-down list box. If the tables do not show in your table list after running the commands, Review > Refresh Table will populate the list. Create Query / View To create our view, we will use a two-step process. First, we create a query, then convert the query into a view. In our query, we combine the first and last name fields to create one field. We will also get the “ID” field, as we will need it to connect back to the “Project” table.

To create the query, we select the “Queries” option in the “Database” pane. In the “Actions” pane, select “Create Query in SQL View.” The follow SQL command will create our query: SELECT “FirstName” || ' ' || “LastName” AS “Name”, “ID” FROM “TeamMember”; If you are familiar with SQL, this looks like a standard query except for the double pipe symbols “||”. They are used to concatenate strings together. In the command, we select each “FirstName” and join it to a space, then take the result and join it to “LastName.” Finally, we name this string “Name.” We also get the “ID” as it identifies each record. You can test the query to make sure it works, but at this point your results are blank, but the query should run without error. Save the query as “QueryTeam”. Turning the query into a view is as simple as right-clicking the query name and selecting “Create as View.” Name the view “TeamView.” If you select “Tables” under the “Database” pane, you will see “TeamView” listed under the tables.

Create the Forms We will create a team member form and a project form for data input. The team member form is the easiest, so let's make it first. Click on the “Forms” icon in the “Database” pane and select “Use Wizard to Create Form.” The form wizard will display. On the first screen, select “Table: TeamMember” from the drop-down box. Move the “FirstName” and “LastName” fields into the list box labeled “Fields in the form.” Click the “Next >” button. There is no subform so just click “Next >” again. On step 5, use any of the options for arranging the controls except tabular. On step 6, just accept the defaults. With Step 7 pick a style that you like. When you get to step 8, name the form “TeamMemberForm” and leave it on “Work with the form.” When you click the “Finish” button, the form will open for input. Add a few names for testing the project form when we finish it. For the project form, we need about the same thing, except use “Table: Project” from the drop-down in step 1, and select all the fields except for “ID” for inclusion in the form. Name the form “ProjectForm” and select “Modify the form” on step 8. This time, instead of the form opening for input, it opens for editing.

When you create forms using the form wizard, the wizard groups a text box for most data types with a label for each field. In order to change the control for the data field, you have to ungroup the text box from the label. In our case, we want to change the “Member” field, so right-click the “Member” label and text box and select Group > Ungroup. Click on the form background to unselect both. Right-click on the text box and select “Delete.” From the forms toolbar, select the list box. If the form toolbar is not showing then View > Toolbars > Form Controls to display it. Once you select the list box, your cursor will become a crosshair, +. Click and drag to create the list drop-down box. When you release the mouse button the list box wizard will pop up. Select “TeamView” as your table and click “Next >”. The field we want to fill the list box is “Name”, so select “Name” and click “Next >”. Finally, we need to match the fields from the two tables. For the “Value Table” select “Member”. “ID” is the field to select for “List Table” as it is the primary key that matches up with the “Member” foreign key field in the “Project” table. Save your changes and close the design window.

Now, if you entered names in “TeamMemberForm”, you can open the “ProjectForm” and those names will appear in the drop-down list box we created. You will want to test creating several projects and assigning members to them to test the workability of your forms. In this article, we discussed the use of a LibreOffice Base view to create a new table from an existing table. We used this view in the construction of a form that automated the retrieval and selection of records from that view. TABLEAU drop table “Project” if exists; drop table “TeamMember” if exists; create table “TeamMember” ( “ID” integer generated by default as identity (start with 1) not null primary key, “FirstName” varchar(25) not null, “LastName” varchar(25) not null ); create table “Project” ( “ID” integer generated by default as identity (start with 1) not null primary key, “Title” varchar(50) not null, “Description” varchar(250), “Due” date, “Member” integer, constraint FK_MEM foreign key (“Member”) references “TeamMember” (“ID”) );