Need Inspiration RE: Updating Inventory Values

Need Inspiration RE: Updating Inventory Values

I've created a module for the maintenance department to create workorders.  There is a subform in this module that lists the parts that are used in the process of carrying out the work order.

So we have a MAINTENANCE WORK ORDERS module, and a PARTS module.  The subform uses the PARTS module to list the PART, QTY, COST used in the work order.

When things are added to the Maintenance Work Order via this subform, I want to reduce the inventory amount in the PARTS module by the amount on the subform.  This needs to happen for each part (line item) on the subform.

So far....

I decided to create another module called INVENTORY TRANSACTIONS.  This would take the information from MAINTENANCE WORKORDER subform (transaction type, part, quantity used) and create a transaction record in the new INVENTORY TRANSACTIONS module.

Then, this transaction record would increase or decrease the inventory level in the  PARTS module.  

So now I have two issues:

1.  Creating the record in M INVENTORY TRANSACTION module upon workorder add/edit.

The CREATE RECORD option in workflows doesn't provide the option to update the QTY field in the INVENTORY TRANSACTIONS module from the workorder subform QTY.   

2. Updating the inventory levels in PARTS based on the INVENTORY TRANSACTION

The UPDATE field in the workflows won't do math, so I'm assuming I need some sort of function to update the inventory amount by adding the QTY field in the INVENTORY TRANSACTION module and the inventory quantity in the PARTS module.

Am I overthinking this? And insight, function code or new perspective would be much appreciated.

          Zoho Marketing Automation

                  Zoho Pagesense Resources

                    Zoho SalesIQ Resources

                          Zoho TeamInbox Resources

                            Zoho DataPrep Resources

                              Zoho CRM Plus Resources

                                Zoho Books Resources

                                  Zoho Subscriptions Resources

                                    Zoho Desk Resources

                                      Zoho Projects Resources

                                        Zoho Sprints Resources

                                          Qntrl Resources

                                            Zoho Creator Resources

                                              Zoho WorkDrive Resources

                                                  Zoho Campaigns Resources

                                                    Zoho CRM Resources

                                                          Design. Discuss. Deliver.

                                                          Create visually engaging stories with Zoho Show.

                                                          Get Started Now