--- title: Build Inventory Management App in Retool with ApyHub Currency API url: https://apyhub.com/blog/building-inventory-management-app-in-retool-using-apyhub-currency-api author: Sohail Pathan published: 2023-05-24T00:00:00Z tags: [engineering] --- # Build Inventory Management App in Retool with ApyHub Currency API # Build Inventory Management App in Retool with ApyHub Currency API ## Introduction In this tutorial, we're going to use retool to create our very own inventory application that will make product creation and management a breeze. But that's not all - we'll also add something new - we will show how to display product prices in both USD and EUR using the ApyHub currency conversion API. This way our application will be able to cater to potential customers from all around the globe (in this specific example we are going to focus on the conversion to EUR but the service we are going to use for that also supports other currencies. So, whether your plan is managing your **product information model (PIM)** for your own web store or you just want to get a handle on inventory for any other purpose (such as **internal equipment inventory**), this tutorial has got you covered. The application we are going to create will allow users to: - Create a product inventory: Add, View, and modify products of an inventory. - Display the price of these products in different currencies, in USD and EUR. In order to be able to follow this tutorial without any problem, you will need to have: 1. Basic understanding of JavaScript and SQL 2. A Retool account (sign up at https://retool.com/) 3. An ApyHub account (sign up at [https://apyhub.com](https://apyhub.com/)/) **Step 1**: For existing Retool users: Sign in to retool. If you don't have a retool account yet, you should first create one before continuing. **Step 2**: Create a new application and pick the option to start using a template. Search for the [Inventory Management Template.](https://retool.com/templates/ecommerce-inventory-management) Then, click on the **“Create App From template”** button. [![inventory-app-process.gif](https://i.postimg.cc/Fz8w9bcw/inventory-app-process.gif)](https://postimg.cc/FkbTpLTx) To run the project, click the Toggle View mode button on the right. Upon seeing that the app is running correctly, we can switch to design mode and modify the app accordingly. **Step 3**: Currently, our app allows adding products to an inventory with the unit price in USD. We want to enhance this functionality by enabling the addition of products in two currencies: USD and EUR. Additionally, we aim to display the total sum of items in both currencies. However, before proceeding, we need to establish a database to store these details. Let's begin! Retool offers a built-in database feature that allows us to create a table with the necessary columns. Here’s how can you do that: 1. To create the database in Retool, locate the **Retool logo button** at the top left corner. 2. **Click on the Retool logo button**, and a drop-down menu will appear. 3. From the drop-down menu, select the **Database** option. You will see the sample **users** table. Now, we will create our inventory table by clicking on the **+** button. [![create-table.png](https://i.postimg.cc/BncDcYjQ/create-table.png)](https://postimg.cc/RWhNvT1j) A popup window New Table will appear, allowing you to name the table. Enter the name `apyproducts` and make sure to check the option for including the **created_at** column. [![table-name.png](https://i.postimg.cc/vZkTpCcv/table-name.png)](https://postimg.cc/sMP3W6PB) A table will be created with two columns **id** and **created_at.** That’s nice right?🙂 Let's remember our goal: We want to add functionality to let users add products in inventory along with their quantities in stock, and let them specify the value of the products in a base currency (USD) and get the value in EUR automatically and finally calculate the total value of the products in inventory in both currencies. In order to do that, we will need 7 columns in our table i.e. 1. **Product Name** 2. **Product Quantity** 3. **Base Product Price (USD)** 4. **Product Updated Time** 5. **Product Price ( EUR)** 6. **Total Price (USD), and** 7. **Total Price (EUR)** We have two ways to create these columns. **Option 1: Add the columns manually, one by one.** By clicking on the **+**, A pop-up window to add select the column type. Adding columns to the inventory table is as easy as following these steps. 1. For the *Product Name* Column, Add Field Name as `product_name` and choose Field Type as **Text.** Click **Save**. 2. For the *Product Quantity* Column, Add Field Name as `product_quantity` Field Type as **integer** and Number Type as **Integer.** Click **Save** 3. For the *Base Product Price (USD)* Column, Add Field Name as `product_price_usd` Field Type as **Number**, Number Type as **decimal** and Decimal Places as **2 (1.00).** Click **Save** 4. For the *Product Price (USD)* Column, Add Field Name as `product_price_eur` Field Type as **Number**, Number Type as **decimal** and Decimal Places as **2 (1.00).** Click **Save** 5. For *Total Price ( USD )* Column, Add Field Name as `product_price_usd` Field Type as **Number**, Number Type as **decimal** and Decimal Places as **2 (1.00).** Click **Save** 6. For *Total Price ( EUR )* Column, Add Field Name as `total_price_eur` Field Type as **Number**, Number Type as **decimal** and Decimal Places as **2 (1.00).** Click **Save** 7. For Product Updated Time, Add Field Name as `updated_at` and choose Field Type as **Date + Time.** Click **Save** [![add-column.png](https://i.postimg.cc/zD0cp7R7/add-column.png)](https://postimg.cc/6T4cpdc4) OR **Option 2: Import** Use the [CSV file](https://drive.google.com/file/d/1g8UiqpuzFDOKKZ6-Sg-a1RlczRjwE1CQ/view?usp=share_link) to import the table schema into the database. This file will automatically generate and populate the table with the sample values. [![import-csv.gif](https://i.postimg.cc/pTdSzcJg/import-csv.gif)](https://postimg.cc/3yP9HB6B) **Step 4**: The **`apyproducts`** database has now been successfully created. It’s now time to test the table by referring it to our User Interface. For that, choose the **products** query from the Code section on the left. Change the Resource reference from **onboarding_db** to **Retool Database.** Write one query to display the data of the `apyproducts` in the editor. ```sql SELECT * FROM apyproducts; ``` Click on the **Save and Run** Button. [![products-query-changes.png](https://i.postimg.cc/BZHTNc7y/products-query-changes.png)](https://postimg.cc/23jLSvDF) Once you click on Save and Run, The SQL query triggers in the database and returns the response with the schema that we have just created. [![table-show.png](https://i.postimg.cc/CxLbhyMT/table-show.png)](https://postimg.cc/ZC2WHMyV) We can add the reference to the database from the UI by selecting the Product Table on Design Canvas. On the right, Click Select **Inspect**, and Add the data as `{{apyproducts.data}}` and Click on **Run**. [![table-show-new.png](https://i.postimg.cc/7P96x55h/table-show-new.png)](https://postimg.cc/Yj4kRqfB) **Step 5**: Now, we need to build the currency conversion functionality that converts the currency from USD to EURO. For that, we will use [ApyHub Currency Converter Utility](https://apyhub.com/utility/currency-conversion). In order to do that we will need an API key so we can call an API for authentication and authorisation purposes, ensuring secure and controlled access to the API's functionalities and data. Here is how we can generate an API Key in ApyHub: If you don't have one, create an account on ApyHub: [https://apyhub.com/auth/signup](https://apyhub.com/auth/signup) Now that we have an account, log in to ApyHub. Click on the **API Keys** Button in the left navigation bar → Select the option to **create a new API key →** Assign the name to API Key **Eg. new-retool-inventory-app →** Copy the token that is generated. Done 👍 [![apyhub-account-step.gif](https://i.postimg.cc/wMj9tk1v/apyhub-account-step.gif)](https://postimg.cc/xJhwhMyD) **Step 6**: Now we need to set up a Resource Service in Retool that allows us to connect to an ApyHub currency conversion service. For that, we have a requirement of **URL (Endpoint)**, **Headers** and **Body** which you can find by clicking on this [ApyHub Currency Converter Utility Page](https://apyhub.com/utility/currency-conversion). [![api-docs.png](https://i.postimg.cc/FK5W1n2r/api-docs.png)](https://postimg.cc/FYpgBGr8) You can find the ➕ symbol in the Code Section in the bottom left. Click on it and Select **Resource Query** and choose **RESTQuery (restapi).** **Action Type:** | POST | https://api.apyhub.com/data/convert/currency | | --- | --- | **Header:** | Content-Type | application/json | | --- | --- | | **apy-token** | PLEASE-ENTER-YOUR-SECRET-TOKEN-HERE | **Body**: Choose **JSON** from the drop-down. | source | usd | | --- | --- | | **target** | **eur** | [![api-call.png](https://i.postimg.cc/bJL3CYdr/api-call.png)](https://postimg.cc/r001zk12) Click on **Run** to test the API. Once successfully called, You will able to see *Query Ran Successfully* and below that the response in JSON as `data: 0.92009`. [![api-test.png](https://i.postimg.cc/TYsHvP0W/api-test.png)](https://postimg.cc/gnvKvmfY) If the API runs successfully, assign it a suitable name as we will rely on this name to retrieve the data using the service. To provide a reference, we will refer to this service as `conversionService`**.** *Note: If you encounter any errors, please review the preceding steps and ensure that you have inputted the correct values.* We are now done with creating the database and the conversion service. We will now build the most crucial functionality of this application. While adding the products to the inventory, we need the input value of Product Name, Quantity, and Price in USD only. We have the `conversionService` which provides the latest rate of euro from US dollars hence we just need the formula to calculate the Unit Price in EUR. [![Org-charts.png](https://i.postimg.cc/rwWg6Nzv/Org-charts.png)](https://postimg.cc/NKsRmT64) **Step 7:** Click on the **+Add New Product** form. On the right, there’s an **Inspect** Panel. You will see the notification of the deprecated element and a backlink text ad **Upgrade** By clicking on the same, the UI element will be upgraded to the latest version.**Importantly, You have to upgrade all the existing input fields.** **Step 8**: To calculate the remaining values, we need three additional input fields i.e. Unit Price (EUR), Total Amount (USD) and Total Amount (EUR). First of all, change the label of Unit Price (in cents) to **Unit Price (USD)** and rename the id from *`new_product_price`* to `new_product_price_usd` **Map the below values in the input field:** - Default Value: **0** - Input format: **Currency** - Currency Code: **USD** - Decimal Places: **2** - Label: **Unit Price (USD )** [![new-product-price-usd.png](https://i.postimg.cc/dt00DJCB/new-product-price-usd.png)](https://postimg.cc/WtQVHLXF) **Step 9**: On the right panel, click **Create**. From the Component Directory, drag the **Number Input** field below to the Unit Price (in cents) field and rename the id as `new_product_price_eur` You can follow this GIF for a more visual explanation. [![ui-adjustment.gif](https://i.postimg.cc/fyGg4zQN/ui-adjustment.gif)](https://postimg.cc/D4ccLKzj) Since it’s a currency input field, we need to provide the formula to get the Product Price in EUR and validate to ensure we get the value in decimal format and read-only - because it gets computed automatically. To get the Product Price in EUR, Multiply the product price in USD with a Rate of 1 EUR i.e. *Product Unit Price (EUR) = Product Unit Price ( USD ) x Rate of 1 EUR.* **Map the below values in the input field:** - Default Value: `{{new_product_price_usd.value * conversionService.data.data}}` - Input format: **Currency** - Currency Code: **EUR** - Decimal Places: **2** - Label: **Unit Price (EUR)** - Make the Read-only: **True** ( **Go to** Interaction Section, **Click** on the setting icon button on the right, and write **true** ) [![new-product-price-eur.png](https://i.postimg.cc/NfPjHZ7w/new-product-price-eur.png)](https://postimg.cc/gn8p9STM) **Step 10**: Similarly for **Total Amount (USD)**, on the right panel, click **Create**. From the Component Directory, drag the **Number Input** field below to the Unit Price (in EUR) field and rename the id to `total_price_usd` To get the total Amount in USD, multiply the Product Price in USD by a product quantity i.e. *Total Amount (USD) = Product Unit Price ( USD ) x Quantity* **Map the below values in the input field:** - Default Value: `{{new_product_price_usd.value * new_product_quantity.value}}` - Input format: **Currency** - Currency Code: **USD** - Decimal Places: **2** - Label: **Total Amount (USD)** - Make the Read-only: **True.** ( **Go to** Interaction Section, **Click** on the setting icon button on the right, and write **true** ) [![total-amount-usd.png](https://i.postimg.cc/g0ypN7Rj/total-amount-usd.png)](https://postimg.cc/rdp7pQf2) **Step 11:** Finally for **Total Amount (EUR)**, On the right panel, click **Create**. From the Component Directory, drag the **Number Input** field below to the Unit Price (in EUR) field and give the id as `total_price_eur` To get the Product Price in EUR, multiply the Product Price in USD by the product quantity i.e. *Total Amount(EUR) = Product Unit Price(EUR) x Quantity.* **Map the below values in the input field.** - Default Value: **`{{new_product_price_eur.value * new_product_quantity.value}}`** - Input format: **Currency** - Currency Code: **EUR** - Decimal Places: **2** - Label: **Total Amount (EUR)** - Make the Read-only: **True** ( **Go to** Interaction Section, **Click** on the setting icon button on the right, and write **true** ) [![total-amount-usd.png](https://i.postimg.cc/g0ypN7Rj/total-amount-usd.png)](https://postimg.cc/rdp7pQf2) Yayy!! It looks like our **+Add to Product** form is ready. We will now send the input data to the database via the SQL query. **Step 12:** Now we will add the functionality of saving the new Item details in the database. Here, we will map the id of the input fields in SQL query items. Let’s do it. In the Code Section, we will modify the **add_product** query. Change the following: - Resource: **Retool Database**. - GUI Mode: **Run Query Only when manually Triggered**. - Select Table: **`apyproducts`** - Action Type: **Insert a Record.** - Action Type: **Insert a Record.** - Changeset: **Key-Value Pairs** ( Key is Column Name in `apyproducts` table, Values is the `id` in put fields of **+Add the Product** Form ) | Key | Value | | --- | --- | | `product_name` | `{{new_product_name.value}}` | | `product_quantity` | `{{new_product_quantity.value}}` | | `price_in_eur` | `{{new_product_price_usd.value}}` | | `price_in_usd` | `{{new_product_price_eur.value}}` | | `updated_at` | `{{new Date()}}` | | `created_at` | `{{new Date()}}` | | `total_price_usd` | `{{total_amount_usd.value}}` | | `total_price_eur` | `{{total_amount_eur.value}}` | After carefully mapping the value, click on **Save**. [![add-products.png](https://i.postimg.cc/kg9fWhnf/add-products.png)](https://postimg.cc/Yhn6HRLW) The `add_product` query will now take the value from the input fields and add it to the database whenever it triggers. We will trigger this query when the user clicks on **Add Product** Button **Step 13:** We will go back to **+Add the Product** form. Click on **Add Product** button. Choose Type as **Submit** and Form to Submit as *ID_OF_FORM. In Event Handlers,* Click on the **+** Button and add the Submit handler with the following conditions. - Event: **Submit** - Action: **Control Query** - Query: `add_product(retool_db)` - Method: **Trigger** [![add-products-trigger.png](https://i.postimg.cc/kXGhHSQm/add-products-trigger.png)](https://postimg.cc/gn9DXntS) Finally, we will trigger the `products` query followed by `add_product` which will refresh the inventory database with newly added items and show on the User Interface. [![products-trigger.png](https://i.postimg.cc/28WKQZn8/products-trigger.png)](https://postimg.cc/4mJPZn8r) **Final Step**: Run the app by clicking on **Run** and Clicking on **Toggle Preview Mode**. [![Final-step.png](https://i.postimg.cc/hvW9cfvp/Final-step.png)](https://postimg.cc/1nBgKRpq) **TA-DAAA** 🤩 We have now successfully built an inventory management app using Retool and ApyHub. With Retool's flexibility and [ApyHub's](https://apyhub.com/?utm_source=commudle&utm_medium=advanced-email-validation&utm_id=tutorial) powerful API integration capabilities, we can further enhance the platform with additional features and integrations to meet specific business needs. [ApyHub](https://apyhub.com/?utm_source=commudle&utm_medium=advanced-email-validation&utm_id=tutorial) offers 50+ utilities (from conversions, generation, extraction and other data utilities etc), check them out to find utilities that address your specific use cases. [![ezgif-com-video-to-gif.gif](https://i.postimg.cc/8zThXH1Z/ezgif-com-video-to-gif.gif)](https://postimg.cc/4mF7mpHc)