Let's connect the tables

In this chapter, we’ll see how to retrieve information from one table in order to insert it into another. In our example, we will modify the Orders table in order to retrieve the customer and insert the items.

If we haven't already done so, we'll create a customer to have data to display. This operation is not mandatory to link the tables, but only serves to see the result of our modification immediately.

Now that we have a customer and an item, we can start modifying the Orders table by creating a new field within it.

From Options, select the Fields page. Click Add, set the correct table (in this case the table of orders) and in Field Type choose the SubTable item. This field type allows you to select data from another table and automatically fill the fields of the current tab according to the matching rules. Remember that the data are not linked, but copied. If, for example, the customer's name is changed, this will not be automatically updated within their orders.

In the Field Label parameter enter the Customer value.

In the Main Table parameter, choose the Customers table. This value will inform the field from where to retrieve the data to be displayed and inserted inside the tab.

Finally, set the combinations to teach the field which values must be retrieved and on which fields they must be inserted.

Click Settings for the parameter main matches to display the management window.

Click the Add button to enter a new match row and select the [customer] value under the Orders fields column.

Then move the mouse over the Name field, shown in the grid Fields of Customers table, and double-click to insert it automatically. This operation tells the program that when we have selected a data from the subtable, it must take the name and write it automatically in the Customer field.

Click OK to confirm.

Save, close the window and try to place a new order.

As you can see, the field is present inside the Detail page.

In the next chapter we will see how to fix the display.

By opening the field dropdown menu, you will see a list of all the clients currently created. Choose a line by selecting it with the mouse and the program will automatically insert the name of the customer into the field.

We have connected our first two tables and now we will do the same thing for the items to use inside our orders. What we want to achieve, as it has been done with for the customer, is the automatic compilation of the Code field, inside the lines of the document, selecting an item.

From the Options in the Fields page, click the Add button and create a subtable that will read the data from Items and insert them into the orders_rows table. The fields are always created within their “space” of use.

As for the other subtable, after having indicated the table from which the data will be retrieved, set the matches.

Click OK and save the field.

I suggest going to the Actions button in the Options and clicking the Update Program item every time you make changes to the database. This will reload and apply all settings.

Now, by creating a new order and inserting lines inside the main grid, we can select an item from the drop-down list.

In this case, since the window is still empty, we can create a new item directly from the drop-down list.

We have created the main links and now we can add two fields to manage a description both inside the item and inside the order rows.

1. We create a text type description field inside the Items table.

2. We create a text type description field inside the orders_rows table.

Now let's modify the code field of the orders_rows table and to do so just double click on its row or use the pen button.

Click on Settings for the parameter Main machtes and add a new row by entering “description” on both columns.

Let's save everything and try to create a new order. Now, by entering a line on the main grid and selecting an item, both the code and the description will be filled in automatically.