Build a site inspection app on Retool Mobile

🎉

beta

Retool Mobile is currently in public beta. Sign up to get started →

Retool Mobile enables you to build native mobile apps that interact with your data and deploy them to your workforce.

Use case

A common use case for Retool Mobile is to manage site inspections for different properties. For example, property management workers for apartment buildings can complete inspections about the state of each apartment in different buildings, log maintenance work, take pictures, and look up previous inspections. This tutorial explains how to build a site inspection mobile app for users to:

• Browse, filter, and view building and unit information.
• View inspection history for all buildings and units.
• Create new inspections, enter required information, and save the results to a database.

Prerequisites

This tutorial demonstrates a real-world use case of Retool Mobile and uses sample data from a PostgreSQL database. Retool cloud organizations include a basic PostgreSQL database named managed_db that you can interact with for testing purposes.

Before you begin, download the provided sample data in CSV format. Each CSV file corresponds to a database table.

Use the Data Editor to create the required tables, using the provided names, and then upload each CSV. Once complete, you can use the same queries and resource used by this tutorial.

Much of what this tutorial covers can apply to other resources and data types. You can also use your own SQL database that's connected to Retool.

Get started

👍

You can download a JSON export of this mobile app and import it into Retool.

Once you're ready to begin, sign into Retool and click Create > Mobile app. Set the name to Site Inspection.

View buildings

The first part of the tutorial explains how to look up and display a list of apartment buildings.

Configure the Buildings screen

Retool Mobile organizes mobile components into separate screens and creates pathways for your users to follow. Users navigate between screens using the tab bar or by interactions, such as pressing a button or selecting an item from a list.

New Retool Mobile apps initially contain two screens. Select Screen 1 in the Screens section of the mobile panel (left) to display its settings in the Inspector (right panel).

• The screen's name (screen1) is used internally when you configure settings or write queries. Click on screen1 in the Inspector to edit this and set it to Buildings.
• The screen’s title is displayed in at the top of your app and in the tab bar at the bottom, if visible. In the Inspector, set the value for Title to Buildings.

You can also set the icon to use in the tab bar at the bottom of the app. Click the screen in the Tab section of the Inspector and select an icon.

Retrieve building data

Buildings data for this app comes from a PostgreSQL database. You write queries to interact with connected data sources using SQL.

Click + New in the bottom panel to create a query, select the managed_db resource, then write the following query to retrieve all records from the buildings table:

select * from site_inspection_buildings;

Assemble components for the Buildings screen

You build the interface for each screen by assembling components. Queries connect to components so users can access or manipulate data. Click + in the Components section of the left panel to display the component browser and add them to the current screen. You then configure component properties using the Inspector.

Add a Card Collection mobile component to the Buildings screen, set its name to Buildings_Collection, and set the Data Source to getAllBuildings.

Card Collection displays a each item from an array of objects using a card layout. It automatically configures list options by dynamically mapping values from the query data. Use the Mapped options settings to configure what values to use by referencing item. For instance, setting Title to {{ item.name }} displays each item name as the list option's title.

Once configured, Card Collection displays the building information returned by the getAllBuildings query.

View inspection history

This part of the tutorial explains how to look up and display all previous inspections.

Configure the History screen

Select screen2 and change its name to Inspections, set Title of Inspections, then change the tab bar label to History. As before, you can also specify an icon.

Retrieve all inspection data

Each inspection record is associated with a unit. To fetch all inspections with unit and building data, you can write a query that uses INNER JOIN clauses to combine data from the site_inspection_buildings, site_inspection_inspections, and site_inspection_units tables. This allows the query to retrieve not only inspection data but also information about the unit and building.

SELECT
  site_inspection_inspections.id as i_id,
  site_inspection_inspections.visit_type,
  site_inspection_inspections.image_url,
  site_inspection_inspections.created_at,
  site_inspection_units.number as apt_num,
  site_inspection_buildings.name as building_name
FROM
  site_inspection_inspections
  INNER JOIN site_inspection_units ON site_inspection_inspections.unit_id = site_inspection_units.id
  INNER JOIN site_inspection_buildings ON site_inspection_units.building_id = site_inspection_buildings.id
