Tutorial 5: Connect data models

Button Widget Intermediate App Maker skills

access_time About 30 minutes

What you’ll build

You’ll build an HR app that lists employees by department. An app user can:

  • Add, edit, sort, and delete department and employee records
  • View employee records in two ways:

    • By department only
    • By department or view all

This app doesn't include a way for the user to move between pages. You'll use the page selector in Preview mode to move between pages.

What you’ll learn

This tutorial teaches you how to:

  • Create relations between data models. In this tutorial, you create a one-to-many relation between a Departments model and a Employees model. One department can have many employees.
  • Display records that satisfy the relation, such as records for all employees in the HR department.

Create your app

  1. Sign in to the G Suite account provided by your employer or school.
  2. Open App Maker.
  3. Create a blank app and name it Relations.

Set up the Departments data model

  1. Add a Cloud SQL data model and name it Departments.
  2. Add the following fields:

    Field nameField type
    DepartmentString
    LocationString

  3. Click the Department field and click Set as display field.

    When you set Deparment as a display field, App Maker uses the field values to reference a record in that model. For example, when you add a Dropdown widget later in this tutorial, the values of the Department field automatically populate the dropdown options (with the binding expression @datasource.items).

Set up the Employees data model

  1. Add a Cloud SQL data model and name it Employees.
  2. Add the following fields:

    Field nameField type
    FirstNameString
    LastNameString
    EmailString
    StartDateDate

    You don't need to set a display field for the Employees model.

Add a relation

  1. Click the Departments model and click the Relations tab.

  2. Click Add Relation:

    1. For the Departments model relation end, click the Count down arrow and select One. Leave the default name for this relation end. Enter "Department" for the name of this relation end.
    2. For the other model, click the Model down arrow and select Employees.
    3. For Employees model, relation end click the Count down arrow and select Many. Enter "Employees" for the name of this relation end.
    4. Click Create.

Create a page where you can add departments

  1. Rename the NewPage page to EditDepartments. The top-level panel of the page is selected.
  2. In the Property Editor, click the datasource property and select Departments.
  3. Add an insert Form to the page. Keep default options for the form.
  4. Close the Widget palette (click Close close).

Create a page where you can add employees

  1. Add a new page. Name it EditEmployees, set the datasource to Employees, and click Create.
  2. Add an insert Form to the page. Keep default options for the form.
  3. Close the Widget palette.

Create a page where you can list employees by department

You can use relations to tabulate employees by department in two ways. This tutorial shows you both approaches:

  • Specific departments only (demonstrated in this section)—Use the relation as a datasource. The table that uses this datasource doesn't support pagination or column-based sorting.
  • Specific departments; when no department is selected, all employees (demonstrated in the next section)—Use a query filter on the relation. The table that uses the query filter supports pagination. You can also sort the table by columns.

In a real app, pick one approach. We recommend the second approach because it's paginated, sortable, and only empty when a department has no employees.

To create a page that lists employees only by department:

  1. Add a page, name it RelationDatasource, set the datasource to Departments, and click Create.
  2. Add a drop-down list to let app users select the department:

    1. Click Widgets widgets and add a Dropdown widget to the RelationDatasource page.
    2. Click the options property and select More options chevron_right datasource chevron_right items. Click OK. This sets the possible values of the dropdown to the display field of the datasource, in this case the values in the Department field.
    3. Click the value property and select More options chevron_right datasource chevron_right item. Click OK. This sets the widget's value to the item that the user selects.
    4. Click the label property and enter Select a department.
  3. Add a table to display employee records:

    1. Add a Table Table Widget widget to the page below the Dropdown widget. Align the left sides of the table and the dropdown widget.
    2. Select the Department: Employee (relation) datasource and click Next. With this datasource, the table displays any employees that match the value of the dropdown widget.
    3. Keep default options for the table. Click Next and Finish.
  4. Close the Widget palette.

Create a page where you can list employees by department or list all employees

  1. Add a page, name the page QueryFilter, set the datasource to Employees, and click Create.
  2. Add a drop-down list to let users select the department:

    1. Click Widgets widgets and add a Dropdown to the QueryFilter page.
    2. For the value property, select the binding expression More options chevron_right datasource chevron_right query chevron_right filters chevron_right Department chevron_right _equals. Click OK.
    3. For the options property, select a binding expression from the Application panel on the left: datasources chevron_right Department chevron_right items. Click OK.
    4. Click the label property and enter Select a department.
    5. Open the Events section of the property editor, click onValueEdit and select Reload datasource to reload the datasource and rerun the query when the value of the drop-down list is changed by the app user.
  3. Add a table to display employee records that match the department selection:

    1. Add a Table Table Widget to the page below the dropdown. Align the left sides of the table and the dropdown widget.
    2. The datasource Inherited: Employees is selected. Keep default options for the table; click Nextchevron_rightNextchevron_rightFinish.
  4. Close the Widget palette.

Preview and test your app

  1. In the top-right corner, click Preview to run your app in a new browser tab.
  2. Select your account and click Allow to allow your app to use specific information.
  3. Enter department data:
    1. From the page selector, select EditDepartments.
    2. On the Edit Department form, enter the name and location of several departments. Click Create to create each new record.
  4. Enter employee data:
    1. From the page selector, select EditEmployees.
    2. On the Edit Employee form, enter information for several employees. Click Create to create each new record. Select different departments for the employees.
  5. View employees by department on the page that uses a relation datasource for the table:

    1. From the page selector, select RelationDatasource.
    2. Select a department from the drop-down list. The table contains employee records for that department. When no department is selected, the table is empty.
  6. View employees by department on the page that uses a query filter to load data for the table:

    1. From the page selector, select QueryFilter.
    2. When no department is selected, the table contains all employee records. Select a department from the dropdown. Now the table contains employee records for that department.

Congratulations! You built an App Maker app that uses relations.

Next steps

  1. Learn more about relations.
  2. Study the Relations sample app. It's similar to the app that you just created.
  3. Learn about query datasources and filters.