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 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:

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.

Forum Discussion

Pressing a formula Rich Text button in an email

Code Page Formula Button

Formula Url Buttons

Pressing TAB key puts form in view mode

QrewTip: Formula Logic Order

Delete Formula Button Error