0

Multiple items from inventory used as one product

Hi all,

 

In my business, I have a long list of stock items, but I only sell boxes with specific items in them.

I'm looking for a way where I can have an inventory list that I can keep updated, but when a client buys a specific box, those multiple items that are in that box are deducted from the inventory list.

 

For example:

Box A contains 6 items from the stock list.

Box B contains 10 items from the stock list. some of which there are two of in the box.

Client buys box A, and those items are deducted from the stock list without having to manually add each item individually.

 

any help would be greatly appreciated!

Tom

10 replies

null
    • John_Halls
    • 3 yrs ago
    • Reported - view

    Hi Tom

    In ERP terms this would be called a Kit. It's like a Bill of Materials but instead of the parts being turned into something different the parts remain as they are but sold together in the Kit. It all depends on how your workflow is set up. Do you assemble the Kits ahead of time? Do the Sales Orders / Delivery Notes / Invoices show the Kit, or the Invetory Items of the Kit? There would be a Many-to-many relationship between the Kit and its parts and at some point in the process a script would be run to decrease the number of parts in the Kit and increase the number of the Invetory Items ready to be sold. Shall I have a go at putting an example script together for you?

    Regards

    John

    • stanandted
    • 3 yrs ago
    • Reported - view

    Hi John,

    thank you for getting back to me. I'm all still new to this so apologies if I'm not making too much sense!

    Yes so basically the kits are made to order. The inventory sits on the shelf until a customer orders a kit, then the box is picked from the inventory and packed. The sales order just displays the kit purchased, and not the individual inventory items.

    I've had a look at the example 'Inventory' database that is preset in the app, and that is pretty much spot on to what I need, except for when adding a client order, I'd need to add the kit to the order instead of the individual inventory items. 

    Hope that makes sense! 

    Thanks

    Tom

    • John_Halls
    • 3 yrs ago
    • Reported - view

    Hi Tom

    I'll take a look at the Inventory template.

    Regards

    John

    • stanandted
    • 3 yrs ago
    • Reported - view

    Brilliant thank you John, any help is greatly appreciate it as it's starting to hurt my head now!

    Thanks

    Tom

    • John_Halls
    • 3 yrs ago
    • Reported - view

    I used the Inventory template.

     

    I added a table called Kit with 2 joins to the Article table, Kit as Ordered" and 'Articles in a Kit'. This is where you define your Kit. In addition there is a Kit Orders table, also joined to the Article table. Records here are added by a script in a button on the Sales Order Line Item form.

    Data Model

    Kit

    Kit Orders

    Add a new tab to the Article form to be able to add Kit items.

    Artilcle

    Add a button to the Sales Order Line Item

    This will still need work to check that a kit exists, an also to prevent the button being pressed more than once, etc

    Sales Order Item

    Sales Order Item Button

    And update the formula that calculates the Article Inventory

    Inventory Calc

     

    I hope that helps, it's a start anyway...

    • stanandted
    • 3 yrs ago
    • Reported - view

    Hi John,

     

    Thank you so much for getting back to me on this. You've made it look so simple but for the life of me i can't seem to replicate what you've done!

    Is there any chance you could break it down a little more for me, step by step if possible? I'm clearly missing something or putting it in the wrong place.

     

    Thanks

    Tom

    • John_Halls
    • 3 yrs ago
    • Reported - view

    I'll put something together over the weekend Tom

    • stanandted
    • 3 yrs ago
    • Reported - view

    Hi John, that would be brilliant if could, thank you so much!

    • John_Halls
    • 3 yrs ago
    • Reported - view

    Hi Tom

    I'll get you to the point where you have added a KIt table and it's joins.Once we know you have that working we'll move on th the next stage.

     

    Add a table called Kit. Add a field called Quantity and while you are still adding fields to this table go to the Create Table Reference and add 3_Article. Call this Kit as Ordered. This is the relationship that allows a kit to be built for a single product. Now add ANOTHER table reference using 3_Article and call this one Articles in a Kit. Notice that both arrows are pointing to the right. This is the norm for a join table used to create a many-to-many join. What is unusual, and maybe a bit confusing, is that both the tables it joins to are the same one, Article. At this point click OK and Save Changes and see the kit table with two arrows pointing to 3_Article in the Data Model. Now look at the 3_Article table and see the last two fields are Kit and Kit2 both with arrows pointing left. Change the name of Kit to Articles in a Kit and Kit2 to Articles as Ordered. While here add a Tab Layout element and call it Kit. Drag it just above Articles in a Kit. This creates an extra tab in the form view. We don't need to see Articles as Ordered so set its Display field only, if to (or null).

    Kit Table

    Now click back into Kit and click on the Kit as Ordered relationship and change Composition from No to Yes and click OK and Save Changes. This makes the relationship an embedded child of Article.

    Kit SubTable

     

    No go to your 3_Article List and click on the first record. Go to the Kit tab and check that you can add articles to the join. You will want to add columns Article Numer and Article Name fromthe Articles in a Kit table.

     

    Let me know how you get on. We'll move onto the next part once you are happy you have got this going.

     

    Regards John

    • stanandted
    • 3 yrs ago
    • Reported - view

    Hi John,

     

    Thank you for your help so far. This step by step approach was perfect! Managed to do all of this with no issues so I believe we can move on to the next part if thats ok?

     

    Thanks again

    Tom

Content aside

  • 3 yrs agoLast active
  • 10Replies
  • 988Views