Exibir origem

Creating new query

It enables you to create query. You can define in which table System must search information that will be in query.

In this stage you can match data from different files in the same query, for example, a list of products with pending purchase orders.

To establish a relation, the files must have at least one field in common. For example, the Purchase Order table (SC7) has the field C7_PRODUTO, where the code of the product to be purchased is saved; the file of Products (SB1) has the field B1_COD, where the product code is saved for its identification. However, note the SB1 file contains data of the products without duplicity, while the file SC7 may present different orders for a same product.

Thus, it is not enough to make a list with just the code of the product. You must relate the product record with the purchase order record to know which ones are pending.

The files have in common the product code. With this code, it is possible to relate the tables. Even having different names, the fields hold the same information.

By relating the tables, you can query/print data from the record of products and purchase orders.

Procedures

To create query:

1.	Select Create New Query and click OK.

The list of data tables is displayed.

2. Select the table to be used as the main table.

The main table can be located through the field Search For.

Linha Microsiga Protheus - Inglês > Creating New query-mpview_criando_uma_consulta.htm > help_button.zoom40.gif Important:

The main table is the table from which the system will search for information to be used in the query.

3. Click Next.

4.	In the window Open Table, click Add Relation [PICTURE sdunew.png]. The System displays the window Add Relation. Note it is presented a suggestion of relation in Main Table Expression/Related Table Expression.

5.	If the relation indicated is the required, click it and then, OK.

6.	If you need to use another relation, click the scroll bar of Table to check the pre-defined relations or use the field Search Table. A list of default relations between the main table selected and other System tables is displayed.

7.	Click the table to be related to the main table.

Linha Microsiga Protheus - Inglês > Creating New query-mpview_criando_uma_consulta.htm > help_button.zoom40.gif Important:

Note that the information in Expression Related Table was changed with the name of the field of the related table chosen, common to the main table.

Therefore, through fields displayed in Expression Main Table/Expression Related Table, it will be possible to establish the relation.

Note also that the name of the field is preceded by the file identification followed by “_” (example: D1_COD). This syntax is used in tables of Microsiga Protheus® to indicate the field belongs to the table.

Creating Specific Relation

System enables list of pre defined relations among tables to facilitate queries creation. You can create your own relation. Suppose you have to create specific table for your company and also query containing data of this table combined with data of standard table of System, then you must create specific relation.

Linha Microsiga Protheus - Inglês > Creating New query-mpview_criando_uma_consulta.htm > help_button.zoom40.gif Note:

To create relation common field among tables must exist.

Procedures

To create specific relation:

1.	Click Add Relations and, then, New Relation.

Window New Relation is displayed to define specific relation. Field Expression Main Table displays main table selected.

2.	To select one or more fields to use in this relation, click option displayed beside field Expression Main Table.

Window Query Field with list of main table of fields is displayed.

3.	Click key field for relation, then click OK.

Field selected was displayed in field Expression Main Table.

Linha Microsiga Protheus - Inglês > Creating New query-mpview_criando_uma_consulta.htm > help_button.zoom40.gif Note:

In some cases you have to create relations with more than one field so information is really individual or separated.

For example: It is a report with main table based on Customer Register so you must create relations with fields Customer Code and Store (A1_COD+A1_LOJA). It guarantees query will display separated data for each customer store.

4.	Click option beside field Expression Main Table to add field to key relation of main table.

Window Query Field with list of main table of fields is displayed again.

5.	Click other key field that will be part of relation and then click OK.

Note sign (+) and field selected was added in field Expression Main Table.

Linha Microsiga Protheus - Inglês > Creating New query-mpview_criando_uma_consulta.htm > help_button.zoom40.gif Tip:

Repeat this procedure until all fields are added to make part of Relation Key in case you need to add more fields to separate information.

6.	Select table related after defining all key fields of main table.

Enter table to be related in field Search Table.

7.	Then click Query Fields, beside the field Expression Related Table.

Window Query Field with list of fields of table selected is displayed.

8.	Click field and then OK.

Repeat this operation to add all fields to be part of relation key, as you did in main table.

9. To finish relation among tables, click End.

10.	You must select relation created.

11.	Type name of table related in field Search Table.

Note information of Expression Related Table was changed and now have name of field chosen of table related and common to main table.

12.	Click OK to confirm relation.

Relation defined is displayed in next window.

You can create several relations between main table and tables related. It allows you to create query with data of table Products (SB1), Purchase Order (SC7) and Items of Purchase Invoice (SD1).

13.	To add more relation to main table click again Add Relation and configure it as described in previous procedures.

14.	Add all relations and click End.

Procedures

To delete relations:

1.	Place the cursor on the relation to be deleted.

2.	Click Delete Relation.

3.	Confirm the operation.