Discussions

Expand all | Collapse all

Subtracting units from one Table based on entry into another Table

  • 1.  Subtracting units from one Table based on entry into another Table

    Posted 12 days ago
    Hello,

    I'm trying to create a system where in table Orders when a new order is made however many units of a specific type of item are ordered are then subtracted from the specific item's supply in Inventory.

    I'm fairly new to Quick Base and I've been trying to use the Help and University tools to learn this but for the life of me I'm really struggling. It seems like Quick Base should be able to do this but I can't seem to figure how.

    It seems like I would use Automations and Formulas to do this. However, I'm probably using Formulas incorrectly as when I try and place an order for 100 units, it correctly understands I want to subtract 100 units. However when I try and use Automations to transfer that information to the Inventory table it merely replaces the supply value with -100 when I would instead like it to subtract 100 from our current supply.

    Additionally it's not clear to me how I would specify which item to subtract from using Automations. It seems like I can only automate the one action with no option to do a comparison of the inventory name to make sure the correct field is being updated.

    Here is what I would like to happen simplified:

    Orders table:
    New Order made for 300 units of item ZXY

    Inventory Table:
    Supply for item ZXY is reduced by 300 units automatically when the Order is made. Ideally with a warning of a kind if we go below 0 on accident.

    Any help would be greatly appreciated.

    ------------------------------
    Blake Burns
    ------------------------------


  • 2.  RE: Subtracting units from one Table based on entry into another Table

    Posted 11 days ago
    Assuming you have a relationship between Inventory and Orders (Inventory->Orders) -- my suggestion would be to make a summary field between orders and Inventory, summing the quantity ordered field from orders to your inventory table.

    https://help.quickbase.com/user-assistance/create_summary_field.html

    That way, for each item you'll have an auto-calculating number that shows how much of each item has been ordered. I would then suggest to create a field in your inventory table (or leverage your existing field) to be called "Starting Inventory". Then you could just make a simple numeric formula in Inventory called "On Hand" which would be [starting inventory] - [total quantity ordered].

    ------------------------------
    Charlie Murawski
    ------------------------------



  • 3.  RE: Subtracting units from one Table based on entry into another Table

    Posted 11 days ago
    That worked!

    Thank you, Charlie.

    ------------------------------
    Blake Burns
    ------------------------------