I am building an inventory and work order management system and have hit a roadblock.
I have a number of objects:
BAGS - this represents a unit of inventory
TRQ - (Transport Request) this represents a work order
STATUSLOC - this represents a status/location. (I could have used a simple drop-down text field for this, but since I will be using it all over my application in many different objects, it seemed easier to use an object with just two fields: Index (for display order) and Description (for the text).)
The BAGS have a connection to the STATUSLOC object which represents the current location of each BAG. The TRQ have two connections to the STATUSLOC object representing the origin of the transport and the destination of the transport.
The goal is to allow users to create TRQs (work orders) and attach them to BAGS (or vice versa). Then, when the work is complete, the user will change a field in the TRQ from "Pending" to "Complete" and fields in the connected BAGS record(s) will update accordingly. (Specifically, the "Location" STATUSLOC field in the BAGS record needs to be set to the "Destination" STATUSLOC field in the TRQ record.) Finally, when the "Active" field in the TRQ is changed to FALSE (the work order is "dead"), the TRQ needs to detach from the BAG. (Essentially, the TRQ connection field in the BAG needs to be set to null.)
I have already implemented the first step of allow users to create TRQs and attach them to BAGS. (Currently, the connection resides in BAGS and is many-bags-to-one-TRQ.) But that's where I run into two problems:
1) First, is there any way to use this connection from within the TRQ record? In other words, make it a 2-way connection, wherein the BAG record has access to the connected TRQ but the TRQ records also have access to the BAGS? (I can of course create a new field in TRQ that connects to BAG object, but this creates a second, entirely detached connection.)
2) More importantly, I cannot seem to figure out how to get the fields in BAGS to change in response to a change in the attached TRQ record. I've tried it many different ways, and in every case there seems to be some limitation that prevents the data from transferring. It should be relatively simple: When a "Status" field in TRQ is set to "Complete", the connected BAGS record replaces the value of its own "Location" field (which is a STATUSLOC connection) with the value of the TRQ "Destination" field (which is also a STATUSLOC connection).
Can anyone offer advice on how to accomplish this?