Ok, you have an Knack database app with a products table containing, well, all your products. But sometimes you have a "kit" ... a bundle of those individual products that you sell together. Here I show a structure where both the kits and individual products reside in the same data table. It is much better to have them in the same table, rather than making a separate table for kits. Please check it out.
Interested in my Knack database services? ... Book a call with me here: https://calendly.com/daveparrish/callwithdave
Check out this related post: https://www.knackbuilders.com/post/knack-object-connect-to-itself
Full Transcript
0:01 Okay uh, they perish, coming with another video, uh, you might hear nature out here. I'm still in Macaragua, and most places are very hard to find something that's quiet, you know. 0:16 I'll back to the hotel in some sort of garden. Hopefully I'm not interrupted, but today's topic. For lack of a better name, connecting products, two products to make kits. 0:29 This will make sense in just one second. Here we go. It's an app I'm working on. It's still a rough stage, but as I'm doing these, and something interesting comes up. 0:37 I like to talk about it. This is a company that makes fireworks. They manufacture them. They got tons of products. 0:43 Umm. And. They do. It says, I think there's a couple hundred products. But individual products. But sometimes they make kits. 0:56 And you know what I'm talking about. You buy a kit. It's got sparklers, cracker jacks, whatever. It's things that explode and fly in the air. 1:04 So it's a number of things. And we want it to have those also in the product table itself. So they're sort of like, I don't know. 1:12 It's inner breeding a little bit. Umm. I've done the shoes case a couple times. And I don't know if it's a trick, but it needs to be structured. 1:20 And it's an important way for it to work right. And I'm going to come back and go through this shouldn't take too long, thanks.
0:00 Okay, let's do this. We'll talk about this making kits and having a relation to the table to itself to do that I'm going to call up the front on first So here's all yeah 296 products most are individual products, but some we went To be as kits and contain these other products within the same table. 0:20 Let me go to one. That's a kit power pack So how you do this i'll take you to the front and then we'll go to the back end come on right here this first one power pack and it contains each one of these now this structure is going to end up being sort of like an invoice parent record which is the product 0:43 and to that child records that have bearable information uh but let's go to the this one now some of this needs to be cleaned up and note some content if it's a kit uh or if it's not a kit won't apply there's kit content and there's other content so the system will know if it's a kit or not and we're 1:02 going to use page rules to hide show various content but this needs to be cleaned up a little bit but down at the bottom here's what we have we've got a tip again sort of like an invoice we've got a kit now we're going to add line items which are the other products that make up the kit and we're going 1:20 select the other products and this will be filtered by only products not kits so that you can't accidentally add a kit to a kit uh so we we add one we also give the quantity sometimes there might be two sparkler sets and i'm not going to do it because i have them right here here's what this kit is made 1:38 up of and you can hyperlink right to the product we have quantity and they have these things called shots they have to measure they have a lot of regulations they sell in a lot of different countries shots are the number of things that go off i think now notice this has 16 shots it's one of them but 1:56 if we had two this does the calculation on itself now it has 32 i'm going to take this back to one so it automatically is going looking at the products counting up them and then we'll get a total for the whole kit that'll be done automatically otherwise they're going to be have to go to all the products 2:17 add them up off the side that won't work well uh and the other thing we're doing here over time their kits some products can be deactivated like they're not allowed anymore and they'll find a replacement for it so we have this thing called active and what what happens is they can and they need for auditing 2:39 purposes and they need to have a history of what went on with this kit so they can retire this product and there's two tables here active in the kit and stuff that used to be there and they can ah now it falls down on this retired one it has the information they can go capture a date and they can make 2:59 some sort of note of why it's retired so now they have a history of all that but let's go to the core thing of how this is set up um here we have products so that has all the products in it and then I made a separate table that I call kit product instance so it's an instance of a product in a kit with 3:20 variable information so that's what this is and I'll show you how it's set up you need two connections to product both these first two are to product this first connection makes the connection between the kit which is the product um the parent record that makes that then you need to get a second one 3:45 that's you're choosing from the products that allows those to be like we saw down here to add individual as many as you want now it's important you do that um that's the basic idea basic setup I got caught up on it first I only had one connection and when you do that it just defaults to the parent record 4:06 every time so that there's two connections there now a couple other clever things that come up here too uh I'm gonna go here and look at uhm for a non-kit product you're gonna manually enter the number of shots there's no other way to do it it's an individual product so we have a field called shots now 4:32 sometimes that's manually entered if it's a product and other times it's a calculation if it's a kit and we don't want two columns here here's how it would work out i don't even know if i have this completed let's go to a kit when you get these weird relations stuff come up so this is original data before 4:58 we did the structure but i'll show you what i mean here so what we actually have we have shots because this has to handle if it's a product or a kit and i'm not done with this but i'll show you what i mean this is the one that was manually entered and now we made it more robust to have the line items 5:17 so i did another thing it's a sum function for kits now we don't want it to look like this though um on this page based on if it's a kit or not i can do display rules to hide and just show the appropriate one now on the other view we can't hide show content columns so we're gonna have to do is make a 5:39 third field we got shots it's a manual and we got kit shots that's a sum we're gonna make another field that represents shots we're basically merging those two together using a conditional rule we'll have that shots to be used as a column here and we'll make its value if it's not a kit the manual entered 6:01 one and if it is a kit uh the calculated one if you follow that and in fact I'm going to do it I already wrote this down I'm gonna do another video on merging cells there's a ton of use cases to do that structure that I just talked about that works out really well that's what I got folks take care.