Understanding lookup fields
A lookup field (also known as a foreign key) is how AppGini links 2 fields from 2 tables together. For example, let's say that our database contains a products table, a suppliers table, and a product categories table.
Tip: The list of video tours to the left of this page contains some helpful videos explaining several features of lookup fields. It takes less than 20 minutes to watch them all. So, please do.
The products table stores data about each product, including the supplier of the product, and the product category. Since suppliers and categories are stored in their own tables, the products table should look up those two tables when storing supplier and category data for each product.
The products table is thus a child table that has 2 parent tables: suppliers and categories. To achieve this, we should create a field in the products table to hold supplier data, and another one to hold category data. Each of these two fields is called a lookup field. We can define its properties in the Lookup field tab of the field properties pane, which is shown above. Lookup fields are also known as foreign key fields.
How will a lookup field appear in the generated application?
The above screenshot shows the detail view of the products table as generated by AppGini. The detail view is where users can edit records of the table. The "Supplier" and "Category" fields are lookup fields that bring their data from the suppliers and products tables, respectively. This data is represented in a drop down menu for each field.
How to set up a lookup field?
To set a field as a lookup field in AppGini, create a new field and, in its properties pane, go to the "Lookup field" tab, as displayed in the above screenshot. From the "Parent table" drop down, select the table that contains the source data. From the "Parent caption field part 1" drop down, select the source field.
You can optionally specify a second source field to be joined to the first one. For example, you could create a lookup field that lists the full name by joining a "first name" field to a "last name" field, using a space as the separator.
Note: AppGini will change the data type of the lookup field to be the same as that of the primary key of the parent table. This is normal behavior and you shouldn't alter it. If the parent table doesn't have a primary key yet, you should change the data type of the lookup field manually to match the primary key once you create one.
Displaying lookup fields as an options list (radio buttons)
AppGini makes it possible to display the lookup field as an options list (radio buttons list) rather than a drop-down menu, as shown above. To do so, simply check the "Show as radio buttons" option in AppGini, as shown below.
Parent/Children settings
When you configure a field as a lookup field, the parent table you specify for that field can, in turn, be configured to show some special behavior. In AppGini, if you click the parent table, you should see a button labeled Parent/Children settings, like the one to the right.
Clicking that button displays the Parent/Children settings window - as shown below, which allows you to enable displaying child records below the detail view of the parent record.
This window lists all child tables of the current table (that is, tables that contains a lookup field where the parent table is set to the current table). Select a child table from the grid at the left to configure its related behavior in the parent table. An example of parent and child tables is the orders and order_details tables. The orders table is a parent table of order_details. Every order saved in the orders table would have one or more items saved in the order_details table.
Show tab below detail view would display a list of child records below the detail view when you select a parent record. For example, this is how an order looks like in the detail view, where the order items are listed at the bottom.
Copy child records when copying parent would copy child records if the user copies the parent record by clicking the Save As Copy button. The lookup field in the copied child records would be automatically set to the new parent record. This is a very handy feature for scenarios like duplicating an order and all its order items, a product, and all the items in its bill of materials, ... etc. It saves users the time to manually add child records from scratch.
Copying child records requires cURL PHP extension to be installed and enabled on your server.
As of AppGini 5.81, a new configuration parameter, $host, was added in config.php. The value of this parameter is set by default to the host name of your server as automatically detected by PHP. However, if copying of child records is not working (and curl PHP extension is enabled), you might need to check and change this value manually by editing the config file. PHP might not detect the internal host name correctly in cases where servers are behind NAT or load balancers, Docker containers, or similar network configurations.
Display child info in the table view. Starting with AppGini 23.15, the Parent/Children settings dialog includes the option Show count of children in table view. Enabling this option displays the count of child records in the table view. You can also add new child records directly from the table view by enabling the option Add new children from table view. The screenshot below shows how both options would be displayed in the table view of the orders table, showing the count of order items in each order, and allowing users to add new items directly from the table view of orders.
See also the related video tutorial