A.D. Tejpal
adtp at airtelmail.in
Mon Jul 19 09:33:14 CDT 2010
This is in further to the earlier thread "BOM - Table Structure For Handling Common Parts In Various Assemblies/Sub-Assemblies" (2nd half of Jun-2010). Valuable inputs were kindly provided by Arthur, Rocky, Drew, Jim (D) and Shamil (mentioned in chronological order of their postings).
Certain basic features that could perhaps be considered desirable for an access application handling tasks like bill of materials (BOM) are listed below:
1 - Use of composite key made up of PartID and ParentPartID as primary key for junction table. This would be in line with the approach indicated by Shamil.
2 - Similar approach for ancillary table used for holding the extracted product chain.
3 - For data entry / editing, the following arrangement could be helpful to the user, while building up a product chain:
(a) Row source for bound combo box meant for ParentPartID should grow progressively in such a manner as to ensure that there is continuity of component chain. At the very start, it would be a single value representing PartID of the top item for which the product chain is being built.
(b) On a freshly dirtied new record, entry for PartID to be feasible only if ParentPartID has been entered.
(c) Row source for bound combo box meant for PartID should be confined to values which would not lead to circular dependency.
(d) On each record, before finally accepting PartID or ParentPartID, super check to be conducted against resultant circular dependency - if any.
(e) If freshly entered values are likely to result in duplication of a given combination of PartID and ParentPartID, friendly message to user - without waiting for attempted move to new record.
(f) While carrying out data entry / editing, offer the user a choice (say via option buttons) as to the style of display for existing data. For example, it could be one of these: (i) Minimal cluttering by hiding all nesting levels other than the top one, (ii) Medium cluttering by showing all sub-assemblies but hiding their base components (parts having no child). or (iii) Show all.
4 - While extracting the hierarchical chain of components for a given top item, stop recursive process as soon as circular dependency is detected. While preventing potential wastage of loop cycles, it would eliminate the need for laying down an arbitrary ceiling for the number of nesting levels allowed.
5 - In line with the approach indicated in one of the sample db's kindly provided by Jim (D), complete product chain (for the given top item) as extracted, could be displayed in various styles as follows, as per user selection from an option group:
(a) All parts (Complete hierarchical chain).
(b) Assemblies only - arranged as per relevant nesting level.
(c) Assemblies only - Straight.
(d) Base parts only - arranged as per relevant nesting level.
(e) Base parts only - Straight.
Note: Summary information (like maximum nesting levels, number of distinct assemblies and base parts as well as total cost of base parts needed for the product chain) should also get depicted.
6 - Tree View:
(a) Tree view display of product chain should match the subform data style as per previous para.
(b) The tree view should be in two way synchronization with the pertinent subform. Selection of any node should force the cursor to move to corresponding record in subform and vice-versa, with matching color highlight.
(c) Topmost item (for which the product chain is being extracted) in tree view as well as the subform should be highlighted in matching color.
(d) All sub-assemblies on the tree view as well as subforms should get highlighted in matching distinct color for ready differentiation as compared to base parts.
(e) Total cost of base parts needed for the product chain should also get depicted at bottom in the tree view.
7 - The application should be designed in such a manner as to suit both data types (number or text) for PartID and ParentPartID.
I have tried to incorporate the features outlined above, in my new sample db named
Form_TreeView_BOM (access 2000 file format), which is now available at Rogers Access Library. Link:
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=45
Interested members might like to examine the same and advise whether anything needs further attention.
A.D. Tejpal
------------