Build a mobile inventory management app

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

A common use case for Retool Mobile is inventory management, enabling workers to look up product information in any warehouse, wherever they are. This tutorial explains how to build an inventory management mobile app for users to:

• Browse, filter, and view details of products across all warehouses.
• Browse and view details of warehouses, and look up available products at the location.
• Quickly look up product information by scanning a barcode.

Prerequisites

This tutorial demonstrates a real-world use case of Retool Mobile and uses sample data from a PostgreSQL database. Retool Cloud organizations include Retool Database, which you can use to upload and manage sample data.

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

Table	Description	Download CSV
inventory_management_items	Inventory products with brand information.	Download
inventory_management_warehouses	Warehouse information.	Download
inventory_management_warehouseitems	Warehouse inventory information.	Download

Import the CSVs to Retool Database using the provided names. 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

Sign into Retool and click Create > Mobile app. Set the name to Inventory Management.

View and filter products

This part of the tutorial explains how to build functionality to display a list of products and enable workers to filter by search term, brand, and warehouse location.

Create a mobile app

Sign into Retool and click Create > Mobile app. Set the name to Inventory Management.

Configure the Products 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.

The primary screen for this mobile app is used to display a list of products. Select Screen 1(screen1) in the Screens section of the mobile panel (left) to display its settings in the 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 Products.
• 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 Products.

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 product and warehouse data

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

This mobile app uses three queries to retrieve data about inventory, warehouses, and brands. Click + New in the bottom panel to create the first query, then select the Retool Database resource and use the following SQL statement:

getAllItems

select id, title from inventory_management_items;

Save the query as getAllItems.

Repeat the process to create queries for getWarehouse and getBrands.

getWarehouses

getWarehouses

select * from inventory_management_warehouses;

getBrands

getBrands

select distinct brand from inventory_management_items order by 1 desc;

Assemble components for the Products screen

You add mobile components to each screen of a mobile app to build the interface by clicking + in the Components section of the left panel. You then configure component properties using the Inspector.

Add the following mobile components to the Products screen, then configure their respective names and settings:

• Text Input
  • Name: Products_Searchinput
  • Placeholder: Search by name...
  • Label: Search
• List Collection
  • Name: Products_ProductCollection
• Fab
  • Name: Products_Fab
  • Text: Find by Barcode
• Scanner
  • Name: Products_Scanner
  • Hidden: true

Configure filter options

The app will allow workers to filter products with a search term, and select a brand and warehouse using a dedicated screen.

First, add a screen. Set the Title to Filters and its name to filters. This screen won't be accessed using the tab bar so toggle Add to tab bar off.

Add the following mobile components to the Filters screen, then configure their respective names and settings:

• Text Input
  • Name: Filters_ProductSearchInput
  • Placeholder: Search by name...
  • Default value: {{ Products_SearchInput.value }} (automatically populates the value based on any search already entered in the Products screen)
  • Label: Search
• Select
  • Name: Filters_BrandSelect
  • Default value: {{ getBrands.data.brand }}
  • Label: {{ self.values }}
• Select
  • Name: Filters_WarehouseSelect
  • Default value: {{ [null, ...getWarehouses.data.id] }} (Set an empty value as the first option, followed by a list of warehouse IDs)
  • Label: {{ [null, ...getWarehouses.data.street] }} (Display an empty label for the first option, followed by a list of warehouse street addresses that correspond to the ID values)
• Button
  • Name: Filters_ClearButton
  • Text: Clear filters

This button uses event handlers to reset the filters to their default values. Add an event handler to the button that resets Filters_ProductSearchInput to its default value:

• Action: Control component
• Component: Filters_ProductSearchInput
• Method: Reset value

Repeat this to add event handlers for Filters_BrandSelect and Filters_WarehouseSelect.

Workers will access these filters from an action button at the top of the Products screen. Select the Products screen, then add a Right action button from the Actions section of the Inspector:

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

Return to the Filters screen and add an action button that navigates back to the Products screen.

Retrieve inventory data

The app now has the necessary screens, components, and queries in place to display and filter products. The next step is to connect everything together with a query.

This query creates an initial dataset that combines product, warehouse, and brand data. It then returns a list of products that takes into account any specified filters. In can also return individual products with an id that matches a scanned barcode.

note

Although this query is more complex, it's more efficient as it reduces the number of queries to a data source.

getItems

-- sub-query to select the data to use
with "itemsWithWH" as (
  select
    items.id,
    items.title,
    items."reviewsCount",
    items."thumbnailImage",
    items."price/value",
    items."price/currency",
    items."brand",
    items."url",
    items."returnPolicy",
    items."features/0",
    items."features/1",
    items."features/2",
    items."features/3",
 array_agg(distinct wh."warehouse_id") as warehouses
  inventory_management_items as items
  join "inventory_management_warehouseitems" as wh
  on wh."itemId" = items.id
  group by 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 , 11, 12
)
select * from "itemsWithWH"
where
  title ilike {{ '%' + (Products_SearchInput.value || Filters_ProductSearchInput.value) + '%' }}
  and ({{ !Filters_BrandSelect.value }}
    or brand = {{ Filters_BrandSelect.value }})
  and ({{ !Filters_WarehouseSelect.value }}
    or {{ Filters_WarehouseSelect.value || 0 }} = any(warehouses))
  and ({{ !Products_Scanner.data }} or id = {{ Products_Scanner.data ? Products_Scanner.data[0] : 0 }})
