10 Replies Latest reply on Jan 15, 2014 8:49 AM by philmodjunk

    creating new child records without a parent record

    JennySemmler

      Title

      creating new child records without a parent record

      Post

           I have finally got my winemaking database singing sweetly except for one issue that I need to resolve.

           I have attached my workflow below - a vineyard has many patches/varieties of grapes, and this makes many components (multiple harvests per year, multiple years). There is an indexing table to link many components into many blends and many blends to many components. The components are blended into a wine blend through a portal which has a drop down value list of available components determined by calculation. If I then bottle that blend the database performs well.

           Sometimes, however, a blend may itself be re-blended (either in part or in whole) i.e. looped back to become a component. I have written a script to take blend information and write that into a component record for re-selecting as a component through the drop down value list. This type of component record has no parent patch/vineyard, and does not appear in the value list for selection despite the calculation working correctly. I have managed to get the value list to work correctly if I assign this re-blended component to a "dummy vineyard and patch" to provide it with a parent. However this then leaves me with clunky issues of having a vineyard that I don't want. To complicate matters, for legal reasons I have to carry through all the original blend details into the re-blended component. 

           (Example: 2012 shiraz from vineyard a is blended with 2012 shiraz vineyard b (AB). Half of this blend is then removed and blended with 2012 cabernet from vineyard c & d, and the other half of the shiraz blend goes to bottle. Eventually Shiraz AB Cabernet CD is itself blended with Merlot from Vineyard A to make wine SCM ABCD. I have to legally report % variety, % vineyard and % vintage of AB that is bottled and SCM ABCD that is bottled.)

           Any advice on how I should address this would be very much appreciated. 

      Workflow.png

        • 1. Re: creating new child records without a parent record
          philmodjunk

               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:

               Products----<BOM>-----Parts

               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

          • 2. Re: creating new child records without a parent record
            JennySemmler

                 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!

            • 3. Re: creating new child records without a parent record
              JennySemmler

                   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

                    

              • 4. Re: creating new child records without a parent record
                philmodjunk

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

                • 5. Re: creating new child records without a parent record
                  JennySemmler

                       Hello Phil,

                       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

                  • 6. Re: creating new child records without a parent record
                    philmodjunk

                         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.

                    • 7. Re: creating new child records without a parent record
                      JennySemmler

                           Hello Phil,

                           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.

                           Cheers

                           Jenny

                      • 8. Re: creating new child records without a parent record
                        philmodjunk
                             

                                  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".

                        • 9. Re: creating new child records without a parent record
                          JennySemmler

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

                          • 10. Re: creating new child records without a parent record
                            philmodjunk

                                 Good luck with that one! Database Relationships are simple. People relationships are a whole 'nother universe!