Skip to main content

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/arsinousltd-sudo/Arsinous-V8-Sales/llms.txt

Use this file to discover all available pages before exploring further.

The Inventory sheet gives you a consolidated, per-product view of everything in your warehouse — combining goods-receipt lots from MySQL with open purchase order quantities into a single refreshable grid. Because each invoice deducts stock directly from the irs_items table at the lot level, the Inventory sheet always reflects your actual on-hand quantities the moment you refresh it. You can sort by ID or name, and filter out products with no stock to focus on what matters right now.

​
Data Sources

The Inventory sheet draws from two MySQL tables:
irs_items is the goods receipt table — each row represents a batch (lot) of a product received into stock. pos_items is the purchase order items table — each row represents a quantity of a product on an open purchase order. Both tables must be populated in MySQL; Arsinous V8 Sales reads them but does not provide a UI for creating goods receipts or purchase orders directly.
TableKey fieldsRole in Inventory
irs_itemsproduct_id, lot, exp_date, in_stock, cost, final_costProvides on-hand stock per lot
pos_itemsproduct_id, quantityProvides ordered (on PO) quantity
productsid, name, catProvides product name and category

​
The Inventory Sheet Layout

After a refresh, the sheet has two header rows followed by one data row per product.
ColumnFieldDescription
A✔Checkbox (data validation applied to A3:A)
BidProduct ID
CnameProduct name
DcatCategory
EquantityTotal in-stock quantity across all lots
Fordered (PO)Total quantity on open purchase orders
Gexpiry datesAll lots formatted as "<lot> <exp_date> (<in_stock>)", newline-separated
HcostCost values per lot, newline-separated
Ifinal_costFinal cost values per lot, newline-separated
Row 1 is a title row with "Inventory" in column A, "updated:" label in column F, and the refresh timestamp in column G (written as ["Inventory","","","","","updated:", new Date(),"",""]). Row 2 is the column header row. Data rows start at row 3, which is also where checkbox validation (A3:A) is applied.

​
Refreshing the Inventory

Trigger a refresh in any of these ways:
  • Arsinous menu → Update Current Tab while the Inventory sheet is active
  • Sidebar → Refresh Inventory button
  • Programmatically by calling updateInventory() — this is also called automatically after every invoice save or delete

​
How updateInventory Works

// Excerpt from Updates.gs
function updateInventory(hideEmpty, sortCol) {
  if (hideEmpty === undefined) {
    var props = JSON.parse(readProperties());
    hideEmpty = props.hideEmpty || false;
    sortCol   = props.orderCol  || 2;
  }

  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Inventory");
  sh.clearContents();

  var products = JSON.parse(getAllFromDB('products'));
  var irs      = JSON.parse(getAllFromDB('irs_items', ' WHERE in_stock > 0 '));
  var pos      = JSON.parse(getAllFromDB('pos_items',  ' WHERE quantity > 0 '));

  products = products.map(function(p) {
    var lot = irs.filter(function(l) { return l.product_id == p.id; });
    if (lot.length > 0) { p.lots = lot.slice(); }
    var po = pos.filter(function(l) { return l.product_id == p.id; });
    if (po.length > 0) { p.pos = po.slice(); }
    return p;
  });

  if (hideEmpty === true || hideEmpty === "true") {
    products = products.filter(function(p) { return p.lots || p.pos; });
  }

  var output = products.map(function(p) {
    var q = 0, expiryDates = [], costs = [], finalCosts = [];
    if (p.lots) {
      p.lots.forEach(function(item) {
        if (!isNaN(item.in_stock)) {
          q += Number(item.in_stock);
          expiryDates.push(
            (!!item.lot ? item.lot + " " : "") +
            (item.exp_date || " - ") +
            "(" + item.in_stock + ")"
          );
          costs.push(item.cost);
          finalCosts.push(item.final_cost);
        }
      });
    }
    var ordered = 0;
    if (p.pos) {
      p.pos.forEach(function(item) {
        if (!isNaN(item.quantity)) { ordered += Number(item.quantity); }
      });
    }
    return [false, p.id, p.name, p.cat, q, ordered,
            expiryDates.join("\n"), costs.join("\n"), finalCosts.join("\n")];
  });

  // Sort by sortCol, then prepend two header rows and write
  output.sort(function(a, b) {
    if (!isNaN(a[sortCol])) a[sortCol] = Number(a[sortCol]);
    if (!isNaN(b[sortCol])) b[sortCol] = Number(b[sortCol]);
    if (a[sortCol] > b[sortCol]) { return 1; }
    if (a[sortCol] < b[sortCol]) { return -1; }
    return 0;
  });
  output.unshift(["✔", "id", "name", "cat", "quanity", "ordered (PO)", "expiry dates", "cost", "final_cost"]);
  output.unshift(["Inventory", "", "", "", "", "updated:", new Date(), "", ""]);
  sh.getRange(1, 1, output.length, output[0].length).setValues(output);

  // Add checkbox validation to data rows (A3 onward)
  var rule = SpreadsheetApp.newDataValidation().requireCheckbox().build();
  sh.getRange("A3:A").setDataValidation(rule);

  return JSON.stringify({ message: "Updated." });
}

​
Parameters

​
hideEmpty
boolean
default:"false"
When true, products with no in-stock lots and no open purchase orders are excluded from the output. The preference is stored in user properties via readProperties() / updateProperty() and persists between sessions.
​
sortCol
number
default:"2"
Column index (0-based within the output array) to sort by. Use 1 to sort by product id, 2 to sort by name. Stored in user properties as orderCol.
Enable Hide Empty to declutter the Inventory sheet during picking or stocktaking — you’ll see only products that currently have stock on hand or are on order. Disable it when you need a full product catalog view including items currently out of stock.

​
Expiry Date Display Format

When a product has multiple lots in stock, the expiry dates column shows one line per lot, formatted as: 
LOT_NUMBER EXP_DATE (QTY_ON_HAND)
For example, a product with two lots might display: 
A001 2025-06-30 (120)
A002 2025-12-31 (48)
If a lot has no lot number recorded, the lot number prefix is omitted.

​
Return Value

updateInventory returns JSON.stringify({ message: "Updated." }) after writing the sheet. This lets callers (such as the sidebar) confirm that the operation completed successfully.

​
Editing a Product from Inventory

You don’t need to switch to the Products sheet to edit a product’s catalog details. Select any row in the Inventory sheet and choose Arsinous menu → Edit Product. The editProduct() function works from both the Products and Inventory sheets — it reads the active sheet’s header row to build the key map and opens the same 600 × 600 px product edit modal.

​
User Preferences Storage

The hideEmpty and sortCol settings are persisted in Google Apps Script user properties so they survive sheet refreshes and browser sessions. They are read at the start of every updateInventory() call when no explicit arguments are passed, and can be updated through the sidebar controls. 
// Conceptual flow
var props = JSON.parse(readProperties());
hideEmpty = props.hideEmpty || false;
sortCol   = props.orderCol  || 2;

Build docs developers (and LLMs) love

Get started for freeTalk to us