Dynamic Inventory Management for Desks and Device

Hello everyone,

I’m working on setting up dynamic inventory calculations for my office equipment but have run into some challenges.

Here’s my setup:

  • Desks Table: This table contains all the desks in my office.
  • I/O Devices Table: This is a “nomenclature” table that lists all devices with properties like type, model, and quantity (e.g., monitors, keyboards, etc.).

What I Want to Achieve

When I assign I/O devices to desks (e.g., 2 monitors and 1 keyboard to Desk A), I want the I/O Devices Table to automatically calculate and update the remaining quantity for each device. For example:

  • I start with 20 monitors and 20 keyboards in the I/O Devices table.
  • I assign 10 monitors and 5 keyboards across several desks.
  • The I/O Devices table should dynamically show:
    • Monitors: 10 remaining
    • Keyboards: 15 remaining

Current Challenges

  1. Many-to-Many Relationship Issue:
  • I’ve created a many-to-many relationship between desks and devices. However, when I assign devices to a desk and enter a custom quantity, it creates a new row in the I/O Devices table instead of updating the existing row.
  1. Dynamic Quantity Calculation:
  • I’m not sure how to set up the logic to calculate the remaining inventory dynamically based on the assignments.

I’ve read through the documentation and forums but haven’t found a clear solution to these issues. Any guidance or examples on how to set this up would be greatly appreciated!

Thank you in advance!

I can give you some insight into your Challenge #2. Still trying to fully comprehend the relational landscape myself. I find the youtube tutorial videos quite dense and helpful.

To test my theory on how you can get #2 done; I was able to setup a workflow that was triggered on an update to any of my ‘qty assigned’ fields. The workflow queries the table for all non-empty ‘qty assigned’ values, which passes on to a calculation block, which uses math.js and sum(Node result / Query record / qty assigned) which then gets passed to ‘update record’ type block.

Using this workflow I was able to query the table, sum the results from the query, and update another record in a different table. I hope this makes sense - let me know if I can clarify.