order by "reviews_count" desc

This query can automatically run whenever a worker navigates to the Products screen. Add an event handler to the screen from the Screen Events section of the Inspector and configure with the following settings:

• Event: Visible
• Action: Trigger query
• Query: getItems
• Method: Trigger
• Only run when: {{ !getItems.data.id.length }}

The Only run when condition only allows the action to trigger if has a truthy value is set. !getItems.data.id.length evaluates as true if there is no query data already available.

Display inventory data

Select the Products_ProductCollection mobile component and set the Data source to getItems. The component 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.

Settings	Value
Title	{{ item.name }}
Body	{{ item.brand }} {{ item['price/currency'] }}{{item['price/value']}}
Caption	{{item.sales}}
Source	{{ item.thumbnailImage }}

With this step complete, workers can now browse a list of products and filter results based on search term, brand, and warehouse location.

View selected product details

When a product is selected in the list, its data is available at the List Collection component's selectedItem. The following screen uses the selected products details and displays them to the user.

Create the Product details screen

First, add a screen and rename it productDetail. This screen uses multiple components to display different pieces of information about the product.

Assemble the components to display product information

Add the following components and configure them with the specified values.

• Image
  • Name: productDetail_Image
  • Source: {{ Products_ProductCollection.selectedItem.thumbnailImage }}
• Heading
  • Name: productDetail_Heading
  • Source: {{ Products_ProductCollection.selectedItem.title }}
• Web View
  • Name: productDetail_WebView
  • Source: {{ Products_ProductCollection.selectedItem.url }}
• Key Value
  • Name: productDetail_Details
  • Source: {{ Products_ProductCollection.selectedItem }}

Display product metadata

The Key Value component displays most of the product information. Not all information is required and some data should be transformed to make it more presentable. Configure the following rows Key Value component:

Key name	Row title	Mapped value
price/currency	Price	{{ getItemDetail.data["price/currency"] }}{{ item }}
brand	Brand	{{ item }}
returnPolicy	Return policy	{{ item }}
warehouse	Warehouses	{{ item }}

All other rows should be hidden. Toggle Show row to off or click the visibility toggle icon.

Display product description

Finally, add a Text component named productDetail_Details to display the product description. The description is comprised of multiple values that combines the features into a single description. Set this component's value to:

productDetail_Details.value

{
  {
    Products_ProductCollection.selectedItem["features/0"];
  }
}
{
  {
    Products_ProductCollection.selectedItem["features/1"];
  }
}
{
  {
    Products_ProductCollection.selectedItem["features/2"];
  }
}
{
  {
    Products_ProductCollection.selectedItem["features/3"];
  }
}

The Product Details screen can now display the product image and name. It includes a button to open the product URL, displays key-value metadata, and a description.

Display selected product details

Return to the Products screen, select Products_ProductCollection and add an event handler to navigate to the selected products:

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

View warehouses

The final part of the tutorial explains how to display a list of warehouses and select one to view more details. It uses the same principles previously covered.

Configure the warehouses screen

Add a warehouses screen with a Title of Warehouses. Similar to the Products screen, this screen will contain a list of warehouses from which to select.

Assemble the warehouse list interface

Add a Card Collection mobile component to the Warehouses screen named Warehouses_Collection. Set the Data source to be the getWarehouses query, then configure Mapped options to display warehouse location data in the list:

Setting	Value	Description
Title	{{ item.street }}	The street address.
Body	{{ item.district }}, {{ item.state }}	The city and state.
Source	{{ item.thumbnailImage }}	A thumbnail image.

Configure the warehouse details screen

Add a warehouseDetails screen with a Title of Warehouse details. This screen will present information about the selected warehouse.

Assemble the warehouse details interface

The components in this screen use data from Warehouses_Collection.selectedItem to display information about the selected warehouse. Add the following mobile components and configure them to make use of the relevant data from the selected item.

• Image
  • Name: WarehouseDetail_Image
  • Value: {{ Warehouses_Collection.selectedItem.thumbnailImage }}
• Heading
  • Name: WarehouseDetail_LocationHeading
  • Text: {{ Warehouses_Collection.selectedItem.street }}, {{ Warehouses_Collection.selectedItem.state }}
• Heading
  • Name: WarehouseDetail_AssetsHeader
  • Text: Assets
• Text Input
  • Name: WarehouseDetail_ProductSearchInput
• Card Collection
  • Name: WarehouseDetail_ProductCollection.

The List Collection mobile component can display a list of products at the selected warehouse. This requires a query that joins item and warehouse data together, then returns products only at the selected warehouse.

getWarehouseItems

select
  inventory_management_items.id,
  inventory_management_items.title,
  inventory_management_items."reviewsCount",
  inventory_management_items."thumbnailImage",
  inventory_management_items."savedPrice",
  inventory_management_items."brand",
  wh.quantity
from inventory_management_items
join "inventory_management_warehouseitems" as wh
on wh."itemId" = inventory_management_items.id
where
  wh."warehouseId" = {{ Warehouses_Collection.selectedItem.id }}
  and title ilike {{ '%' + WarehouseDetail_ProductSearchInput.value + '%' }}
order by "quantity" desc

Wrap up

You have now built a inventory management mobile app that enables workers to browse, filter, and view details of products and warehouses.

By applying the lessons learned here and following the same patterns, you could extend the mobile app's functionality with features like editing or adding products and warehouses.