ORDER BY
  created_at DESC;

Assemble components for the History screen

Add a List Collection mobile component to the History screen, set its name to History_Listview, and set Data Source to getAllInspections. As before, use the Mapped options settings to configure what values to use by referencing item.

The List Collection component works the same as Card Collection, but displays each item in a list view layout.

The History screen now displays a scrollable list of all inspections.

Update the query to use server side pagination

The getAllInspections query is considered expensive because there are no limits in how much data is returned. The more data to query, the more it can affect performance—especially when a query joins multiple tables. It's good practice to restrict queries so that they only retrieve the minimum amount of data needed, and to be mindful of how often queries run.

One method is to use server side pagination. When configured, a query can use an offset to retrieve only a certain number of records needed at any one time—not all of them. As the offset increases, the query reruns and it continues to fetch only the records needed.

You can use List Collection's selectedPageIndex property as the offset so this query only fetches a certain number of inspection records. Select the List Collection and toggle Enable server side pagination on, then update the getAllInspections query to include an offset that returns only 20 records each time:

SELECT
  site_inspection_inspections.id as i_id,
  site_inspection_inspections.visit_type,
  site_inspection_inspections.image_url,
  site_inspection_inspections.created_at,
  site_inspection_units.number as apt_num,
  site_inspection_buildings.name as building_name
FROM
  site_inspection_inspections
  INNER JOIN site_inspection_units ON site_inspection_inspections.unit_id = site_inspection_units.id
  INNER JOIN site_inspection_buildings ON site_inspection_units.building_id = site_inspection_buildings.id
ORDER BY
  created_at DESC
LIMIT
  20 OFFSET {{ History_ListView.selectedPageIndex * 20 }};

As you scroll through the list, the offset increases and automatically triggers the query to return more results.

View apartment units

This part of the tutorial explains how to select a building and view its apartment units.

Configure the Units screen

Add a Units screen with a Title of Units. This screen will only be accessed when a user selects a building and is not included in the tab bar.

Add an event handler for building selection

Event handlers perform actions, such as navigating to a different screen, in response to certain events. In this case, selecting a building from the list should navigate to the Units screen.

Return to the Buildings screen, select the Buildings_Collection component, and add an event handler using the Interaction section of the Inspector.

• Event: Press
• Action: Navigation
• Method: Navigate to screen
• Screen: Units

Data about the selected building is available in the List Collection component's selectedItem property which can be referenced elsewhere in the app.

Retrieve unit data for the selected building

Unit information is available in the site_inspection_units table. Each record includes a building_id field that maps to the building in which the unit is located. As you can access the selected building's ID with selectedItem, write a query to look up all units based on the selected unit.

SELECT
  *
FROM
  site_inspection_units
WHERE
  building_id = {{ Buildings_Collection.selectedItem.id }}
ORDER BY
  CAST(number AS INT) ASC;

Assemble components for the Units screen

The Units screen displays the name of the selected building and a list of all units it contains. Each unit includes the apartment number, rental status, a preview image, and a button to begin inspection.

First, add the following mobile components to the Units screen, then configure their respective settings:

• Heading
  • Name: Units_Heading
  • Text: at {{ Buildings_Collection.selectedItem.name }}
• Card Collection
  • Name: Units_Collection
  • Data source: getUnitsInBuilding

Next, use the Mapped options settings for the Card Collection to configure mapped values by referencing item.

View inspection history for the selected unit

This part of the tutorial explains how to display the inspection history of a selected apartment unit.

Configure the UnitDetail screen

Add a UnitDetail screen. Similar to the Units screen, UnitDetails will only be accessed when a user selects a unit and is not included in the tab bar. It also does not require a title.

Add an event handler for unit selection

Return to the Units screen, select the Units_Collection component, and add an event handler using the Interaction section of the Inspector.

• Event: Press
• Action: Navigation
• Method: Navigate to screen
• Screen: UnitDetails

Retrieve inspections for the selected unit

