Engineering

Building an Inventory Management App in Retool using ApyHub Currency API

Using Retool to create an inventory management application with Currency Conversion.
blog thumbnail
SO
Sohail Pathan
Last updated on May 24, 2023
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/)
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. Then, click on the “Create App From template” button.
inventory-app-process.gif
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
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
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
OR
Option 2: Import
Use the CSV file to import the table schema into the database. This file will automatically generate and populate the table with the sample values.
import-csv.gif
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.
1
SELECT * FROM apyproducts;
Click on the Save and Run Button.
products-query-changes.png
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
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
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.
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
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
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.
api-docs.png
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:
Header:
Content-Typeapplication/json
apy-tokenPLEASE-ENTER-YOUR-SECRET-TOKEN-HERE
Body: Choose JSON from the drop-down.
sourceusd
targeteur
api-call.png
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
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
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
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
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
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
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
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 )
KeyValue
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
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
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
Final Step: Run the app by clicking on Run and Clicking on Toggle Preview Mode.
Final-step.png
TA-DAAA 🤩
We have now successfully built an inventory management app using Retool and ApyHub. With Retool's flexibility and ApyHub's powerful API integration capabilities, we can further enhance the platform with additional features and integrations to meet specific business needs. ApyHub 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