Lookup vs Rollup Fields
HTML-код
- Опубликовано: 28 июл 2024
- When architecting a database in Airtable, there are many uses for passing data from one table to another. In fact, this is the true power of a relational database. On my team, we call this “spreadsheets in 3D” - when you really start to tap into the relational database element, it’s like working in a spreadsheet that has crazy layers of depth and complexity. The hardest part of that is understanding the difference between Lookup and Rollup fields.
In short, a lookup field looks to a second table that is linked and it returns a field that you specify. The rollup field acts much the same way, with the added bonus of performing some type of “aggregation function” on your data before returning a field.
Each of these field types has its own specific use cases. In this video I outline a few of them, specifically as they pertain to creating proper automations.
Be sure to stick around for the end of the video where I demonstrate a complex use-case. There are many instances where we’ve needed to use this exact (advanced) procedure to establish the proper lookup relationships for our clients - it requires a few extra steps, but be advised that you can easily hide the extra fields from view after you’ve built the proper logical architecture.
What parts of your data presentation and/or automated processes can be streamlined with better use of these complicated field types?
_________________________________________
Learn more at these links: 🔗 👇
Join us for our next live training!
www.garethpronovost.com/webin...
Take our free Airtable Crash Course!
garethpronovost.com/airtable-...
Getting Stuck? See how we can help!
garethpronovost.com
Get our Airtable Field Type download!
www.gapconsulting.io/airtable...
_________________________________________
Table of Contents: 📖 👇
00:00 - What this video covers
01:15 - Framing the Example Database
02:08 - Reasons for using a Lookup or Rollup
02:05 - Why a Direct Link Fails
03:53 - Building a Lookup
06:11 - Where the Lookup Field isn't enough
07:26 - Building a Rollup
09:43 - Complex Example (Putting it all together)
_________________________________________
Software Resources (may contain affiliate links): 💻 👇
Airtable - airtable.com/invite/r/v0eI3ASY
_________________________________________
#GAPConsulting #GarethPronovost #Airtable 
Хобби
Join us for our *FREE LIVE TRAINING* that teaches the building blocks of automation: www.garethpronovost.com/webinar-registration
Your explaination on the lookup field here is exactly what I needed.
So glad to help!
Great video! That solved for my problem of having my Task table tell my Project table what phase each project is in. Keep em coming!
Exactly what I needed! Thanks so much!
Glad that helped :D
Thank you. Was looking for great examples like this to help me get my head around this and how the relationship functions work
Glad this helped and thanks for watching!
I'm crazy with access but since I discovered Airtable I'm excited
Thank you Gareth for this very helpful tutorial! As a newbie to Airtable, I was concerned that I would not be able to use Lookup like normally do with Excel, but this clears up. It helps a lot as I'm working on creating a database for my students.
Glad it helped!
The advanced segment starting after minute 9 is very useful. thank you so much
Glad it was helpful!
Exactly what I wanted. Simple...Thankyou!😊
Glad to hear it! 🙌
Nice explanation! Thank you!
Glad it was helpful! 🙌
Excellent, thanks so much.
Glad it was helpful!
Thanks for the info Gareth! I find your videos very helpful. I work in a similar field as you, and have been wondering how I would use AirTable to automate the tedious invoicing process. I was struggling to understand the Lookup field and now it makes sense that it would literally "look" into another linked field. This is going to be extremely helpful, as I'm designing a database to coordinate volunteers and event participants.
On that note, I just started using the Time Tracking block today. I'm wondering if that can be plugged into the process for invoicing?
@@jeirmeister1276 - glad to be of service. Yes, you can use the time tracking block with an invoicing process. I'd suppose you'd want to calculate a subtotal for an invoice based on the (1) duration and (2) rate. This could be done pretty easily with a formula.
I'd caution that you might want to build some sort of function that pushes your automation. For example, you might send this invoice to QBO only after you've reviewed it and checked a box. Otherwise, you might have your automation fire prematurely before you've put all the information in (depending on how you construct this).
Great video again
Appreciate that - happy to help!
Thank You, Boss!
Thanks for watching!
Thanks so much for all your helpful videos. Is it possible to use a roll-up to trigger a Zap when a total is reached in one field?
You can trigger a zap from Airtable when a record appears in a specific view. So, to do this, you could create a view called "Automation Trigger" - then set a filter on this view so that records only appear in the view when certain conditions are met. For example, "Roll-up amount >0" - This way, only after a rollup sums more than 0, will the record trigger an automation.
Hope this helps!
Great👍
I will be in touch your.. your videos are very informative
Sounds good - thanks!
Thanks, Gareth for the useful videos. I have been watching your video for weeks now and they are very valuable to me. 1 question: Is there is any way to create "nested single select field" ... for example, I have 3 single select fields "Block", "Floor" and "Room/Location" ... If Block A is selected for example in floor field only values which are related to Block A will appear and if floor GF is selected only rooms and locations related to Block A+GF will appear.
Thanks
Ahmed
Hey Ahmed - sounds like you're looking for a parent/child relationship between datasets, which Airtable doesn't easily offer in it's current form. That said, a developer built a cool tool to help with this if you are submitting a form response. Check out this video for more info: ruclips.net/video/b_wBsLdZGI4/видео.html
Hope this helps!
Is there a way to use the number of records for calculations? meaning...if I have a table with 7 records (as stated under the table)... can I retrieve that counted number in a formula? sorry for bothering... is for a COVID 19 programme.
Thanks for the videos and info. is there anyway to set notifications to pop up on your desk top instead of having to go into the bell icon? would be good to have so you dont always have to go check for notifications. i know on the mobile it has push notifications just want to know if we can set it up to push on desktop, like whatsapp msger does when a msg comes through. thanks,
Hey Nelson - you can set your notifications to push to the mobile device and you can also choose to receive an email notification. Other than that, the bell icon is the only other way to receive notifications with the native app.
That said, you might also consider building a Slack integration. If you don't use it already, Slack has a great free plan and you can integrate this with Airtable rather nicely. This might help you get alerts when important things require your attention!
How do we fix a lookup not showing the values we need?
Hi, after plenty of tries I have to ask for help.
I have a table with a list of clients with a "Last modified time" column.
So I can track the last interaction with a client due to some changes in the status or notes fields.
In a second table, I would recap how many records have been modified on a specific date (ex: 1 April).
I don't understand how to explain to Airtable this logic.
Basically, I would check the number of interactions with my clients on specific dates.
This data can really be useful for me to monitor the number of my activities and relate them to my goals.
Can someone help me out with this issue?
Thanks a lot in advance.
I'm having trouble using lookups when I have multiple tables linked on my base. The table I want to lookup from isn't coming up as an option. The two are linked however. What could I be doing wrong?! Help!!
Are the tables linked directly, or are they inferred?
If you have tables A, B & C... Table A can link to Table B - then Table B can link to Table C. With this relationship, there's a relationship inferred from A to C. Table A links to Table C THROUGH Table B - however, you can't use a lookup under this condition. The link must be direct (you can lookup A from B, or B from A, or B from C, or C from B).
Hope this helps!
I would like to know if it possible to search in all fields of a table while looking for a linked record (in another table)?
when you start typing for a record to link to, the only thing that will be available to search from is the primary field of the records you are connecting to.
Is it possible to do a lookupfield and filter by a view of the other table, or filter by a select option?
for example, Project A contains 2 members, 1 with membership A and 1 with membership B.
Can I create a lookup so that I can get membership A info and memberhsip B info only? for automation purposes.
Not in the native software, but you can build an advanced form with dependencies using a plug-in/widget. Check this out for some tips: ruclips.net/video/b_wBsLdZGI4/видео.html
Hi, thank you very much for your video. I have multiple dates from the tasks table, in the main project table. In project table I need the earliest and latest date of the particular type of task. Please hep to write this code
Hi! To bring back the earliest date from a linked record, use a rollup field along with the MIN function. Similarly, to get the latest date, use a rollup field with a MAX!
Gareth, I just booked a consult call with you. It's 2 weeks out however. Any way to hire you directly, before the free call? I need help asap. Thanks!
He endorses people that do that on his site. Also you can post the question here and he may answer or someone else might know the answer
I have two tables are directly linked. “Look up” is not an option to choose from when creating a new field. I’m on iOS. Is this a pc only feature? Not sure what I’m doing wrong
Yes, this is a browser only feature - unavailable on the mobile apps. That said, if you build it in your browser, you can then see that data from your mobile app.
hello sir,
I am new at kodular, My question is how to find
Automatically Row number, I have student db but i every time not look up
in table and write Manually row number into code section.
so what is the solution of that…?
please help me i am very confuse in Index in list thing its find air table row number…?
If yes then how we can find Air Table row number…?
I am waiting for Your Response…? I NEED HELP PLEASE HELP ME…
Row numbers aren't a thing in Airtable the way they are in a spreadsheet. This is because the row number isn't consistent - it changes from view to view. The closest thing you could do is use the 'autonumber' field type to fill out a new number every time you create a record.
What's the formula you used to create the full name primary field? I’m truly a beginner.
Most likely a concatenate field to combine multiple pieces of info. This is something that I address on our weekly webinar, if you'd like to sign up it's a free event I hold every week: www.garethpronovost.com/webinar-registration
Can I lookup across multiple tables? Currently it only lets you choose 1.
You can only lookup to one table per lookup field.
Hi Gareth, how can I extract the email domain in the email field?
I'm not sure what you mean, the email lookup will already extract this data
Can a Rollup field be used with qualitative data? Or just with quantitative?
Qualitative is possible too, in the form of arrays (unique array, for example)
How to do sumifs() in airtable???
They are not quite the same as Excel. Instead you'll use a ROLLUP field type, aggregate with a SUM(values) formula, and set conditions to only count the records you want to include! 💪