5 Replies Latest reply on Dec 30, 2015 3:27 PM by golife

    Hierarchical portal sorting with product assemblies


      I am referring to a previous question I had put some time ago - with very nice answers - and worked a lot on the solution. But at the moment, I would like to ask if there is any more experience with hierarchical sorting in portals where also the sorting order can be changed.


      See also:

      Re: Portal sorting problem moving detail records up or down


      The problem is a bit more complex.


      In portal the user should see different levels of products with calculated dimensions and prices for each product. One product can contain a number of other products. This makes a product assembly or Bill of Materials.


      Products are configured in table PRODUCT, some without, some with items creating a Bill of Material (BOM). PRODUCT therefore can form an assembly of parts (products). The relationship is between PRODUCT -< BOM where in the BOM one id points to the parent PRODUCT, the other id points to the child product (called PART in my case).


      Now, when the customer orders products, we want to not only show the PARENT product, but the whole list of subsequent depending parts. In ORDERDETAIL we can see the parent product on level 1 (or you could also define it as level 0) and dependent parts (level 2...n).

      So far so good. Using a filtered portal, it is possible to show the full expanded version of all products with all their parts, or any hierarchy the user likes to see.


      I start to have problems figuring out how to best sort products in portal sorting order while allowing to move products to any position.


      If there was no portal sorting, products and parts would just appear as copied into the ORDERDETAIL. But since we want the user to also move products (at least products on the highest level) up and down (if not also parts within their level), it becomes not an easy task.


      Using just the highest level 1, it is easy. But moving a product up or down requires to also move all it's dependent parts. Inserting or deleting a product on the highest level also means inserting and deleting all dependent parts. Showing or hiding levels of hierarchy also means renumbering not just the product on the highest level, but also dependent parts.


      Parts are indented to also give a visual clue on the level of hierarchy.


      Row numbers are simply 1...n. They do not reflect hierarchy. Ideally there is a hierarchical numbering such as 1.1, 1.1.1, 1.2, 1.2.1, etc. This would truly reflect a hierarchy of products and parts.


      To illustrate using a simple example:



      Row#  Hier.#  Name                   Level


                                                                (Total price = Sum of 1 ... 2)

      (1)      1        - Product A              1  (Price = Sum of 1.1 ... 1.3)

      (2)      1.1     - Part A-A                  2

      (3)      1.2     - Part A-B                  2

      (4)      1.3     - Part A-C                  2

      (5)      2        - Product B              1 (Price = Sum of 2.1 ... 2.3)

      (6)      2.1     - Part B-A                  2

      (7)      2.2     - Part B-B                  2

      (8)      2.3     - Part B-C                  2 (Price = Sum of 2.3.1 ... 2.3.1)

      (9)      2.3.1  - Part B-C-A              3


      /* Now try to move Product B within the portal to position 1. It means, moving the rows 5-9 to become 1-5, and 1-4 to become 6-9 keeping the order between the product and its parts. Also the hierarchical numbering will just reverse. My solution idea so far is to hide all other levels > 1 and there will be only level 1 rows. I can move product B from then row 2 to row 1, and product A in row 1 to row 2. Then I must make sure to renumber all other row numbers and hierarchical numbers in ORDERDETAIL itself, and then I can expand the levels in the portals again. */


      I am using even two "number" fields, one which reflects the current position (1...N) of each row, the other one reflecting a hierarchical order. But making sure both fields are updated correctly depending on whatever event may occur, and especially also keeping products and their parts together always, is a complicated scripting experience.



      - User wants to move a product with all dependent parts to another position up or down (not lower than 1, not higher than max rows)

      - User wants to delete a product (deleting all dependent parts)

      - User wants to insert a product (inserting all dependent parts)

      - User wants to add a part on any level of an existing product

      - User wants to edit a part on any level of an existing product

      - User wants to delete a part on any level of an existing product

      - User wants to change an existing product (possibly changing parts, adding parts, removing parts)

      - User wants to reorder parts within a product not breaking the boundaries of the level

      - User wants to freely move any part to any product to any level

      - User wants to move a selected group of products / parts to any position in the portal moving all grouped products and parts


      The absolute dream would be to allow also drag-and-drop moving where solutions exist, but maybe such situation is less complex.

      My question therefore: Is there any elegant way I am overlooking?

      Any experiences?

      Any more samples?


      And I think this is not such an uncommon situation to present products in order detail forms in hierarchical order, one level containing another level, but allowing to move such groups from one to another position dynamically updating the sorting order within the hierarchies.

        • 1. Re: Hierarchical portal sorting with product assemblies

          Have you considered an infinite hierarchy solution?


          Here's a good example:

          Infinite Hierarchy | HOnza's Bits @ 24U

          • 2. Re: Hierarchical portal sorting with product assemblies

            It is one of the hierarchy examples, yes. Thanks a lot. I have not seen this page yet.


            Still, there is a bit more to it coming to more details.


            Basically i am still looking for the most simple all-in-one solution which also allows repositioning of whole groups, editing, deleting and inserting anywhere using the portal.


            To some degree I have it. But not yet 100%. )))

            • 3. Re: Hierarchical portal sorting with product assemblies

              golife wrote:


              Now, when the customer orders products, we want to not only show the PARENT product, but the whole list of subsequent depending parts...


              Just out of curiosity, how does the customer access the data?  Via FMP?  FM Go?   WebDirect? Custom Web Publishing? An eCommerce site with data pushed from FMS to MySQL via ODBC or an API provided by the eCommerce platform?

              • 4. Re: Hierarchical portal sorting with product assemblies

                There probably wouldn't be an entire all-in-one wrapped solution for you. Most likely you'll need to borrow from multiple samples to do what you need (if it's even possible).


                Once you're finished. You might want to provide a sample copy of your techniques to the community. Places like modularfilemaker.org and this site open up for sharing, and I'm sure you're using techniques people would be interested in.

                • 5. Re: Hierarchical portal sorting with product assemblies

                  Thanks for all comments ). I appreciate them all. Thank you!


                  Sharing a solution

                  I was also thinking about sharing such solution. But I am not (yet) sure that my solution would be unique enough, genuine enough to share. But I am thinking...) To really present a modular and generic solution which would work for everybody and in almost all situations is not easy.


                  So, for the moment I am sharing my current implementation thoughts hoping that even words can give some help or ideas.


                  Limiting the hierarchical levels for practical reasons

                  For now, because of the complexity of additional calculations needed and presenting almost each level with a different set of fields in each row of the portal, I decided to limit the levels as it is impractical anyway to go down more than 3-5 levels anyway also for users. (Practical really are up to 3 levels.)


                  Each product group is calculated differently

                  Imagine a product consisting of sub assemblies where each part is also calculated differently, for example will increase proportionally, or not, in width or length, as a rectangle, cube, or area, in weight etc. based on figures selected from the root product. So, also each group of parts has it's own calculation method and units.


                  Show/hide also product groups and levels with summaries in the portal

                  Then products and their parts are also grouped using product groups/categories summing up values for each level.

                  5 root products can consist of 20-50 parts with sub parts. I hide or show with or without such sub levels.


                  All sorting and re-positioning on the base table level

                  Of course I do all the sorting and re-positioning purely on the base table level using filters and sorts with a multi-sort criteria in the portal. For now that works. It is quite a lengthy script. I am using a sort field for each row representing the row number. I am using another field where each product on each level is numbered. In a calculated field I place them together as "", or "" etc. The original sorting order from BOM is maintained. Also all dependencies are maintained.


                  Using one layout wherever possible

                  I am using anchor-buoy model, but not yet separation and not yet connector-selector, even though I am using virtual lists. I found that users do not like to switch layouts, and I focus with a lot of functionality using just one layout wherever possible. I also avoid using list views except for printing where they are needed. I am using master-detail which means that on the layout there is a left navigation portal which allows users to select different records of the same table, such as products, orders, etc. and a detail pane on the right side with additional portals as in this case. (There is a list view and a detail view at the same time.) Users know where they are, what they do, and what to expect without using additional windows and changes in the overall presentation. Users do not loose context.


                  Using SQL for hierarchical trees?

                  Also I was looking into hierarchical tree solutions for SQL. But not all SQL is supported in FileMaker. Nevertheless, it could be displayed using virtual lists. It is also not trivial. There are plenty sites describing such techniques. I would need much more time to think about implementation in FileMaker.


                  Portal techniques to share for data entry

                  I am sure, I am reinventing the wheel. But in case: I found myself one nice very fast way of entering new products into the portal which works well here. There is a show-product name field which is displaying but not editable and shows names using tab char(9) depending on the level, and it does not show on the last (entry empty) row. On the last (empty) row an entry pickup field is shown. From this field the user can select the root product name from the value list, and the associated id is selected using executeSQL. Then a script is importing all associated parts and sub-parts from the BOM. I tried popover techniques ... but nothing is as fast in selecting directly from a not too long list which also reacts on keyboard entries. It is user friendly enough.



                  I allow deletion of products and parts. The rule is that a product delete, all sub assemblies are deleted as well. Only if there is no sub assembly the part itself is deleted. Then all can be deleted, or multiple-selected products (rows). Make sure that after deletion all is re-ordered and re-numbered.


                  Moving products up or down

                  I am still working on moving products including all their sub assemblies up or down. For now it requires to hide all sub assemblies using the pure root products of level 1. But I want to also be able to move products within their levels, but not beyond them.


                  Nice to have: Object levels in layout mode

                  All is solvable, but already there is a clutter of differently showing and hiding fields in the portal (very difficult to manage on the layout level !!!) since what belongs to the root product does not always belong to it's parts. I do not like to use different portals. Want to do basically all in one.


                  I would love if FileMaker had different layers in layout view where objects could be placed on layers which could be shown or hidden. It would make work so much easier. For example, groups can be layers. Then also editing individual components is very easy. It is already mentioned in the new ideas section.



                  I am working on Windows. The solution is in-house using FMP. A web version is planned, but would be much simpler. To really build a sophisticated web application I would probably not use FileMaker  -  or maybe just for data display and very basic operations. Most people (except 1 user) are using Android phones. So, it makes no sense to develop for Go.


                  JavaScript library for dynamical technical drawings

                  What we developed is a graphic utility creating technical drawings of selected products representing their dimensions and user selected properties using a special JavaScript library. It already looks very good. Customers need to see what they order and buy. It already works, but there are still some small issues in bidirectional data exchange using WebViewer and container fields. It should hopefully be working soon and I am thinking of sharing some of that.