issue83:libreoffice
Différences
Ci-dessous, les différences entre deux révisions de la page.
Prochaine révision | Révision précédenteDernière révisionLes deux révisions suivantes | ||
issue83:libreoffice [2014/05/11 18:14] – créée andre_domenech | issue83:libreoffice [2014/09/05 14:43] – [4] auntiee | ||
---|---|---|---|
Ligne 1: | Ligne 1: | ||
+ | ===== 1 ===== | ||
+ | ** | ||
Titre / LibreOffice Pt36: Base Views | 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, | If you work with Base long enough, you will run into a situation where you need a table structured a little differently, | ||
Ligne 23: | Ligne 23: | ||
LastName|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. | + | 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:// | + | LibreOffice Partie 36 : Les vues de Base |
+ | |||
+ | Si vous travaillez avec Base pendant quelque temps, vous rencontrerez une situation où vous aurez besoin d'une table dont la structure est un peu différente ou vous aurez besoin des résultats d'une requête sous forme d'une table que vous pourrez utiliser. La solution à ces problèmes est « Vues ». Une vue est une requête sous forme d'une table qui pourra servir dans d' | ||
+ | |||
+ | Nos tables | ||
+ | |||
+ | Nous allons créer une base de données simple avec deux tables. La base de données suivra des projets pour une société et nous permettra d' | ||
+ | |||
+ | 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 | ||
+ | |||
+ | Vous verrez à droite le SQL pour créer les tables. Créer une nouvelle base de données, puis allez à Outils > SQL et tapez les commandes à la main ou faites un copier/ | ||
+ | |||
+ | ===== 2 ===== | ||
+ | |||
+ | **You can also get the above SQL commands on pastebin.com at http:// | ||
The key to our task is the foreign key “MemberID” in the “Project” table, which connects to the “TeamMember” table' | The key to our task is the foreign key “MemberID” in the “Project” table, which connects to the “TeamMember” table' | ||
Ligne 33: | Ligne 59: | ||
Create Query / View | 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 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: | + | Les commandes SQL ci-dessus sont également disponibles sur pastebin.com à http:// |
+ | |||
+ | La clé de cette tâche est la clé externe « MemberID » dans la table « Project », qui est relié au champ ID de la table « TeamMember ». Quand nous créons notre formulaire, nous créerons un menu déroulant permettant de sélectionner le responsable du projet parmi les membres de l' | ||
+ | |||
+ | Si les tables ne affichent pas dans votre liste de tables après avoir exécuter les commandes, Affichage > Actualiser les tables (Review > Refresh Table) établira la liste. | ||
+ | |||
+ | Créer une requête / vue | ||
+ | |||
+ | Nous utiliserons un processus en deux étapes pour créer notre vue. D' | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ===== 3 ===== | ||
+ | |||
+ | **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 " | SELECT " | ||
Ligne 42: | Ligne 84: | ||
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”. | 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. | + | 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 | + | Pour créer la requête, nous sélectionnons l' |
+ | |||
+ | SELECT " | ||
+ | " | ||
+ | |||
+ | Si vous connaissez le SQL, cela ressemble à un requête standard sauf pour ce qui concerne les doubles symboles trait vertical « || ». On les utilise pour concaténer les chaînes. Dans la commande, nous sélectionnons chaque « FirstName » et l' | ||
+ | |||
+ | Transformer la requête en vue est aussi simple que faire un clic-droit sur le nom de la requête et sélectionner « Créer une vue ». Appelez la vue « TeamView ». Si vous sélectionnez « Tables » dans le volet « Base de données », vous verrez que « TeamView » figure dans la liste des tables. | ||
+ | |||
+ | ===== 4 ===== | ||
+ | |||
+ | **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. | 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. | ||
Ligne 50: | Ligne 103: | ||
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. | 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. | + | 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. | + | Créer les formulaires |
+ | |||
+ | Nous allons créons un formulaire pour les membres de l' | ||
+ | |||
+ | Sur le premier écran, choisissez « Table : TeamMember » dans le menu déroulant. Déplacez les champs « FirstName » (prénom) et « LastName » (nom) dans le menu déroulant appelé « Champs du formulaire ». Cliquez sur le bouton « Suivant > ». Il n'y a pas de sous-formulaire alors cliquez sur « Suivant > » à nouveau. À l' | ||
+ | |||
+ | Pour le formulaire projet nous avons besoin pour à peu près la même chose sauf qu'il faut utiliser « Table: Project » à partir du menu déroulant à l' | ||
+ | |||
+ | ===== 5 ===== | ||
+ | |||
+ | **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. | 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. | + | Save your changes and close the design window.** |
+ | |||
+ | Quand vous créez des formulaires avec l' | ||
+ | |||
+ | Comme table, sélectionnez « TeamView » et cliquez sur « Suivant > ». Le champ que nous voulons dans la Zone de liste est « Name » ; sélectionnez donc « Name » et cliquez sur « Suivant > ». Enfin, il faut « Sélectionner les champs dont le contenu doit coïncider ». Pour la « Value Table », choisissez « Member ». Il faut sélectionner ID comme champ pour « List Table », car c'est la clé primaire qui s' | ||
+ | |||
+ | Enregistrez les modifications et fermez la fenêtre d' | ||
+ | |||
+ | ===== 6 ===== | ||
+ | |||
+ | **Now, if you entered names in “TeamMemberForm”, | ||
+ | |||
+ | 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.** | ||
+ | |||
+ | Maintenant, si vous avez mis des noms dans le formulaire « TeamMemberForm », vous pouvez ouvrir « ProjectForm » et ces noms-là s' | ||
+ | |||
+ | Dans cet article, nous avons parlé de l' | ||
- | Now, if you entered names in “TeamMemberForm”, | ||
- | 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. | + | AE : Je pense qu'il faut laisser tel quel, car c'est du SQL et j'ai volontairement laissé toutes les étiquettes de champs, etc. en anglais. Si vous voulez, on peut mettre texte et tableau en français, mais... |
TABLEAU | TABLEAU |
issue83/libreoffice.txt · Dernière modification : 2014/09/05 15:19 de andre_domenech