Joiner tables are a powerful way to combine different tables to accomplish all sorts of things in your Knack database app. Their use cases can vary a lot. Here I show the details of a couple examples. It's a big topic, but this is a start.
Interested in my Knack database app services? ... Book a call with me here: https://calendly.com/daveparrish/callwithdave
Full Transcript of this Knack Database App Post
0:00 Hey folks Dave Parish here doing another video for you guys today's topic join our table example the other week knackered as webinar and this table was brought up sort of a it can be a complex topic for me at least when I was first starting in Belia it took me a while to get my head around it but 0:25 I'm gonna give you an example of one I might have a bonus one and put this a little bit of context of what they can do because it's quite powerful so I'll be right back.
0:01 Okay, let's go for it. Join our tables. Hey, like and subscribe to my channel if you can. I appreciate it. 0:09 Let's do this. I'm going to call up an app. I'm going to give you a quick overview of this. I'm probably going to do a case study on it. 0:16 This is a quite involved budget. Budgeting, invoicing, and accounting system. I do a lot of these. But here's the quick overview. 0:27 This is an organization that is funded by the U.S. Department of Agriculture. There's a bunch of these type of organizations that their job is to promote agriculture commodities. 0:39 It could be. Seafood, it could be corn, whatever, to promote these outside of the country and all kinds of different markets. 0:47 And they come up with and it's an invoiced approval system too. But they come up with an annual budget for what they're gonna do for the whole year. 0:57 They create activities. Here's tons of activities. An activity could be going to the Pigula Wiggly and handing out samples. It could be hiring an influencer to do something to get buzz going around something. 1:10 It could be a cooking class, all kinds of things. They have budgets that they set for each of these main activities in each market. 1:22 Then they hire local people to execute them, local firms. These people then log into the system and invoice create invoices and expense items that go against each one of these activities so they can always see where the budget is. 1:37 We're not even done with this, but we do have these budgets in here. How much was spent. To date, I'm not going to get into whether this is and the balance of it. 1:45 They do a whole invoice approval system. There's all kinds of stages to it. It captures milestones along the way. Then they have a process of they have to upload stuff to. 2:01 Government system to get reimbursed for things. And then later on auditors from the government come in and check everything to make sure they're cool. 2:07 This all handles everything without any custom code and without any make scenarios. But regardless, the issue here is for joiner tables. 2:18 Let's go to one of these years. These are different budgets. For a year. So it's their whole thing for a year. 2:26 And why we needed a joiner table is there's two main things. There's different kinds of programs. They have these names for consumer food service, agreed. 2:35 They do different stuff for, and there's also markets. These could be joined year. For market, year, or program. And in this case, I've done a few of these for different organizations. 2:49 Some organize themselves around market. Some organize, organize themselves around programs. And when I say organize, I mean, here's different programs. 2:58 And there's a point person, the marketing manager. For each one of these areas. And they're responsible for monitoring everything that's going on, doing the approval, seeing where they are with the budget, all that stuff. 3:15 Well, we have a year here, and we have a program type here. And to get it to go. Group like this. 3:22 Actually, you could group it like this in a pivot table, but then you couldn't go down and see details of all kinds of stuff and do perform all kinds of actions. 3:29 To get this fiscal year to group in this manner with the program type, we need a a joiner to, ,table. 3:42 And just as an aside, if you didn't want to do that, you could do something like, hey, let's look at all the activities and then let's filter by program type and then they can go here and see just stuff that's for food service, but that's the pain. 4:01 We can't do it. I'm gonna go to the back end. There's a bunch of stuff going on here, but what we want is we have different activities and we have different Master Grand is the year and we want to combine those together. 4:39 So I made this table just called year program and what that does is merge the program. I'll do the records. 4:51 The program type could be any one of these with the Master Grant, which is the year and we make a display named this. 5:02 Combines everything together so that the managers can go to one spot and see all their stuff. And not only does it do that, for all the different calculations we do, we count number of activities, budget spent the day balance, all that stuff is automatically calculated. 5:22 We could not do that without this table that puts the year together with the program type. This is what this accomplishes. 5:31 That's what I got on that. I got one bonus thing for you which is, takes this to another level. I'm pretty sure this falls in the realm of joiner tables. 5:41 This is a, swimming school. It doesn't matter. All kinds of schools are the same. I do a lot of school stuff too. 5:47 They have students right here and they have classes, different types of classes. Bio 101. I mean, even though it's a swim class. 5:56 But what they actually have to join stuff together, they don't just have classes. They have an instance of, a class. 6:03 This is a partly adjoining thing. It's again swim 101 that occurs on this state, at this pool, with this instructor. 6:16 Right? So that's separating the actual type of class to an actual class. And then this takes us, ,and it's coming into another level. 6:24 And that other thing we just joined the year in the program. What this does is take an instance of a class, ,and then joins a student to it. 6:33 But it's not just a student because a class can have many students. So this joining thing takes one instance of a class, a specific class, and does a one-to-many and joins many students to that class. 6:50 This system, any classing system, or school scheduling type system, wouldn't work without having these sort of tables in between the main stuff. 6:58 So it's a one-to-many and joins many students to that class. That will help think about how to approach these. Thanks guys, bye.
s