We cover *_junction tables_* in more detail in our crash course - if you want to learn the basics of Airtable for free, check it out here: www.garethpronovost.com/airtable-crash-course
Junction script is a real winner :) Personal preference: I like to use the naming convention "Table 1 x Table 2" for junctions to keep track of the source tables.
Nice! This strategy will help fix several struggles I've had with my airtable database! Now I just have to dig in and figure out how to adjust/fix my tables with this method. It will also help me with some metrics I've wanted to set-up but couldn't figure out how to do it.
Excellent video, very useful tool. But what happens if I have many students, many classes, and I don't want to create the junction table for all students against all classes? Let's suppose I have 2000 students and 60 classes, and not all students are in all classes. Is there any way to filter which students I will cross with which classes so that it only makes the appropriate combination?
@@GarethPronovost Dude, I feel you. That's exactly how I felt when I learned about transaction logs (essentially a junction table on steriods in one of my previous companies).
Does this particular set up work if you want to try and export your database schema and converted into a real relational database like postgres or something?
Great explanation. Is there an easy way to create the junction table without the script? Airtable made extensions unavailable on the free plan, and I don't really want to spend $20 for exactly one extension.
I'm sure you can do this with a script, but that could be costly as well if you don't know code. Another option is to manually populate the junction records
Thanks so much for this useful video! I have a scenario where all of my students would be signed up to different classes. Using the junction table in the way you described would mean that every student would be included in the attendance register for each class - even the ones they were expected to attend. How do you propose we go about creating an attendance register for only classes the student is signed up for?
Hi - great question! You don't have to create these records using the Junction Table Script - instead you can create the records as the students register (either manually, or with automation). In this way, you will only create the records you need!
Curious about the effect of junction tables on record limits in a base. For example a full outer join between 100 records in Table A and a 100 in Table B = 10,000 permutations in the J table. The requirement to re-run the script to account for any changes in Table A or Table B means that if you remove unwanted permutations in the J table, they will get recreated each time.
You're right that junction tables can add significantly to your record count. Generally when using them in practice we don't use the script to create the records, but if we were to do so then your math checks out.
Thanks. I have alrady heard from the junction tables but never used. Is there a practical way to use it in Aitabel? For example if i want to „put“ students in to class or give students classes. Heve we always go to the junction table? Thanks and God bless you!
Hi Gareth, love your content. Would this be the best approach when creating a 'Charge Summary' document (in Documint) that displays a summary of product totals from a line-item table, this table has multiple records containing the same product names, so I don't want individual records repeated- I just want to show total hours for a given product each month. I assume a junction table would give me a list of all products per purchase order, in which I could then use roll-up fields + filtering to line items assigned that product + PO combination (excluding products with totals = 0). TIA!
Hmm I definitely feel like this is something I've been missing in my AirTable bases and it's probably the reason why I get stuck a lot of the time. I still am trying to figure out what kind of use cases I can use this for when it comes to my personal task management...Thanks for giving me something to think about!
I feel like I over use junctions tables or over populate my bases. I'm keeping track of an assembly process. The price of the various materials vary. I have some "sub parts" which can be used in multiple bigger assembly. then I have an output schedule and a promotional schedule, a cost table, client table and a revenu table oh and a tools table 🙈 I struggle a i spend too much much of my time entering things manually.
I also use junction tables A LOT, and you really should if you want to properly normalize your database. The trick is to leverage automation and scripts to automatically add the records to you base.
Great video. Can you please do the same video on how to create matching automation, say like tinder for dating or better service companies with say 20 different service variations and a customer 50 different demands/needs, then once it's a match, they get notified via email and can continue communicating
Hi Danil, that's a pretty specific request! 😅 If you need help with this, we have consultants who can help - check out our services here: garethpronovost.com/airtable-consulting
Excellent video and very instructive. Nevertheless this junction table creates records for all possible combinations table 1 x table 2, but what if only some records in table 1 relate to table 2 ? Is there a script for this ?
We are using Airtable to track electrical construction projects (PROJECTS table), project clients and vendors (COMPANIES table), who worked on the project (PEOPLE table) and project photos (PHOTOS table). I have a junction table (PROJECT TICKETS) between PEOPLE and PROJECTS because this is many-to-many. PROJECT TICKETS also houses what the persons position was on the project, since this may change from project to project. The PEOPLE table also connects directly to the COMPANIES table since each person can only work for one company at a time. COMPANIES also links directly to PROJECT. The PROJECT table has separate fields for Client, Owner, General Contractor, Architect, Engineering Firm, each of which only hold one link to a record in the COMPANIES table. (Sorry for the long wind-up.) Here's my questions: 1. Should I be using a junction table to connect PROJECTS with COMPANIES and store the label of Client, Owner, General Contractor, Architect, Engineering Firm as a single select field type at the junction record level? 2. What are the pros and cons of each method?
Hey Dave - this is a pretty deep dive for UA-cam and I'm sure we could help more thoroughly with an hourly consultation which we offer on our website. That said, the biggest pro to using a junction table for that info is that it moves your data into a vertical layout as opposed to a horizontal layout. If you use direct links for each role, you have to add a new field every time there's a new role you want to include. This get cumbersome and difficult to view. A junction table makes light work of this as you can drop the role into a single select field. Ultimately though, it's up to you - the best database is the one you USE, so build it the way that feels most comfortable to you!
Hi, thanks for your explanation, I think I'll be using this a lot :-) I've got a question: is a junction table the best way to go if I want to do a price update for item I'm selling? I haven't figured out quite how to do that; when I update the price per item, my sold items/finished orders are updated as well. Are you covering such a situation in any video? I've tried to use the discount strategy but it isn't working yet :-) Thanks in advance!
Hi! Yes, you can certainly use a junction table for this. Another (simpler) option would be to "retire" old prices and create a new record when you update a price. You can do this by adding a checkbox field for "archive" and you check this box when a price is retired. Then create a new record in the table and assign it the new price. This way your old prices won't be altered and you'll still be able to have the current price.
We cover *_junction tables_* in more detail in our crash course - if you want to learn the basics of Airtable for free, check it out here: www.garethpronovost.com/airtable-crash-course
M1111111¹¹^$÷_÷#¥
Junction script is a real winner :) Personal preference: I like to use the naming convention "Table 1 x Table 2" for junctions to keep track of the source tables.
Love that advice re: naming conventions!
Nice! This strategy will help fix several struggles I've had with my airtable database! Now I just have to dig in and figure out how to adjust/fix my tables with this method. It will also help me with some metrics I've wanted to set-up but couldn't figure out how to do it.
Very happy to help!
This was a moment of aha😇- or do you say moment of truth. Very good explanation for what I needed to know to get better in Airtable.
So glad to help!
Thank you Gareth!
Excellent video, very useful tool. But what happens if I have many students, many classes, and I don't want to create the junction table for all students against all classes? Let's suppose I have 2000 students and 60 classes, and not all students are in all classes. Is there any way to filter which students I will cross with which classes so that it only makes the appropriate combination?
This is something SUPER helpful!! Thank you Boss!!
Really happy to be able to help, William!
Love it! Thank you! :D Haven't figured out my specific use case for junction tables yet, but when the time comes, I know I'll be prepared!
For me, once I started using them I found that I needed them all the time! It's a slippery slope 😂
@@GarethPronovost Dude, I feel you. That's exactly how I felt when I learned about transaction logs (essentially a junction table on steriods in one of my previous companies).
@@Erica-pk5lf there's camaraderie in our nerdy-ness. 🤓
@@GarethPronovost #Truth. The people I bond with over a newly discovered keyboard shortcut are my kind of people LOL
That is a major discovery for me. I will use this a lot, thanks 👍
Glad it was helpful!
Does this particular set up work if you want to try and export your database schema and converted into a real relational database like postgres or something?
Great explanation. Is there an easy way to create the junction table without the script? Airtable made extensions unavailable on the free plan, and I don't really want to spend $20 for exactly one extension.
I'm sure you can do this with a script, but that could be costly as well if you don't know code. Another option is to manually populate the junction records
Thanks. So scripts are different from extensions? I do know how to code (frontend developer) so that could be an alternative.@@GarethPronovost
👍🏻 Thanks !
Thanks for watching!
Thanks so much for this useful video! I have a scenario where all of my students would be signed up to different classes. Using the junction table in the way you described would mean that every student would be included in the attendance register for each class - even the ones they were expected to attend. How do you propose we go about creating an attendance register for only classes the student is signed up for?
Hi - great question! You don't have to create these records using the Junction Table Script - instead you can create the records as the students register (either manually, or with automation). In this way, you will only create the records you need!
@@GarethPronovost ok thanks! I was hoping to avoid having to do this manually. What could you suggest as an automation to try to get this to work?
Curious about the effect of junction tables on record limits in a base. For example a full outer join between 100 records in Table A and a 100 in Table B = 10,000 permutations in the J table. The requirement to re-run the script to account for any changes in Table A or Table B means that if you remove unwanted permutations in the J table, they will get recreated each time.
You're right that junction tables can add significantly to your record count. Generally when using them in practice we don't use the script to create the records, but if we were to do so then your math checks out.
Curious, is there any way to run this app from a trigger? Like a record gets update, or created?
Sure, though it would require some custom code in the script
Thanks. I have alrady heard from the junction tables but never used. Is there a practical way to use it in Aitabel? For example if i want to „put“ students in to class or give students classes. Heve we always go to the junction table?
Thanks and God bless you!
Hey Tarzan, yes junction tables are used all the time when creating a data structure. I'm not sure how to answer your question though
Hi Gareth, love your content. Would this be the best approach when creating a 'Charge Summary' document (in Documint) that displays a summary of product totals from a line-item table, this table has multiple records containing the same product names, so I don't want individual records repeated- I just want to show total hours for a given product each month. I assume a junction table would give me a list of all products per purchase order, in which I could then use roll-up fields + filtering to line items assigned that product + PO combination (excluding products with totals = 0).
TIA!
Hey Dean, sounds like you're on the right path. Junction tables are everywhere, so you'll start using them more and more as you build new things 💪
Hmm I definitely feel like this is something I've been missing in my AirTable bases and it's probably the reason why I get stuck a lot of the time. I still am trying to figure out what kind of use cases I can use this for when it comes to my personal task management...Thanks for giving me something to think about!
Thanks for watching!
I feel like I over use junctions tables or over populate my bases.
I'm keeping track of an assembly process. The price of the various materials vary. I have some "sub parts" which can be used in multiple bigger assembly. then I have an output schedule and a promotional schedule, a cost table, client table and a revenu table oh and a tools table 🙈 I struggle a i spend too much much of my time entering things manually.
I also use junction tables A LOT, and you really should if you want to properly normalize your database. The trick is to leverage automation and scripts to automatically add the records to you base.
Great video. Can you please do the same video on how to create matching automation, say like tinder for dating or better service companies with say 20 different service variations and a customer 50 different demands/needs, then once it's a match, they get notified via email and can continue communicating
Hi Danil, that's a pretty specific request! 😅
If you need help with this, we have consultants who can help - check out our services here: garethpronovost.com/airtable-consulting
Excellent video and very instructive. Nevertheless this junction table creates records for all possible combinations table 1 x table 2, but what if only some records in table 1 relate to table 2 ? Is there a script for this ?
I'm sure the script could be written, though it would depend on the nuaced dependencies for your specific case.
Thanks !
We are using Airtable to track electrical construction projects (PROJECTS table), project clients and vendors (COMPANIES table), who worked on the project (PEOPLE table) and project photos (PHOTOS table). I have a junction table (PROJECT TICKETS) between PEOPLE and PROJECTS because this is many-to-many. PROJECT TICKETS also houses what the persons position was on the project, since this may change from project to project. The PEOPLE table also connects directly to the COMPANIES table since each person can only work for one company at a time. COMPANIES also links directly to PROJECT. The PROJECT table has separate fields for Client, Owner, General Contractor, Architect, Engineering Firm, each of which only hold one link to a record in the COMPANIES table. (Sorry for the long wind-up.) Here's my questions:
1. Should I be using a junction table to connect PROJECTS with COMPANIES and store the label of Client, Owner, General Contractor, Architect, Engineering Firm as a single select field type at the junction record level?
2. What are the pros and cons of each method?
Hey Dave - this is a pretty deep dive for UA-cam and I'm sure we could help more thoroughly with an hourly consultation which we offer on our website. That said, the biggest pro to using a junction table for that info is that it moves your data into a vertical layout as opposed to a horizontal layout. If you use direct links for each role, you have to add a new field every time there's a new role you want to include. This get cumbersome and difficult to view. A junction table makes light work of this as you can drop the role into a single select field.
Ultimately though, it's up to you - the best database is the one you USE, so build it the way that feels most comfortable to you!
@@GarethPronovost Thank you! I hadn't thought about the implications of adding/tracking a new role!
Hi, thanks for your explanation, I think I'll be using this a lot :-) I've got a question: is a junction table the best way to go if I want to do a price update for item I'm selling? I haven't figured out quite how to do that; when I update the price per item, my sold items/finished orders are updated as well. Are you covering such a situation in any video? I've tried to use the discount strategy but it isn't working yet :-) Thanks in advance!
I've sined up for the crash course, maybe I'll find it in one of those videos :-)
Hi! Yes, you can certainly use a junction table for this. Another (simpler) option would be to "retire" old prices and create a new record when you update a price. You can do this by adding a checkbox field for "archive" and you check this box when a price is retired. Then create a new record in the table and assign it the new price. This way your old prices won't be altered and you'll still be able to have the current price.
@@GarethPronovost thanks, I'll give that a try first!
This is a great video. But please explain it gradually. I feel it's a bit fast for me. Thanks.
the datetime format is L (small) and not 1 ;)
correct!
text