Sum calculated values from formula fields through linked tables

1

I want to have a column that sums costs of goods from linked tables.

I’ve tried Airtable, Nocodb, Baserow, and am almost to the point where I start coding things myself, but want to do a sanity check before I start reinventing the wheel. Summing linked rows seems like it should be a very normal / feasible thing to do, and I suspect that I’m missing something incredibly basic / obvious.

My question: How can I sum values of formula fields from a linked table?

For additional context / clarity on what I want to accomplish, I created SQLite tables / a query:

My tables

Items

Items > Example Instantiation:

Title Materials Used Cost Of Materials
Widget A (2 Materials Used) 8.47
Widget A (1 Materials Used) 10.7

Items > Fields:

Field Context
Title Text
Materials Used Number
Cost Of Materials What I'm looking for; to sum cost from materials used

Materials Inventory

Materials Inventory > Example Instantiation:

Title Cost Measurement Unit Unit Quantity Cost / Unit
Steel 5000 kg 1400 3.57
Plastic 2000 kg 1500 1.33

Materials Inventory > Fields:

Field Context
Title Text
Cost Number
Measurement Unit Select
Unit Quantity Number
Cost / Unit Formula: {Materials Inventory.Cost} / {Materials Inventory.Unit Quantity}

Materials Used

Materials Used > Example Instantiation:

Title Item Material Inventory Item Unit Unit Quantity Used Cost / Unit Material Cost
Steel Usage Widget A (ID: 1) Steel (ID: 1) kg 2 3.57 7.14
Plastic Usage Widget A (ID: 1) Plastic (ID: 2) kg 1 1.33 1.33
Steel Usage Widget A (ID: 1) Steel (ID: 1) kg 3 3.57 10.71

Materials Used > Fields:

Field Context
Title Text
Item Linked Table: {Items}
Material Inventory Item Linked Table: {Materials Inventory}
Unit Lookup: {Materials Inventory.Measurement Unit}
Cost / Unit Lookup: {Materials Inventory.Cost / Unit}
Unit Quantity Used Number
Material Cost Formula: {Materials Used.Unit Quantity Used} * {Materials Inventory.Unit Quantity}

In SQLite I would do something like this:

Create tables

-- Create the Items table
CREATE TABLE Items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
);

-- Create the MaterialsInventory table
CREATE TABLE MaterialsInventory (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    cost REAL NOT NULL,
    unit TEXT NOT NULL,
    unit_value REAL NOT NULL
);

-- Create the MaterialsUsed table
CREATE TABLE MaterialsUsed (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    item_id INTEGER NOT NULL,
    material_inventory_id INTEGER NOT NULL,
    unit TEXT NOT NULL,
    unit_quantity REAL NOT NULL,
    FOREIGN KEY (item_id) REFERENCES Items (id),
    FOREIGN KEY (material_inventory_id) REFERENCES MaterialsInventory (id)
);

Insert Test Data

-- Insert data into Items table
INSERT INTO Items (name) VALUES
('Widget A'),
('Widget B');

-- Insert data into MaterialsInventory table
INSERT INTO MaterialsInventory (name, cost, unit, unit_value) VALUES
('Steel', 5000, 'kg', 10),
('Plastic', 2000, 'kg', 5);

-- Insert data into MaterialsUsed table
INSERT INTO MaterialsUsed (name, item_id, material_inventory_id, unit, unit_quantity) VALUES
('Steel Usage', 1, 1, 'kg', 2),
('Plastic Usage', 1, 2, 'kg', 1),
('Steel Usage', 2, 1, 'kg', 3);

Select statement:

SELECT 
    Items.name AS item_name,
    COUNT(DISTINCT MaterialsUsed.id) AS materials_used,
    SUM(MaterialsUsed.unit_quantity * (MaterialsInventory.cost / MaterialsInventory.unit_value)) AS cost_of_materials
FROM 
    Items
JOIN 
    MaterialsUsed ON Items.id = MaterialsUsed.item_id
JOIN 
    MaterialsInventory ON MaterialsUsed.material_inventory_id = MaterialsInventory.id
GROUP BY 
    Items.id, Items.name;

The above outputs something like

item_name materials_used cost_of_materials
Widget A 2 8.476190476190476
Widget B 1 10.714285714285715
2

Rollup over formula is currently not supported. There is an open feature request here. Please upvote to help prioritise.