Brace yourself, the following concept can be a bit of a brain bender.
Manufacturers have much the same problem. They assemble product A from components B and C. But component B is assembled from components D & E and so forth... Each manufactured Item has it's own BOM (list of components and parts) and the need arises to list, say for an assembled automobile all the bottom level parts that are not assembled in the factory--the parts supplied from the vendors from which the factor acquires parts and materials for the products and components that it manufactures.
Turn those Blends and Components into products and components and you have much the same problem. Correct?
The trick, in the manufacturing world, is to use a single table for all products, parts and components. Multiple occurrences of the same table sit on opposite sides of the same many to many relationship:
The table occurrence named "Products"and the TO named "Parts"are different occurrences of the same data source table. A component is simply a "part" that has it's own BOM.
Here's a demo file that uses A Toy wagon's BOM of parts and components that you may want to examine carefully for ideas that you can use: https://dl.dropboxusercontent.com/u/78737945/RecursiveBOMDemo.fmp12
Thanks Phil - I had toyed around with this sort of concept early on as I am familiar with BOM requirements (you will see my materials/items index in the flowchart), but the scripting was beyond me - hence my many posts. I've had a quick look, and will see how I can apply this to my database. I will leave this thread open, as this involves some re-design of my workflow and I may need some clarification as I go.
Looking forward to stretching my abilities and learning new skills!
Hello Phil, thank you for the demo. I have been able to replicate the table structure and relationships in my database, and it looks like I should be able to build a blend from components. I have some questions about some portions of this example.
On layout Inventory, how does data appear in the field Inventory:PartsIDList?
Where is the script "start" attached?
Many thanks and regards
Start uses set field to put the list of IDs into PartsIDList
Build Parts List performs the Start Script. And just to make it a "brain bender", Start then Performs "Build Parts List" so this then becomes a kind of Recursive Scripting....
If I keep the database as simple as this, it works. However I want to be able to pick from available blends, not blends that have been consumed (You helped me in an earlier post by suggesting a calculation). So instead of the drop down in the portal being BOM:_fkItem from value list Products Inventory:_pkInventoryID and Inventory:Description, I have substituted Inventory: Description with the Inventory:Available component which is a calculated field if (component status = "available"; assigned component code). Problem is, that the calculation doesn't seem to calculate, so my drop down list remains empty. Up until I changed this one everything worked ok, and my previous version without the recursive BOM also calculated OK.
I've checked that the calculation type is text and that the assigned code and available code are text. I have validated that the calculation is not working by putting an "Inventory:available component code" field on the layout in the header (top right).
Obviously with all the looping and self-joins this calculation is getting messed up. Any suggestions? I don't want to end up with hundreds of components in the drop down.
Many thanks and regards
Two possible issues:
The calculation may set with the wrong result type. If it is returning text, make sure that Number is not selected as the result type. If it is, you can see the correct values in the field, but the indexes that a "use values from a field" value list use will not contain the correct values.
The calculation must be stored/indexed. If it references a related record or a global field, it can't be stored/indexed and thus can't be used as the limiting value in your value list. In which case, you'd need to change how this value is computed--such as setting up script triggers that update a data field in place of the unstored calculation field.
definitely set to the right result type, but I did check my value list and found the error (I had the wrong field attached to the status drop down list). It's all working nicely now, although find I have to navigate away from the current record in the component layout and then back to the record to get the drop-down to have the current component in the list.
I have rechecked all my relationships against the demo you sent through. I notice that in the demo Inventory:PartsIDList (which is text) is related to BOM|SelectedID:-fkInventoryID which is a number. My understanding is that both related fields have to be of the one type for the relationship to work. I downloaded the sample again, to ensure that I hadn't corrupted the file while I was fiddling. What impact will this have on the function of the file?
Thank you for all your help.
I have to navigate away from the current record in the component layout and then back to the record to get the drop-down to have the current component in the list.
Try just clicking the layout background in a blank area after selecting from the value list. That commits the records to the database. If that works, you can include a script Triggered script to commit records each time you select from the value list. If commit records is not sufficient, you can include a Refresh Window step.
My understanding is that both related fields have to be of the one type for the relationship to work.
It's a "best practice" to have both match fields the same type. But a return separated list of values has to be a text field so this is an exception to that "best practice".
Thanks for all this. I can now actually get a product from the vineyard to the bottle complete with reblending! Now all I have to do is get the winemaker sorted out so he acts logically too… :-)
Good luck with that one! Database Relationships are simple. People relationships are a whole 'nother universe!