Each unit needs to include details of previous inspections. Using the selectedItem property on Units_Collection, write a query to retrieve all inspections that match the selected unit's ID.

SELECT
  *
FROM
  site_inspection_inspections
WHERE
  unit_id = {{ Units_Collection.selectedItem.id }}
ORDER BY
  created_at DESC;

Display descriptive reasons using temporary state

Every inspection includes a visit_type field with three possible values:

To make these reasons more descriptive, you can use temporary state to map each value to the description provided.

Click Create new in the Temporary State section of the left panel to create a temporary state with the following JavaScript dictionary as its initial value:

{
  tenant_request: "Tenant Requested Inspection",
  maintenance: "Maintenance Inspection",
  routine: "Routine Inspection"
}

Temporary state is globally accessible and reasonStrings will be referenced in the next step.

Assemble components for the UnitDetail screen

The UnitDetail screen combines information from both the selected unit and the building it's located in (i.e., the selected building).

Add the following mobile components to the UnitDetail screen, then configure their respective settings:

• Heading
  • Name: UnitDetail_Heading
  • Value: Unit {{ Units_Collection.selectedItem.number }} at {{ Buildings_Collection.selectedItem.name }}
• Image
  • Name: UnitDetail_Image
  • Text: {{ Units_Collection.selectedItem.img_url }}
• Spacer
  • Name: UnitDetail_Spacer
• Text
  • Name: UnitDetail_Text
  • Value: Previous Inspections
• Card Collection
  • Name: UnitDetails_Collection
  • Data source: getInspectionsForUnit

Use the Mapped options settings for the Card Collection to configure mapped values by referencing item.

The title references the temporary state reasonStrings to include the more descriptive reason.

Finally, add a Fab component to the screen:

• Name: UnitDetail_Fab
• Text: Add Inspection

Create new inspection

The final part of the tutorial explains how to create a new inspection for the selected apartment unit and add it to the site_inspection_inspections table.

Configure the NewInspection screen

Add a NewInspection screen. Similar to the UnitDetails screen, this will only be accessed when a user starts a new inspection and won't be included in the tab bar.

Add an event handler to create a new inspection

Users will add new inspections by pressing the Add Inspection Fab component on the UnitDetails screen.

Return to the UnitDetails screen, select the UnitDetail_Fab component, and add an event handler using the Interaction section of the Inspector.

• Action: Navigation
• Method: Navigate to screen
• Screen: NewInspection

Assemble components for the NewInspection screen

This screen makes use of numerous components so users can input information for an inspection. Container components are also used to group other components together and control their nested layout.

Add components and configure their respective settings based on the component tree structure below. Components nested within Containers are indented.

NewInspection screen
│
├── Text
│   │ Name: NewInspection_Subtitle
|   | Value: New Inspection At
│
├── Heading
│   │ Name: NewInspection_Header
│   │ Value: Unit {{ Units_Collection.selectedItem.number }} at {{ Buildings_Collection.selectedItem.name }}
│
├── Divider
│   │ Name: NewInspection_Divider
│
└── Container
    │ Name: NewInspection_Container1
    │ Direction: Column (down arrow)
    │
    ├── Select
    │   │ Name: NewInspection_TypeSelect
    │   │ Default value: routine
    │   │ Values: ['routine', 'maintenance', 'tenant_request']
    │   │ Labels: ['Routine Inspection', 'Maintenance', 'Tenant Request']
    │   │ Label: Inspection type
    │
    ├── Container
    │   │ Name: NewInspection_Container2
    │   │ Direction: Row (right arrow)
    │   │
    │   ├── Checkbox
    │   │   │ Name: NewInspection_LivingCheckbox
    │   │   │ Values: ["windows", "door", "fixtures"]
    │   │   │ Labels: ["Windows", "Door", "Fixtures"]
    │   │   │ Label: Living Room
    │   │
    │   └── Checkbox
    │       │ Name: NewInspection_KitchenCheckbox
    │       │ Values: ['stove', 'oven', 'alarm']
    │       │ Labels: ['Stove', 'Oven', 'Alarm']
    │       │ Label: Kitchen
    │
    ├── Select
    │   │ Name: NewInspection_NotesTextInput
    │   │ Placeholder: Describe the state of the unit
    │   │ Label: Notes
    │
    ├── Image
    │   │ Name: NewInspection_image
    │   │ Source: {{ NewInspection_Camera.value[0] }}
    │   │ Hidden: {{ NewInspection_Camera.files.length === 0 }}
    │
    └── Container
        │ Name: NewInspection_Container3
        │ Direction: Column (down arrow)
        │
        ├── Camera
        │   │ Name: NewInspection_Camera
        │   │ Quality: 0.5
        │   │ Text: Add Photo
        │
        └── Button
            │ Name: NewInspection_SubmitButton
            │ Text: Submit

