Consumables/products system

Overview

Manages products used by cleaners and clients, including ordering, fulfilment, pricing agreements, and invoicing. Orders are placed via Fillout form, processed by offshore admin, and fulfilled via ABCO or MCC delivery.

Actors & Systems

Actor/System	Role
Cleaners	Order products via Fillout form when stock is low
Offshore admin team	Process orders, monitor pricing, schedule MCC delivery field tasks
Sales manager	Manage consumable opportunities
Ryan	Performs fortnightly hygiene service replenishment
Fillout	Product order form
Airtable	Product list, orders, line items, consumable agreements, opportunities
ABCO	Primary supplier and usual delivery provider
Xero	Invoice generation & sending

Key Tables

Table	Purpose
Product list	Master list of all products (consumables, dispensers, cleaner equipment)
Product orders	Orders submitted via Fillout
Product order line items	Individual items within each order
Consumables	Client agreements for specific products and pricing
Consumable opportunities	Upsell tracking for products not yet agreed
Services	Hygiene services linked to sites

Introduction

We have a bunch of products that our cleaners and clients use.

There are three categories of products:

• Consumables — Products that are used by our clients (e.g. bin liners, soap refills, paper towels and toilet paper)
• Dispensers — The dispensers that hold consumables like soap
• Cleaner equipment — Products that are used by our cleaners (e.g. gloves, chemicals and mops)

This information is stored in the 'Product list' table.

Consumables

Consumables are fulfilled in one of two ways, depending on the type of product and the agreement we have with the client:

• Ad-hoc — Cleaners notice a product is low/missing while on site, and they order more of it through our order form (e.g. toilet paper, bin liners)
• Hygiene service — Some products are replenished by Ryan, every fortnight (e.g. hand soap refills, aerosol air freshener refills)

Ad-hoc consumables

Day-to-day, our cleaners often notice a product is low/missing while on site (e.g. toilet paper, bin liners). When this happens, they order more of that product through our order form.

What consumables clients have agreed to pay for

We provide consumable products to many of our clients. The consumables they've agreed to pay for (or declined) are recorded in their site and organisation record, along with any that we're currently trying to upsell to them.

If a client contacts us to request that we supply a consumable for them, a member of our admin team can click the 'Add consumable' button to add the new consumable to the org or site record.

The above information is stored in the 'Consumables' table, and linked to from the client's record in the 'Sites' table and the 'Organisations' table.

Product orders from cleaners

We make consumables and cleaner equipment available for cleaners to order on a site by site basis, via an online form, built in Fillout.

Once this order form is submitted, the order details are pushed to the 'Product orders' and 'Product order line items' tables in Airtable. This is done with an integration in Fillout.

Cleaners can also order products for their personal kit (i.e. not for a specific site). These are indicated by {Site or kit} = "My kit" on the order. See the "Kit orders" section below for how these are handled differently.

Client consumable orders

When an order is submitted and it has a line item with specific field values, the 'Copy agreed product price from agreement to NEW order' automation is triggered.

This automation checks if any of the ordered products are covered under the client's agreement, and if they are, it copies the agreed price from the agreement to the order. If the client hasn't agreed to that particular consumable yet, no value is copied.

Processing orders

The offshore team monitors the 'Product orders' list. When a new order appears, they click on it to view the details of the order.

The 'Send?' column of the individual line items on the order tells the admin staff whether it's likely the item is OK to send. This is determined by the {Send?} field in the 'Product order line items' table.

The colours to the left of the line items provide a visual cue to support that suggestion. These are managed by the 'Color by' setting for that field in the interface.

The 'Send to' column tells the admin staff where it's likely they'll need to send the item. This is determined by the {Send to} field in the 'Product order line items' table.

The offshore admin staff observe the suggestions and colour, check the quantity of the item that has been ordered, the name of the person ordering, and often/usually will click on the line item for more details. The details view shows reasons for the suggestions, along with a bunch of other helpful information.

There are two 'reasons' type fields:

• 'Reason' — This explains the rationale behind the suggestion about whether it's likely the item is OK to send. This reason is generated using the {Send reason} formula field in the 'Product order line items' table.
• 'Why send here?' — This explains the rationale behind the suggestion about where to send the item. This reason is generated using the {Send to reason} formula field in the 'Product order line items' table.

Once the admin staff member knows what to do with an item, they change its status. They don't directly use the "Delivered by MCC" or "Item returned" statuses. Those statuses are applied automatically where appropriate (see below).

Notes:

• Currently one of our clients has access to the product order form, and if they happen to order cleaner equipment, we don't send it.
• We don't send anything with an outrageously high quantity, even if it was a cleaner who ordered it (not without investigating first, anyway).

