Inventory Reorder Workflow

This laboratory focuses on developing a reorder workflow initiated by a Retool Inventory Management Multipage app.

Steps

The following steps focus on Retool Workflows, AI and a database (e.g., Retool Database or PostgreSQL) and require that the previous Retool Inventory Management Multipage lab is completed including database entries.

Create a Retool account and login to your account as shown below.

Create and deploy base workflow

To help automate the reordering process, a Retool Workflow will be created and integrated with the Inventory Management Multipage app.

Create a Workflow by selecting the Retool icon to get back to the Home screen.
Select Workflow > Create workflow. The workflow will be created as shown in the figure.

Select the Workflow name and edit to ReorderWF.
Delete the Code Block.
Select the green circle on the right-hand side of the startTrigger block.
Select Resource query block.
Specify the resource (e.g., Retool Database / PostgreSQL)
Change the block name to getVendors. Specify a SQL JOIN that queries both the inventory and vendor_inventory tables using the query:

SELECT 
    v.vendor_name,
    i.sku,
    i.description,
    v.available_quantity,
    v.vendor_contact,
    v.latitude,
    v.longitude
FROM 
    inventory i
    LEFT JOIN vendor_inventory v ON i.id = v.sku
WHERE 
    i.sku = {{startTrigger.data.sku}};

Deploy the workflow by selecting Deploy > Deploy Workflow.

Create Resource Query (Workflow) in Retool App

The next step is to create a connection between the Inventory Management Multipage app and the Reorder-WF workflow. Retool apps can invoke a Retool Workflow via a Resource Query along with input parameters.

Next, connect the inventory-mgmt-app with the Reorder Workflow. Make sure you are in the inventory page.
Create a new query by selecting Code > + > Resource query and name it initiateReorderWF.
- Resource = Retool Workflow
- Select Workflow = Reorder-WF
- Workflow Parameters (JSON)
  - sku = {{ inventoryTable.selectedRow.sku}}
  - quantity = {{Math.abs(inventoryTable.selectedRow.quantity - inventoryTable.selectedRow.replenish + 100)}}
  - latitude = {{inventoryTable.selectedRow.latitude}}
  - longitude = {{inventoryTable.selectedRow.longitude}}

The following demonstrates the steps in action:

Create new Place Order column in inventoryTable

From a user perspective, we want to provide a simple way to trigger a ordering of replacement inventory. The following creates a new column with a visible link to take action.

Select inventoryTable component. Add a new column by selecting Content > Columns > + with the following values:
- Id = placeOrder
- Label = Place Order
- Format = Link
- Value = {{(currentSourceRow.quantity-currentSourceRow.replenish) < 0 ? "Reorder":""}}
Create an event handler for clicking in the cell by selecting Event handler > + with the following values:
- Action = Control query
- Query = initiateReorderWF
- Method = Trigger

The following demonstrates the steps in action:

Use AI Action to auto-select vendor

To demonstrate Retool AI, we can leverage the AI Action block to examine the vendors that match the SKU and identify the best option for replenishment.

Select getVendors and the SQL query using the SKU will be displayed. It should show 2 or more records.

Add an AI Action that examines the content returned from getVendors and determines which vendor will provide the best option for replenishment of the SKU.
Change the blockname to identifyVendor.

Select Blocks > + > AI Action and drag and drop after the Resource query block, getVendors.

Action = Generate text
Input = (Copy the text that follows)

We need to replenish the sku, {{ startTrigger.data.sku }} with {{ startTrigger.data.quantity }} items to our warehouse located at a latitude of {{ startTrigger.data.latitude }} and longitude of {{ startTrigger.data.longitude }}. 
The vendors in consideration are provided in the following dataset including their name, contact info and latitude and longitude:

<dataset>
{{ getVendors.data }}
</dataset>

System message = (Copy the text that follows)

You are an expert in determining the best vendor based on proximity to the inventory warehouses and the vendor distribution centers as well as inventory that meets the quantity requested based on vendor inventory quantity. 
You will provide a JSON response with a single vendor that best meets proximity and available quantity.

Temperature = 0.2 (This is to reduce randomness in response, more random is 1.0)

Deploy the current workflow by selecting Deploy > Deploy workflow.
Rerun the test from the Show Inventory app and examine the new Workflow logs / JSON (See following example).
The following is an example response that provides a step by step explanation along with a JSON result.

To determine the most suitable vendor for replenishing the SKU EE-000-699123 with 98 items, we will follow these steps:

1. **Identify Vendors with Sufficient Inventory**: We first check which vendors have at least 98 items available for the SKU EE-000-699123.

