Create Logs of Child Records with a Webhook?

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • In Progress
  • (Edited)
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?
Photo of Sam C

Sam C

  • 222 Points 100 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
My advice might differ from that of others but this will be straightforward to do with script. In my opinion WebHooks are better suited to integrate QuickBase to another external service rather than connecting a trigger and action within QuickBase. But in any case there is confusion concerning how many tables you have and what the existing relationships and fields are in those tables.

I gather that you have a Mixtures table and a Components table in a one to many relationship. I think you also have a Log table of some sort but I have no idea what fields are in this table or how it is related to the Mixtures and Components table. 

Can you explicitly describe your existing tables, relationships and fields?
Photo of Sam C

Sam C

  • 222 Points 100 badge 2x thumb
Hi Dan, 

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 MaterialsPaint 1 is a mixture, and Paint 1 has ComponentsRed-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:
  • Mixtures (name: Paint 1)
  • Components: (related mixture: Paint 1, related material: Red, fraction of mixture: 50%)
  • Materials (name: Red)
  • Mixture Logs: (related mixture: Paint 1, volume used: 2 gallons)
  • Material Logs: (related mixture log: 2 gallons of Paint 1, related material: Red, volume used: 2 gallons * 50% = 1 gallon of Red used)

Relationships: 
  • Mixtures -< Components >- Materials
  • Mixtures -< Mixture Logs
  • Mixture Logs -< Material Logs
  • Materials -< Material Logs

I hope that helps clarify things. 
(Edited)