Ordering via ABCO to site

The most common scenario is that the admin staff member will need to apply the "Ordered to site via ABCO" status to all items on the order (because most products that are ordered are OK to send to site, and most of them are sent by ABCO). As such, I've provided them with a button that allows them to apply this status to all line items at once.

This invokes the 'Set all order items to 'Ordered to site via ABCO'' automation, which changes the status automatically. This automation is nested in the 'Products' section.

Ordering via ABCO to the cleaner's home

Sometimes the cleaner is ordering cleaner equipment that's not for a specific site but, rather, for their kit to use at all their sites.

Kit orders

When a cleaner orders products for their personal kit ({Site or kit} = "My kit") rather than for a specific client site, the order has no linked site. This means:

• The {Send?} formula returns "Should be OK to send" — Because kit orders are not attributable to a client, the consumable agreement checks (agreed/declined/in negotiation) are irrelevant. The formula bypasses these checks early and recommends sending the item.
• Kit orders cannot generate consumable opportunities — The 'Create consumable opportunity' view in the Product orders table is filtered by Site, so kit orders (which have no site) will never enter that view. This is correct behaviour, since there is no client to upsell to.
• Kit order line items are not invoiced to clients — Since there is no client site, these items are treated similarly to cleaner equipment for invoicing purposes.

Delivery by MCC

If the admin staff member changes the status of a line item on a product order to "Requires delivery by MCC", nothing happens immediately. We obviously need to organise delivery of that item, but we don't want to do that immediately, because the admin staff member may change the status of other items to "Requires delivery by MCC" too, and if they do, we'll want to send all of these together.

So the system waits until it's likely all products that require delivery by MCC have been marked as such. It waits for 30 minutes, because we figured any additional products would have been flagged by then. We have a field in the 'Product orders' table named {Items for MCC to deliver}, which rolls up all the values of the {Product that requires MCC delivery} for all product order line items linked to the order. I.e. It's a list of all the products we need to deliver.

Then another formula field in the 'Product orders' table, named {Ready for MCC delivery}, calculates when the above list of items is ready for delivery. The formula checks if there are items for us to deliver and if 30 minutes has elapsed since the last time one of the items on the order was changed to status "Requires delivery by MCC", it changes to "TRUE". (The logic here is that we assume the user will change, in quick succession, the status of any items that require MCC delivery. It's very unlikely they'll change one after changing another more than 30 minutes ago.)

When the {Ready for MCC delivery} field becomes "TRUE", the 'Organise MCC delivery of consumables product' script is triggered. (This is part of the '15-min automation 2' automation.) This automation creates a new delivery field task with the names and quantities of all items that need to be sent. These details come from the {Items for MCC to deliver} field, which rolls up all the products from the current order that require MCC delivery. The field task also includes the site and organisation, etc.

The automation then creates a new field task that tells Jess to schedule the above field task.

When the delivery driver completes the delivery, they mark the field task complete. This triggers the "Mark order line items as delivered when delivery field task completed" automation, which changes the status of each item in the order from "Requires delivery by MCC" to "Delivered by MCC". This automation is nested in the 'Products' section.

Opportunity

If the admin staff member changes the status of a line item on a product order to "Opportunity", the order enters the 'Create consumable opportunity' view in the 'Product orders' table, and the 'Create consumable opportunities from product orders' script eventually runs on it.

Similarly, if one of our supervisors identifies a consumables opportunity when conducting a quality inspection, they add it to their inspection report, and it enters the 'Create consumable opportunity' view in the 'Product orders' table, and the 'Create consumable opportunities from product orders' script eventually runs on it.

This script runs every 15 minutes (it's part of the '15-min automation 2' automation.) It creates a new consumables opportunity, and adds a new consumable line item to that opportunity (if one doesn't already exist).

Important: The 'Create consumable opportunity' view is filtered by Site, so only orders linked to a specific site will enter this view. Kit orders (which have no site) are excluded by design — see "Kit orders" above.

Our sales manager monitors the list of consumable opportunities. When a new opportunity is added to the list, he clicks to open its details.

He then communicates with the client in question to determine if the client wants each of the products listed in the opportunity and, if so, how much they're prepared to pay. Usually this involves some price matching. We'll get the client to send evidence of the price they're currently paying, and we'll match it.

If the client agrees to a product and a price, our sales manager changes the status to "Agreed to" and types the agreed price. This invokes the 'Copy agreed product price from agreement to EXISTING orders' automation (nested in the 'Products' section), which copies the new agreed price to any existing product order line items that are currently open for that client.

Then, whenever an order comes in in future, the system will cross-check against the consumable record as described earlier in this document.

