Collaborate on Airtable Invoice for HR with Ease Using airSlate SignNow

Airtable invoice for HR

Gareth Pronovost: If you're a little new to building your own database, you might be getting stuck when it comes to linking tables. One of the most difficult concept for beginners when they first come to air table is understanding when to implement something called a junction table. This is a third table that helps you get more functionality out of linking to other tables. In this video, I'm going to be breaking down what it is when you should use it and give you three very common examples. So if that's of interest, stick around, and let's get into it. Welcome back to the channel. If you're new here, my name is Gareth and this is GAP Consulting. And it's our mission to help you get the most out of your no code tools. Air table is one of those incredible no code tools that really helps us do so much in the background, and organize our businesses and lives. Before we get started on the video here. If you're new to air table, or if you need a refresher, I welcome you to join me for some free training, I've put together a training called Airtable Crash Course, where I'm going to walk you through the key features of the software so that you can really get the most out of air table. If that's of interest, go ahead and grab that training at https://.gapconsulting.io/airtable-crash-course. And we will send that training right to you. But without further ado, let's go ahead and hop on into my screen. The first part we have to talk about here is really what a junction table is, and when you would want to use it in your system. So a junction table is the combination of two other tables. So in all cases, a junction table will be a third table. Let's actually take a quick look at our first example here, where I've got two different tables, team members and rates. Now the junction table is the actual third table that we build, that then links to the other two tables. So in this example, we have a link back to our team members and a link back to our rates. So again, the first two tables are getting linked to in the third table. Now the components of a junction table are that it will always link to two or more other tables. And when it's linking to these other tables, it's only linking to one record at a time. So I do not allow it to link to multiple team members. And I do not allow it to link to multiple rates. So the junction table allows me to itemize all of those linked relationships. So I can say there's one team member here at one specific rate. That way, we can do some more complicated logic or computation on the back end inside of the junction table. Now that we have an understanding of what a junction table is, let's take a look at three common examples. Starting with our first example here, which is to do with rates. So let's go back to our team members. Here, we have a bunch of different team members listed out they have different status, whether they're active or inactive, we have their role inside of the company, who they are and what they do in the organization. Now we could put rates right here in this table, right, we could just drop a rate field and bring in a currency and just assign rates to people. Now the reason that we don't want to do this, however, is because rates may change over time. Ideally, we have people that work with us for years and years. And as their skill set and experience grows, we want to start rewarding them with a higher rate. Well, if we just had a field like this, and then we came in and overrode their rate, let's say somebody was at $50. And then we started to pay them more because they got you know, a bit of a promotion $60 an hour. Well now, any calculations that we had that we're looking at that first $50 An hour are now calculating incorrectly, we don't want to lock the rate in to one specific field just like this. So how can we get around this? That's the junction table example number one. So here's my second table of rates. And we say, Hey, we've got a few different rates that we offer for developers. We've got the entry level developer, who is it 35 An hour, the intermediate is at 45. The senior Dev is paid 60. So in this way, we are able to track multiple rates over time. And so as somebody is promoted throughout the organization, we're going to reflect the higher rates. So here is the first example in our first junction, we link to again, first table team member second table rate. And in both cases, we're only linking to one team member and one rate. And then we're able to say well, let's do some more calculations on the back end. How many hours was this particular assignment, for example? 100 hours? Cool. Well, we know if they were at the entry developer rate, we can look that up because we know what that is if we actually expand that entry developer. This is the record from the rates table. Well, then we can see it's a $35 rate. So we can look up this value right here and now perform some calculation say, Oh, well, we know how much we owe them. If they're getting paid $35 an hour, and it was 100 hours in total, well, then we can do the calculation with this formula. Now similarly, here, Diana can come in be an intermediate developer at a slightly higher rate. And we can calculate her Amount Due as well. But let's say in the future, we bring Ali into another assignment. And this time she has been promoted to a senior developer. Well, now, we don't want to go back and change this amount, because this amount is not going to change, she had a different rate at the time. And so because of the fact that we built the junction table, we're able to link to the proper rate in every instance, and then bring in that amount due. So we can look up the current rate, whatever is laid out here, and then perform the calculations that we owe. Let's look at another common example example to where we're going to look and break down attendance between two different tables. Again, we have our two tables to start, we will always have at least two tables. In our example, here, we have class schedules. And so we've got a class called underwater basket weaving. And we have three different dates that this lecture will occur. This is the April 1, April 3, and the April 5 example. So three different lectures. And then we've got students in our next table, Joe, Sally and Betty are all presumably registered for this class. Now, how do we actually track their attendance, we do not build a direct relationship between class schedule and students that would not allow us to get line by line granularity in terms of who attended when. So what we do is we build, you guessed it a junction table, the junction table for attendance, which again follows our junction table rules, two linked relationships, this one linking to the class schedule, and only linking to one at a time always. And the next one linking to our students. And again, only one student at a time. And so now we're able to pull in on this particular class schedule for one or April 1. Joe, did Joe attend. Similarly, April 1, did Sally attend. And so we can just mark off the checkboxes right here, we can always perform the calculation on the back end, once we have the proper structure as we now do. So we can see at a glance that Joe has attended one course, missed the next one, and then attended the third lecture, Sally, perfect attendance, check, check, check, Betty, not so good. only got one. So back here and students, we can build a count field now that says I want you to count every instance in my attendance table, where they attended, where the box was checked. And just like that, we can see Sally has perfect attendance, three out of three, Betty not doing so hot, and Joe in the middle of the pack. Now, that takes us to our third and final example for junction tables, and frankly, possibly the most common one out of all of them. And that has to do with invoice line items. So imagine we've got some services that we offer here, we have hourly consulting, we offer project consulting, and we offer some courses. So we've got a different rate for everything here. If we do hourly consulting, it's 250 an hour if we're doing projects, we're doing $5,000, a sprint. And if we're selling a course, it's just under 1500. Let's say, okay, so how do we build an invoice, it's very possible that people buy multiple things from us at the same time, they're not just gonna buy courses, or hourly consulting every single time, sometimes there's a mix, so we cannot build a direct relationship to our invoices. So here we say, invoice number 1001 linked to Acme, we're going to build it together right now, if we put that direct relationship in, we don't get the line by line granularity that we need. So we build a third table, a junction table called invoice line items. Here we go. First thing we do link to table one service. So we build our linked relationship to services. And of course, as I've already mentioned, multiple times we turn off multiple records. Next, we're going to build a link to relationship to invoice. Here it is, and again, turn off multiple records. Now we can link up and say, Alright, we've got invoice 1001. Let's actually move this one over here. And we're going to group by this. So here I am invoice 1001. And we might have sold 10 hours, let's say of hourly consulting, let's build a quantity field. And this will be just a number field type. I don't need decimals here because I will only sell to the whole hour. So we'll look up 10 hours right there. And now we need to know well what is the rate that we need to charge for this invoice so that Rate lives in the service table. So let's look up the rate, we will do a lookup field. And we say, look at the service and tell me how much that service costs. What's the rate, bring that in here. There we go 250 an hour. So now we can do the line item total, with a formula. And we will simply take the quantity that we sold in hours multiplied by the rate. And now once we format that to be currency, we're going to have the line item total for this particular sale. But maybe we sold more on this invoice. So let's go ahead and hit the plus button. Not only did we sell hourly consulting, maybe we also sold a course. So we know the price of that course is 1497. Great. If we sold one, bring that quantity in. And now we have the math on the back end showing us the right amount. So we can now roll this information up into our invoice. So we know what the total we need to charge is not too hard to do this, we can come up with a total field, use the roll up, which is going to look at our linked relationship. And we are going to look at the line item total and perform a sum of all the line items there. So we sold the one course plus the 10 hours at 250 apiece, giving us a total on the invoice of 3997. If we go back here to the line items, we can actually see that some represented right here in that group relationship. Now one more closing thought here, when I'm building a junction table, I love to always use a concatenate formula in my primary field, your primary field is the leftmost field in your table. And I'm going to say line item name. And I'm going to use a formula here that simply concatenates the two different elements or linked tables that we have in this particular example. So I've got my invoice here. And I've got a space, a bar and a space, and then I have my service solt. Now I like to do this because inside of a junction table, what we are doing is essentially breaking down this detail of saying hey, on invoice 1001 We sold this service, and that's exactly what this record represents. And so by building this name, I can see invoice 1001 sold similarly, consulting, invoice 1001 sold a course. And that is giving me the breakdown that I need inside of this. Now I've got these extra records still floating around here from when I created this table initially, but you get the idea. At this point, we've gone through three amazing examples for building out your junction tables. I hope this helps you start taking your building to the next level, almost guaranteed whenever you get stuck building inside of air table or another database tool. If you can't figure out your schema, almost guaranteed that you need a junction table so come back to this video. That's it for this one, my friends. In the meantime, keep on building