Forum Discussion

QuickBaseCoachD's avatar
QuickBaseCoachD
Qrew Captain
7 years ago
Stephanie,
I did get that concept working once for an app with Bill of materials for parts assemblies where an assembly could use a subassembly from the same table,  and there was no limit as I recall to the number of loops.  The trick is to somehow not have it be seen by Quick Base as a circular reference. 

Unlike excel, QuickBase does not tell you that is has a circular reference, but it does behave weirdly.

When I get a chance I will see if i can locate that app to remind myself how I solved that obstacle.
QuickBaseCoachD's avatar
QuickBaseCoachD
Qrew Captain
7 years ago
Stephanie, I did find that app in my Library.  I recall at the time I was interested in the concept as a theoretical puzzle.

I will paste below the dashboard comments on my app.  Contact me off line via the gmail, email address on my website, QuickBaseCoach.com
and we can discuss further.

I also see now that I was so pleased with myself I posted it to the Exchange.  It is called Assembly Costing.

This app will allow you to do Assembly Costing.  The concepts starts with a basic parts list.  You can then construct an "Assembly" ie a Bill of Materials (BOM) and typically add labor to get an "Assembly". each Assembly has a Parts cost and a Labor cost and then of course a total cost.

But the magic with this app is then you can take that Assembly and use it in another BOM which would typically have have more parts  and more labor and more other Assembles to get another higher level Assembly.  But the delightful aspect of this app is that the Assembly table can infinitely be a child of itself, so there is not theoretical limit to how "deep" you can go in terms of nested Assemblies.  The app was originally written for a client who has Assemblies which go 5 level deep, built there is nothing in the design of the relationships which limits it to that 5 levels.  

 Each Assembly shows what its child BOM is  and also which are it's parents, ie where it itself is used in a higher level Assembly's BOM.

 In order to avoid a circular relationship, the app uses a webhooks.  if you are not familar with Webhooks, then you will need to contact me or someone for assistance.  But basically each time the record is edited, the webhook kicks in and copies the calculated value of all the Assembly calculated costs for Labor and Parts costs into a mirror field. (ie not for that 1 record being edited, but for ALL Assemblies in the table)  Then it is that mirror field which is used to roll up to the next level.  That way Quick Base does not consider it to be a circular relationship.  The reason it does that is because when say a part cost is changed or the labor rate is changed all assemblies could potentially be affected.  So the copy process in fact runs 5 times "just to be sure".  (then there is an exception report looking for any additional copying to be done with a button to kick that off.

 The StockRoom pull table as another small feature of the app just to be able to "pull" a fresh cost into a table (or it could be fed into an order line), presumably to charge a client with the current cost of that Assembly.

 

Ideas for Development

Ability to copy an Assembly BOM

Identify All Sub Assemblies for an Assembly (ie all the children and grand children down the chain)

Who will own the web hook? Note that the app will not work unless the webhook is set up.

Subscribe to the Mismatches safety net