Note that our users can change the client's agreed price, at any time, from the site or org record too. And if they do, it will invoke the same automation, and update any open orders for that client and product.

Order status

The status of a product order is determined automatically, based on the statuses of the individual items in the order. This is done by the {Status} formula field in the 'Product orders' table.

The formula checks {Line item statuses} for "Please select" or "Requires delivery by MCC". If either is found, the order remains "Open". Otherwise it is "Closed".

Updating RRP

Because some of our clients have opted to pay the recommended retail price (RRP), we need a reliable way to push any RRP changes straight through to their agreed pricing.

This is done with an automation script that runs every 15 minutes (the script is 'Push updated RRP to clients who agreed to pay RRP' and it's part of the '15-min automation 2' automation).

When a product's RRP is updated, the script looks up every product record in the 'Push updated RRP to clients who agreed to pay RRP' view of the 'Product list' table, retrieves its SalesUnitPrice, and identifies all linked consumable records via the Consumable agreements link. It then checks each consumable's Use RRP checkbox; only if that box is ticked does it overwrite the consumable's unit-price field with the new RRP.

Invoicing clients for consumables

At 10am on the 1st of each month, the 'Invoice for last month's consumables' automation runs. This automation is nested in the 'Products' section.

This automation generates draft Xero invoices for the previous month's consumable orders. It runs on all records in the 'Generate invoices' view, in the 'Product order line items' table.

This view lists all uninvoiced product order line items. The script groups the order line items by Xero contact ID, and creates one invoice in Xero per contact. Each invoice is dated the last day of the previous month (Sydney timezone), with a reference like "November 2025 Ad-Hoc Consumables". If a line item has no agreed price, it falls back to the item's default price in Xero. After each invoice is successfully created, it ticks the {Invoiced} checkbox on the corresponding Airtable records.

Returned items

If a client or cleaner asks us to take back a delivered item, we can mark that item as "Returned" in the order details. This will remove it from the invoice list.

Hygiene service

Some products are replenished by Ryan, every fortnight (e.g. hand soap refills, aerosol air freshener refills).

If a client wants us to perform this service for them, we add it directly to their site record by clicking the 'Add service' button in the 'Services' section of the site details page.

The user then adds details about the dispensers, and any products that will be replenished as part of the service.

The service is then added to the 'Services' table (in 'Onboarding' status), and once all other conditions are satisfied, a repeating invoice will be automatically created, and the first 4 field tasks for the service will be automatically rolled out. This is done by the following two automations:

• "Glenn's 15-minute onboarding automation" > "Trigger zap that creates repeating periodic service invoice in Xero"
• "Create first 4 field tasks for periodic service"

Note: We can also add a hygiene service to an opportunity, but at the moment, that's not the workflow we're using. If we do it this way, the associated automations for creating a contract and a repeating invoice all work the same as for any other periodic service (e.g. a power scrub or window clean), as does the automation that rolls out the first 4 field tasks for the service.

Cleaner equipment

Because the users of cleaner equipment are our cleaners, clients are not charged for these products.

They're ordered and delivered using the same logic and functionality as consumables, they're just not invoiced.

Key Automations

Automation	Trigger	Action
Copy agreed product price from agreement to NEW order	Order submitted with line items	Copies agreed prices from consumable agreements to order
Set all order items to 'Ordered to site via ABCO'	Button click	Bulk status update for all line items
Organise MCC delivery	{Ready for MCC delivery} = TRUE	Creates delivery field task + scheduling task for Jess
Mark order line items as delivered when delivery field task completed	Delivery field task completed	Updates line item statuses to "Delivered by MCC"
Add consumable opportunity	Line item status → "Opportunity"	Creates/updates consumable opportunity for org
Copy agreed product price from agreement to EXISTING orders	Consumable agreement updated	Updates prices on open orders
RRP update script	Daily at 3am	Pushes RRP changes to clients who agreed to pay RRP
Create consumables invoices on the first of the month	1st of month at 3am	Creates draft Xero invoices for previous month's orders
Glenn's 15-minute onboarding automation	15-min schedule	Triggers repeating invoice creation for hygiene services
Create first 4 field tasks for periodic service	Service onboarding complete	Rolls out initial field tasks

Related Assets

• Fillout form: Product order form
• Airtable views: "Push updated RRP to clients who agreed to pay RRP", "Previous-Month Orders for Next Invoice"
• Xero integration: Invoice creation via Zapier/automation scripts

Version History

Version	Date	Changes
1.0	Dec 2025	Initial documentation
1.1	Feb 2026	Added "Kit orders" section. Added note to "Opportunity" section about site filter excluding kit orders. Added detail to "Order status" section. Updated "Product orders from cleaners" section to reference kit orders.