Transform checkbox values

Users completing a new inspection can check each living room or kitchen item as inspected. Only the values for selected checkboxes are included in the component's value property.

Each inspection needs to include whether an item was inspected or not, but only selected values are available. You can transform these values so they reflect a Boolean state.

Transformers allow you to manipulate data using JavaScript. In this case, two transformers (one for living room items, another for kitchen items) returns all possible checkbox values as key-value pairs. Keys corresponds to checkbox values, while values correspond to true or false.

To create a transformer, click + New in the bottom panel and select JavaScript transformer. Add two transformers that contain the following JavaScript:

// Retrieve all possible checkbox values
const possible = {{ NewInspection_LivingCheckbox.values }};
// Retrieve all selected checkbox values
const checked = {{ NewInspection_LivingCheckbox.value }}

const map = {};
// Map all possible checkbox values as `false`
possible.forEach(x => map[x] = false);
// Map all selected checkbox values as `true`
checked.forEach(x => map[x] = true);

return map;

// Retrieve all possible checkbox values
const possible = {{ NewInspection_KitchenCheckbox.values }};
// Retrieve all selected checkbox values
const checked = {{ NewInspection_KitchenCheckbox.value }}

const map = {};
// Map all possible checkbox values as `false`
possible.forEach(x => map[x] = false);
// Map all selected checkbox values as `true`
checked.forEach(x => map[x] = true);

return map;

When run, each transformer returns a list of all possible checkbox values and their selected state:

{
  "windows": true,
  "door": false,
  "fixtures": true
}

{
  "stove": false,
  "oven": false,
  "alarm": true
}

Capture photos and upload to Amazon S3

📘

Refer to our uploading photos to Amazon S3 tutorial to learn about the additional steps required to configure access control.

You can use the Image Picker component to take photos using the device's camera, which is returned as a value. In this screen, NewInspection_image references {{ NewInspection_Camera.value[0] }} to use the photo as the image source. Similarly, if no photo has been taken yet, the component is hidden as {{ NewInspection_Camera.files.length === 0 }} evaluates to false.

Next, create a query to upload the photo to Amazon S3. Click + New in the bottom panel to create a new query named uploadPhoto, then select your S3 resource. Set the Action type to Upload data and configure the following settings:

Add an event handler to the Camera component that triggers whenever a new photo is captured. When this occurs, the query uploads the photo to S3 automatically and returns a URL of the image for you to use.

Save the new inspection

The final step is to write a query that stores the new inspection as a new record on the database.

Click + New in the bottom panel to create a new query named createInspection, then select GUI mode. This mode uses an interface that makes it easier and safer to write queries that manipulate data. Select the inspection table and set the Action type to Insert record.

Update the Changeset to configure the query with each field name and the input value to use.

This query needs to run when the Submit button is pressed. Select the NewInspection_Submit button and add an event handler to trigger the query:

• Action: Trigger query
• Query: createInspection

To prevent duplicate inspections, you can disable the button and display a loading state while the query runs. Set the value of Disabled and Loading for this button to {{ createInspection.isFetching }}. The isFetching query property is a Boolean value that is true when the specified query is running. Once it's finished, the value changes to false.

Wrap up

You have now built a site inspection mobile app that enables workers to browse buildings and units, view inspection history, and create inspections.

By applying the lessons learned here and following the same patterns, you could extend the mobile app's functionality with features like uploading multiple photos for new inspections.