So I want to keep track of various mixtures that we use and how much of them we use. Each mixture is composed of various materials that are entered when the mixture is made.
What I want to be able to do is when a usage log is created for the mixture, a corresponding material log is made for each material in that mixture.
Say for Example:
"Paint 1" is 50% "Red" and 50 % "Blue", so it has two child records "Red" and "Blue" related to those colors. Now I enter a log that says I used 2 gallons of "Paint 1". When I make that log, I'd like it to automatically create two logs in the materials log table, that say I used 1 gallon of "Red", 1 gallon of "Blue".
I feel like I should be able to do this with a simple Webhook, but I don't think that I can, because I have to search through the "Materials in mixtures" table to find the ones related to that mixture first, and then create records for those, which I don't think is possible in a Webhoook.
Am I missing something here or do I need to just go ahead and make some sort of script to handle this?
What I want to be able to do is when a usage log is created for the mixture, a corresponding material log is made for each material in that mixture.
Say for Example:
"Paint 1" is 50% "Red" and 50 % "Blue", so it has two child records "Red" and "Blue" related to those colors. Now I enter a log that says I used 2 gallons of "Paint 1". When I make that log, I'd like it to automatically create two logs in the materials log table, that say I used 1 gallon of "Red", 1 gallon of "Blue".
I feel like I should be able to do this with a simple Webhook, but I don't think that I can, because I have to search through the "Materials in mixtures" table to find the ones related to that mixture first, and then create records for those, which I don't think is possible in a Webhoook.
Am I missing something here or do I need to just go ahead and make some sort of script to handle this?
-
302 Points
Posted 2 years ago
Ⲇanom the ultimate (Dan Diebolt), Champion
-
29,604 Points
Sam C
I agree that handling it with a script would be straightforward, I was just wondering if there was a more native way to do it.
Certainly, I can see how it's confusing.
The Mixtures table has a many-to-many relationship with the Materials table through the Components junction table, which keeps track of how much of each Material is in a given Mixture.
So using the previous example, Red and Blue would be Materials, Paint 1 is a mixture, and Paint 1 has Components, Red-50%, Blue-50%.
The Mixture Logs table has a one-to-many relationship with Mixtures. This is a normal log table, where users would just enter the amount of a Mixture they used. In the example, the user enters Paint 1, 2 gallons.
But I also need to keep track of how much of each Material is used, and for that I have the Material Logs table. The Material Logs table has a one-to-many relationship with the Materials table, and a one-to-many relationship with the Mixture Logs table.
So the goal here is to allow users to make Mixtures, and then enter Mixture Logs, and then when a Mixture Log is created, Material Logs are created for each Component in that Mixture, by multiplying [Component - Portion of Mixture] by [Mixture Use - Volume].
In the example, a Mixture Log was created for Paint 1, 2 gallons, so 2 Material Logs are created: Blue, 1 gallon ; Red, 1 gallon.
Tables:
Relationships:
I hope that helps clarify things.