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
- 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.
- 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!