1 2 Previous Next 24 Replies Latest reply on Apr 29, 2017 7:25 PM by JackRodges

    Grouping Multiple Inventory Items as One into a Portal

    CStovin

      Title

      Grouping Multiple Inventory Items as One into a Portal

      Post

           I have created a basic Contact-> Order-> Line Item <-Inventory database but am at a loss how to go about creating the next stage.

           Our company signs out equipment to its employees.  Each Order can be made of multiple individual items from our inventory.  This is basically your typical Invoice solution except we are not selling items, but rather signing them out.

           The trick is how to assign individual Inventory items to a “Kit number & category which then can be pulled up in an Order portal.  Once that Kit number is pulled up in the portal the line items automatically become populated with all the items associated with that specific Kit/category.

           Additionally the Order portal would be able to pull up individual items from the Inventory.

           Items from the established kits could not be rented out individually until that particular item in the Inventory has it’s kit number and category association turned off / deleted.  This is to allow for upgrading equipment or moving equipment around which may have been assigned.

           Any help would be appreciated.

        • 1. Re: Grouping Multiple Inventory Items as One into a Portal
          philmodjunk

               This can be done with a bit of scripting and a "many to many self join" relationship between two occurrences of your inventory table and a join table used to list the items that make up a given "package".

               Contact---<Order----<Line Item>----Inventory----<PackageItems>------Inventory 2

               Inventory::__pkInventoryID = PackageItems::_fkPackageID
               Inventory 2::__pkInventoryID = PackageItems::_fkPackageItemID

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               Inventory and Inventory 2 are two Tutorial: What are Table Occurrences? that both refer to Inventory.

               You'd use a portal to PackageItems on an Inventory layout with fields from Inventory 2 added as needed to build the list of items that make up a "package" record in Inventory. You may need a Qty field in PackageItems if multiple items of the same ID are included in some packages.

               Then a script performed from a script trigger on the Line Item::_fkInventoryID field can check for the existance of related records in PackageItems. If such are detected, it can use Go to Related Records to pull up a list of them in order to loop through them and add them to Line Item and link them to the current order record.

          • 2. Re: Grouping Multiple Inventory Items as One into a Portal
            CStovin

                 Wow, thanks Phil,

                 I'll have to read this a number of times before the visual cements.  This will give me some direction even though the execution might not be completely clear to me yet.

                 Thanks for the break down, mucho appreciate.

                 Cheers

                  

            • 3. Re: Grouping Multiple Inventory Items as One into a Portal
              philmodjunk

                   What I've provided is only a general outline. Feel free to post follow up questions as you drill down into the implementation details needed to get this to work.

              • 4. Re: Grouping Multiple Inventory Items as One into a Portal
                CStovin

                     Hi Phil, 

                     I am still working out the basic  "Invoice structure" workings before I attempt to add on additional  ( Bill OF Materials ) type of structure.   I just realized that I might need 2 levels deep.  If that is the case would I need another < PackagedItems 2> and an Inventory 3  table occurance ?

                     Such as example:

                     INVENTORY::_set# =  "Set 8"

                     INVENTORY::_set_category = "Camera"

                      

                     Camera body assigned to "Set 8",   Lens assigned to "Set 8" ; both set also to category "Camera"  but............

                     Camera #8 also has a filter, and a lens cap which basically travel with this camera if it was ever reassigned to a different Set #

                     Maybe another way of phrasing it : 

                     1 -  Inventory 1 Items can exist on their own, and be pulled into Work Orders 1 if they are available.

                     2-  Individual Inventory 1  Items can be assembled to make complete products which become part of the Inventory.

                     3-  Assembled Inventory 1 Products can be grouped into Package Items which can also be pulled into Work Orders 1

                • 5. Re: Grouping Multiple Inventory Items as One into a Portal
                  philmodjunk

                       I think you are describing packages that consist of packages. If so, recursion can work here and not require any additional relationships. Each time your script adds an item from a package, it can check to see if the item is itself a package and can then use the existing relationships to generate a set of line items for that package as well by recursively calling iteslf via perform script.

                       Not a simple concept, but with care, it would work here.

                  • 6. Re: Grouping Multiple Inventory Items as One into a Portal
                    CStovin

                         Hi Phil,

                         I'm having some difficulty setting up this relationship.  I thought it would work the same way that my Lines -> Order works, but nothing is coming up in the table, even though I have both Inventory 2 and Package Items tables checked to allow creation.

                         Contact---<Order----<Line Item>----Inventory----<PackageItems>------Inventory 2

                         Inventory::__pkInventoryID = PackageItems::_fkPackageID
                         Inventory 2::__pkInventoryID = PackageItems::_fkPackageItemID

                          

                         I'm also unsure why there is a    _fkPackageID   &    a   _fkPackageItemID

                    • 7. Re: Grouping Multiple Inventory Items as One into a Portal
                      philmodjunk
                           

                                but nothing is coming up in the table

                           In what table? Viewed from a layout based on what table occurrence?

                           

                                I'm also unsure why there is a    _fkPackageID   &    a   _fkPackageItemID

                           You you had these items in your inventory table:

                           1 Red Wagon
                           2 Wheel
                           3 Wagon Handle
                           4 Stake Sides
                           5 Stake Ends
                           6 Body

                           Then Package Items might describe a "Red Wagon" package with 5 records:

                           _fkPackageID     _fkPackageItemID    Qty
                           1                         2                                4
                           1                         3                                1
                           1                         4                                2
                           1                         5                                2
                           1                         6                                1

                           Note that the quantity field records the number of item components needed to make one wagon. If some one orders 20 Red wagons, your line items table would log the above Qty values multiplied by the package quantity of 20: 80, 20, 40, 40, 20

                            

                      • 8. Re: Grouping Multiple Inventory Items as One into a Portal
                        CStovin

                             Ok I think I got it.

                             1:::    In your example above,  how would I create a go to button for the related record.  If I just use the go to related record function in script maker, it will go to the _fkPackageID which is 1 ( the same layout it is in basically ),  but if I want the button to be able to go to the _pk_Inventory_ID which would be equal to the same value as the _fkPackageItemID,  would I have to create a set variable = $_fkPackageItemID   then perform a find and paste the variable into the pk_inventoryID ?

                              

                             2:::: second question:  "Then a script performed from a script trigger on the Line Item::_fkInventoryID field can check for the existance of related records in PackageItems. If such are detected, it can use Go to Related Records to pull up a list of them in order to loop through them and add them to Line Item and link them to the current order record."

                             I'm not sure where to start with this, to be able to make a loop and automatically enter all the related items from Package Items portal into the Line Items portal.

                        • 9. Re: Grouping Multiple Inventory Items as One into a Portal
                          CStovin

                               I figured out question 1....   I had the packaged items layout in the same layout folder as inventory.   Once I pulled out the packaged items layout from the inventory folder - and made it is own it now goes to the proper item

                          • 10. Re: Grouping Multiple Inventory Items as One into a Portal
                            philmodjunk

                                 1) A portal to packageItems would seem a very useful thing to put on your Inventory layout.

                                 2)

                                 Contact---<Order----<Line Item>----Inventory----<PackageItems>------Inventory 2

                                 Inventory::__pkInventoryID = PackageItems::_fkPackageID
                                 Inventory 2::__pkInventoryID = PackageItems::_fkPackageItemID

                                 #Should be performed from a portal to LineItem located on a layout based on Order
                                 If [ Not IsEmpty ( PackageItems::_fkPackageID ) // if this is a package ]
                                    Freeze Window
                                    Set Variable [$Qty ; Value: LineItem::Qty ]
                                    Set Variable [$OrderID ; Order::__pkOrderID ]
                                    Go To Related Record [Show only related records; From table: PackageItems; Using layout: "PackageItems" (PackageItems) ]
                                    Loop
                                        Set Variable [$ID ; PackageItems::_fkPackageItemID ]
                                        Set Variable [$ItemQty ; PackageItems::Qty]
                                        Go To Layout ["LineItem" (LineItem)]
                                        New Record/Request
                                        Set Field [LineItem::_fkOrderID ; $OrderID ]
                                        Set Field [LineItem::_fkInventoryID ; $ID ]
                                        Set Field [LineItem::Qty ; $Qty * $ItemQty ]
                                        Go To Layout["PackageItems" (PackageItems)]
                                        Go to Record/Request/Page [Next ;exit after last ]
                                    End Loop
                                    Go to layout [original layout]
                                 End If

                            • 11. Re: Grouping Multiple Inventory Items as One into a Portal
                              CStovin

                                   this is a fantastic start,  Thanks Phil.

                                    

                              • 12. Re: Grouping Multiple Inventory Items as One into a Portal
                                CStovin

                                     weird it seemed to be working.

                                     Now when I pull up the Lines::_kf_products_id  in the portal on the Work Order layout it it will populate the first item that,  which other items are grouped to, and then it will populate the second item which is grouped to the first... but no more -

                                • 13. Re: Grouping Multiple Inventory Items as One into a Portal
                                  philmodjunk

                                       You'll need to describe the results that you are getting in more detail. I can't quite picture what you are describing.

                                  • 14. Re: Grouping Multiple Inventory Items as One into a Portal
                                    CStovin

                                         Inventory layout has a portal showing related records from Package Items.  ( following fields in the portal :)

                                         1st Field:   PACKAGE ITEMS::_kf_package_item_ID   <---------  drop down list field in the above portal that pulls values from Inventory 2 

                                         2nd field :  PACKAGE ITEMS::_kf_package_ID  <--------------- always the same number  -- " relates to the inventory product ID  which items are being group to "

                                          

                                         in The Work Order layout portal, which shows related records from LINES

                                         ORDER::_kf_products_id         --->  set script trigger on exit to run the " populate package items script "  which you wrote above

                                         If I choose an item from _kf_products_id   which has packaged items assigned to it in the INVENTORY layout using the package items portal,

                                         it will populate the first line item in ORDER with the associated _kf_products_id and only the second line items from the Packaged Items portal.

                                          

                                         example:   _kp_products_id =  0005    --->     Set 3,  Camera Nikon  Body        (  MAIN ITEM THE following items will be grouped to )

                                         example:   _kp_products_id =  0006    --->     Set 3,  Camera Nikon Lens  ,

                                         example:   _kp_products_id =  0007    --->     Set 3,  Camera Lens Shade,

                                         example:   _kp_products_id =  0008    --->     Set 3,  Camera Lens filter

                                                Fields in the portal ---->          the package portal reads :  PACKAGE ITEMS::_kf_package_item_ID  ,   PACKAGE ITEMS::_kf_package_ID,  INVENTORY 2::product_category  ,   INVENTORY 2::group_to_set  ,  INVENTORY 2::product_manufacturer ,  INVENTORY 2::product_name

                                         So when we add the above items in the portal which resides in _kp_products_id (   0005  )

                                         We get:

                                         0006       0005   Camera  03   Nikon Lens

                                         0007       0005   Camera  03   Nikon Shade

                                         0008       0005   Camera  03   Nikon Filter

                                          

                                         Then when we go to the Work Order layout , and pull up in the Lines Portal -  LINES::kf_products_id  and choose  ID# 0005  the portal automatically populates the line items with a script trigger on exit and the only values it displays is:

                                         0005   Set 3,  Camera Nikon  Body

                                         0006   Set 3, Camera Nikon Lens

                                         The other 2 packaged items 0007  & 0008  do not populate in the LInes

                                          

                                    1 2 Previous Next