Substract a number property from table X when a register is related in table Y

Hello everybody, i’m trying to create a mini ERP, sales management system.

Translated from english to spanish
Invoice=Factura
Products=Producto
Supply=Inventario

  1. I have a Facturas table and also a products table.
  2. The Facturas table has a column related to Products Table, so you can select the product to be sold and it’s also getting the SUM of prices for the invoice’ total.
  3. Also, the products table has a ‘Stock’ number type column.
  4. Moreover, every product has a Column “Inventario” that’s summing up every time the store receives new units of this product.
  5. I want that everytime I create a “Factura” and select the products to be sold, it substract the equivalent units from the supply or the stock property in the Products Table.

Hi @Juan_Rubio ,

I assume there is also a units column in the Facturas table for the number of sold units. The formula for all sold products would then be

Facturas.Filter(Product = thisrow).units.Sum()

You could use this as a column formula in the products table and calculate the current stock as

thisrow.supply - thisrow.sold

If you want the stock column to be edible, you would need to put the first formula into a button or an automation instead.

Hope this helps!

1 Like