More flexible data model / formula?

Community Discussions
, , ,
1

Seeking guidance on a more flexible solution to what I think is a data modeling issue. What I currently have implemented is working very well, however it's not structured to scale without manual intervention and I'd like to determine if there's a better/strong/faster method. :thinking:

Use Case
Application to manage inventory for a circuit board manufacturing outfit. The company manufactures several different types of circuit boards...each composed of varying quantities and types of components.

Current Solution

Three relevant tables: [board_details], [order_modeling] and [board_summaries].

b_d o_m b_s

Within the application, a user can model an 'order' comprised of several different boards at varying quantities and determine if there are enough components within current inventory to support the order, as shown below:

buildview

This insight is achieved by calculating the total quantity of each component required across all boards, represented in [order_modeling][Total Required].

At issue, is the formula within [order_modeling][Total Required] used to generate the required number of each component (formula below). Essentially, I have a formula hard-coded to look at each circuit board within [board_summaries], for each component, and multiply the required number of each component by the quantity of boards being built. This WORKS but is pretty hairy and would be administratively rough to maintain as I add circuit boards. Looking for another approach.

=(SUM(FILTER(board_details,"board_details[Component]=[Component] AND board_details[Board Name]=""Board One""")[Board Component Count])*SUM(FILTER(board_summaries,"board_summaries[Board Name]=""Board One""")[Quantities]))+
(SUM(FILTER(board_details,"board_details[Component]=[Component] AND board_details[Board Name]=""Board Two""")[Board Component Count])*SUM(FILTER(board_summaries,"board_summaries[Board Name]=""Board Two""")[Quantities]))+
(SUM(FILTER(board_details,"board_details[Component]=[Component] AND board_details[Board Name]=""Board Three""")[Board Component Count])*SUM(FILTER(board_summaries,"board_summaries[Board Name]=""Board Three""")[Quantities]))

2

@atsouth

What if an automation kicked off every time the number in the Build Project screen changed?

And that automation knew what the old total for "Board one" was, and the new total for Board one. Then you'd use that diff, multiply it times the number of components needed, and apply that to the required quantity.

Then you don't need any formula for the column. Checkout the $[previous] system variable, I think that'd be key. Honeycode Glossary of Terms

This would also need to be a sheet automation, created from the left navigation.

3

Thank you for that suggestion @AndrewB. I hadn't considered the applicability of automations in this context. I'll go down that path for a bit and see if I can make it work.

4

Automations get really complex, but they're the secret power of Honeycode. Other low-code app builders don't allow you to just randomly write data all over the tables like this.

2 Likes