Low Stock Alert for Producers

Check all the warning at the end of the page before using this workflow.

This workflow will send an email to a producer if any of the variant’s on_hand value drops within a specific range (example considered for the workflow: 1-5). You can set whatever range you want ex. <10, <5, 1-10 etc.

Overview:

This workflow runs periodically (example: every 30 mins). Postgres module is used to get a list of variants with on_hand falling within the specified range. Then the polling function only allows the workflow to move forward if there is a new data (variant) passing through the “Polling Function” node. (Note: during testing it will pass on everything) After this, we get product and producer details from the variant list and email the producer.

Pre-requisite:

OFN Database connection (Postgres) for your instance Gmail account linked in n8n

Workflow:

How to use it:

Trigger: On a schedule (every 30 mins for this case). Modify the schedule as per your need. WARNING: DO NOT SET THE WORKFLOW SCHEDULE FOR <10 MINS, AS IT MAY LEAD TO MEMORY ISSUES.

Set Parameters for SQL query (SET node): In this node you can customise the low stock alert by setting up the filter for low stock (ex. on_hand <5, <10, 1-5, <13 etc.) Lower Limit: On_hand will be greater than or equal to the lower limit

Upper Limit: On_hand will be smaller than or equal to the upper limit

Enterprise_id: OFN Enterprise id for the producer

Query to get low stock (Postgres module): Runs the SQL query to get the list of variants falling under the specified range set in the SET node. (NO CHANGE NEEDED IN THIS MODULE)

Polling Function (Code node): Only allows new data to pass through (Note: during testing it will pass on everything). To work properly polling is set to return minimal data. In this case, it only passes on the variant id. Therefore, another SQL query is run after this to get product and producer details for the email. (NO CHANGE NEEDED IN THIS NODE)

Producer and Product details (Postgres module): Gets the product and producer details from the variant id passed on by the polling function. (NO CHANGE NEEDED IN THIS MODULE)

Gmail: Sends an email to the producer with name of the product with low stock. From: Select the email account you want to send the email from (it needs to be connected in n8n) To: Producer email (can map the data from pervious module or just add it directly) - USE YOUR EMAIL FOR TESTING Subject: Email subject (can map data from previous nodes)Message: Email body (can map data from pervious nodes)

WARNINGS:

  • It will only run once when a new variant enters the range, i.e. if a variant x’s stock level drops in the specified range. It won’t run again for variant X if its stock level stays in the range.

  • Run each module first before running the whole workflow for testing.

  • For testing, use your email address in the To field and switch on the workflow.

  • When you switch on the workflow, it might run for all the existing variants with stock level in the range. After this initial run, it will only run for new variants entering the filter range.

  • This workflow will run for newly created variants if the on_hand/stock value is within the filtering range

  • It will send 1 email per variant which comes into the filtering range. This workflow can be modified to send 1 email per workflow run if there are any variants with low stock.

  • Email address from the “Producer and Product details” module is the public email address of the producer.

  • This workflow is designed to work for 1 producer. You can copy the workflow and change the settings in the SET and GMAIL node to run it for other producers.

  • BUT IF YOU WANT TO RUN IT FOR MULTIPLE DIFFERENT PRODUCERS THEN CHECK OUT THE MODIFIED WORKFLOWS LISTED BELOW.

Derived Workflows:

  1. Text message instead of email to producers

  2. (workflow listed below) Running this workflow for multiple producers at once (using a live list in airtable - i.e. you can have a list of producers who want this in Airtable and n8n will take in the enterprise ids directly from there. To add more producers to the workflow, just simply add them in airtable) - Filtering range will be same for all of them.

Last updated