2. **Calculate Distance to Each Vendor**: We will calculate the distance from the warehouse location (latitude 43.651, longitude -79.347) to each vendor's location using the Haversine formula, which calculates the distance between two points on the Earth's surface given their latitude and longitude.

3. **Select the Closest Vendor**: Among the vendors that have sufficient inventory, we will select the one with the shortest distance to the warehouse.

4. **Prepare the JSON Response**: Finally, we will format the selected vendor's information into a JSON response.

### Step-by-Step Analysis

1. **Available Inventory Check**:
   - All vendors have 1000 items available, which is more than the required 98 items.

2. **Distance Calculation**:
   We will use the Haversine formula to calculate the distance. The formula is as follows:

   \[
   a = \sin^2\left(\frac{\Delta \text{lat}}{2}\right) + \cos(\text{lat}_1) \cdot \cos(\text{lat}_2) \cdot \sin^2\left(\frac{\Delta \text{long}}{2}\right)
   \]
   \[
   c = 2 \cdot \text{atan2}\left(\sqrt{a}, \sqrt{1-a}\right)
   \]
   \[
   d = R \cdot c
   \]

   Where:
   - \( R \) is the Earth's radius (mean radius = 6,371 km)
   - \( \Delta \text{lat} = \text{lat}_2 - \text{lat}_1 \)
   - \( \Delta \text{long} = \text{long}_2 - \text{long}_1 \)

   Let's calculate the distances for each vendor:

   - **Bearing 1000**:
     - Latitude: 42.3879, Longitude: -72.1031
     - Distance: 
       - \( \Delta \text{lat} = 42.3879 - 43.651 = -1.2631 \)
       - \( \Delta \text{long} = -72.1031 + 79.347 = 7.2439 \)
       - Using the Haversine formula, we find the distance to be approximately 137.5 km.

   - **Industrial Bearings**:
     - Latitude: 32.7357, Longitude: -97.1081
     - Distance:
       - \( \Delta \text{lat} = 32.7357 - 43.651 = -10.9153 \)
       - \( \Delta \text{long} = -97.1081 + 79.347 = -17.7611 \)
       - The distance is approximately 1400 km.

   - **Precision Bearings**:
     - Latitude: 40.4406, Longitude: -79.9959
     - Distance:
       - \( \Delta \text{lat} = 40.4406 - 43.651 = -3.2104 \)
       - \( \Delta \text{long} = -79.9959 + 79.347 = -0.6489 \)
       - The distance is approximately 3.5 km.

3. **Select the Closest Vendor**:
   - Bearing 1000: 137.5 km
   - Industrial Bearings: 1400 km
   - Precision Bearings: 3.5 km

   The closest vendor is **Precision Bearings** with a distance of approximately 3.5 km.

4. **Prepare the JSON Response**:
   We will format the information of the selected vendor into JSON.

``json
{
  "vendor_name": "Precision Bearings",
  "vendor_contact": "your_email_here@xyz.com",
  "quantity": 1000,
  "latitude": 40.4406,
  "longitude": -79.9959
}
``

This JSON response contains the details of the most suitable vendor for replenishing the SKU EE-000-699123.

Parse AI Action results

Following the AI Action, it is necessary to parse the result, retrieve the vendor contact and send an email with request for a purchase order providing the SKU and Quantity.

Drag and drop a Code block, connect to the AI Action and rename extractVendorDetails.
Specify the following code:

let text = identifyVendor.data;
console.log(text);
return text.match(/([a-zA-Z0-9._-]+@[a-zA-Z0-9._-]+\.[a-zA-Z0-9_-]+)/gi);

Notify vendor with reorder request

Drag and drop a Resource query block, connect to the Code block and rename emailVendor.
Specify Resource type = Retool Email
- To = {{extractVendorDetails.data[0]}}
- Subject = Inventory Order for SKU: {{startTrigger.data.sku}}
- Body = Please provide a PO for: SKU: {{startTrigger.data.sku}} and Quantity: {{startTrigger.data.quantity}}

Drag and drop a Webhook Response block, connect to emailVendor and rename triggerResponse.
Deploy the current workflow by selecting Deploy > Deploy workflow. Test the workflow by selecting the Reorder link in the Show Inventory app and confirm the email is sent to the email listed. Examine the Workflow logs to see how data is sent and processed in each block.

Completed examples

You can import these completed examples to compare but it will require that you configure the database table inventory and inventory_vendor:

Workflow: Reorder-WF.json

Steps​

Create a Retool account and login​

Create and deploy base workflow​

Create Resource Query (Workflow) in Retool App​

Create new Place Order column in inventoryTable​

Use AI Action to auto-select vendor​

Parse AI Action results​

Notify vendor with reorder request​

Completed examples​