Notion Formulas 2.0 - Advanced Masterclass
Vložit
- čas přidán 25. 07. 2024
- In this deep dive, we'll use a bunch of Notion's new formula functions and build something that used to be impossible!
📃 FREE TEMPLATE - Build this example with me:
thomasfrank.notion.site/Notio...
🎥 CREATOR'S COMPANION (The Best Template for Content Creators - use LETSGO2023 at checkout to get $50 off):
thomasjfrank.com/creators-com...
🔗 VIDEO LINKS:
Notion Database Course (free) - • Notion Databases - Ful...
Notion API Course (free) - • Notion API - Full Cour...
Notion Formula Reference - thomasjfrank.com/formulas/not...
📚 NOTION FUNDAMENTALS (Free & Complete Notion Beginner's Course)
thomasjfrank.com/fundamentals/
🛠 MORE COURSES AND RESOURCES:
thomasjfrank.com/templates/
thomasjfrank.com/courses/
💪GET STARTED WITH NOTION:
thomasjfrank.com/usenotion/ - (this is my referral link)
🐦Connect with me:
Twitter ➔ / tomfrankly (best way to interact with me)
Instagram ➔ / tomfrankly
Music ➔ @ThomasFrankMusic
⏱ TIMESTAMPS:
00:00 - Intro
00:23 - Example Project Overview (Videos Database)
00:58 - Another Use Case (Sales Dashboard)
01:38 - Build Start
02:34 - New Formula Editor
04:39 - Lists (Arrays) Tutorial
05:43 - Accessing a Relation's Pages
06:56 - Map Method Tutorial (Most Powerful New Feature)
11:25 - Searching the Relation
13:55 - Variables in Formulas
16:38 - Returning the Top 3 Videos by Views
17:50 - Adding a Second Variable
19:15 - Creating a String from Multiple Values
20:25 - Using the new Style Function
21:52 - Linking to Each CZcams Video
24:40 - Linking to Each Notion Page
26:25 - Truncating the Titles (Regular Expressions)
33:17 - Adding Commas to the View Counts (Regular Expressions)
41:33 - The Final Formula
41:54 - Digging into the Sales Dashboard Example
42:56 - Formula Reference Guide and Tutorial Site
43:54 - CZcams Management Template
🎞 SPECIAL THANKS
Select video clips supplied by Getty Images
💰 I'm fortunate enough to work with great sponsors who support my work, so I don't use Patreon or any other crowdfunding service. However, if you'd like to support a worthy cause, consider donating to help more children get access to a high-quality education: fundraise.pencilsofpromise.or...
Hey friends! This video is a deep-dive build that will show you how to use a ton of the brand-new functions in Notion Formulas 2.0.
We also just updated our Notion formulas guide with reference pages for every new function: thomasjfrank.com/formulas/notion-formula-reference/
Check that site out if you're looking for more beginner-level content or need a reference for any of the functions while building your own formulas.
HUGE props to Ben Smith on my team, who took on the task of updating all my docs himself. In the near future, we'll add example databases to every function page to make the guide even more interactive.
We're also working on some more formulas-related video content for this channel, along with lots of updates to our templates. This is a MASSIVE new Notion feature, so we've got a lot of work to do to fully take advantage of it.
More videos coming soon!
Such a clear, well-structured and brilliantly produced instruction. Thank you.
YOU ARE A LIFE SAVER!!!!
Thank you so much, Thomas!!
Is this really a video could watch for free?... OMG
Excited to play around with the new Notion formulas, thanks for the video!
Hey Thomas, just a big thank you for your videos and content. Really great work !
About 2-3 years ago, I stumbled upon your Notion video on the Project Tasks template, and that's where my Notion journey began. I vividly recall thinking, 'This is the guy I want to watch!' Thank you for being the guiding light in my Notion adventure!" 🚀📚😊
Thank you sooooo much!
I was struggling for a few days with how to manage Formulas 2.0 and every tutorial on CZcams wasn't deep enought... except by yours!
I really mean it!!!!
Hello Thomas,
I was struggling with something I knew was possible but the documentation but the documentation was not deep enough on the topic.
It took me 3 hours of head scratch thinking that surely it was possible but did not quite fully understand how to use formula from relation but I found the solution in less than 10 min with your explanation ! Thanks a lot ! Truly a masterclass!
This new functionality is mind blowing
As always, your videos are precious gold, thank you for taking the time to explain this so thoroughly!
I love how you give real-life examples to make things click quicker and sink in deeply 🔥
Thank you Thom! Amazing video! 😄👍
Thank you so much Thomas for explaining!
ThnQ Thomas! 🎉🙏❤
Thank you mate! Exactly what i was looking for
Thank you very much Thomas !!
🎯 Key Takeaways for quick navigation:
00:00 📈 Notion's Formulas 2.0: more powerful formula language.
03:37 🔄 Formulas 2.0 enables list sorting and manipulation.
09:38 🧩 Use "let" function for advanced data handling.
19:16 🧩 Combine text with string concatenation.
20:37 🖌️ Style text using the `style` function.
22:27 🔗 Create clickable hyperlinks with the `link` function.
23:49 📜 Use `join` to concatenate values into strings.
29:05 🪄 Harness regular expressions for advanced text manipulation.
39:49 🧮 Adjust regex for robustness in handling view counts.
42:13 📊 Create complex calculations with Notion's advanced formulas.
Made with HARPA AIt
This is so insanely good. I'm building my own life management system in notion and I've been using formulas but I realized I was still very limited because I'm definitely not using everything, and I was having a hard time understanding them at times. I can't thank you enough for this.
So helpful! Thanks a lot!
I love that you used Pokemon names to make this easier to understand. You're the best! ✨
Thank you Thomas!
Great video! I want to point out a better way to put commas into the numbers, the way you showed would work only up to 999,999,999, anything else above that would break or would require to add other replace statements into the formula.
Using a single replaceAll("\B(?=(\d{3})+\b)",",") expression would work for any number length.
Yep, that would be a better way! Unfortunately Safari didn't have support for lookarounds when I was learning formulas, so they would result in a blank formula on Notion's mobile apps.
There was a PR for adding them into Webkit last December, so maybe they work everywhere now - haven't had a chance to test that yet.
HUGE Update🤩, Amazing Video👌😍, Thank you so much..👋
Please create a video for notion formula 2.0 for a complete beginner
Thomas you’re really out here helping people upskill their careers for free 😭❤️ TY!!!
Thanks!
Thank you so, so much for explaining in such great detail these updated formulas. I'm a non-technical person, but am too curious to learn more about coding. If I also may say, your voice is quite mesmerizing and the portrayed confidence and intelligence is an assured turn on 😊😅
Oh God, i was following your previous channel regularly . I was searching where you disappeared suddenly for more than a year.
I found this new channel today. I wish you updated to all the subscribers in youtube about this. We all missed you.
Subscribed to this channel today.
THANK YOU
thank you so much
best think in this version is style property
So great! It would be amazing if you could explain how to cross-reference non-numeric information from databases using formulas
11:12: One can now sort by e.g. the first element in an array ("sort(current.at(0))"). I don't know if this was available from the start of Formulas 2.0, or if it's a new feature, but it does make a bunch of things a lot easier.
This is amazing. Does anyone know if you could create new relation to another database in formula property tho? that would be so helpful
Only thing I’d do differently is the name links to the page and the icon to the video (an more appropriate one). Because I think it’s easier to click on a larger name than to click on the small icon and I presume you’d be going more over to the page than over to watch the video.
Excelent Video
wow you are amazing
Nice to see the js-ification of Notion formulae continue apace, but still some holes here. In the first example, you're basically taking an object, reducing it to a single property in order to sort, and then rebuilding the exact same object using find, which isn't just inefficient but in fact will only work if the property you're sorting on has unique values. Not knocking your (excellent!) tutorial, but I think the sort() function really needs to be able to accept user-defined sorting functions as a parameter.
I agree! We've been discussing it with the Notion team; we might get some args for sort() in the near future.
For now, I did find a way to improve the fomula to deal with potentially repeated values:
prop("CZcams Videos").map(
[
"0000000000".substring(0, 10 - current.prop("Views").length()) + current.prop("Views"),
current.id()
]
).sort().reverse().slice(0,3).map(
lets(
currentID,
current.last(),
video,
prop("CZcams Videos").find(current.id() == currentID),
video.prop("Name")
.replace("(.{25}\w*).*","$1... ")
.link(video.prop("URL")).style("b") +
video.prop("Views")
.replace("(\d+)(\d{3})$","$1,$2")
.replace("(\d+)(\d{3},\d{3})$","$1,$2")
.style("c", "b", "green", "green_background") +
" 📃".link(video.id())
)
).join("
").style("b")
Thank you for your excellent content, i learn a lot from it!
however, i found a problem - if the number of view count is the same for 2 or more videos - it will show the name of the first video with this view count twice, instead of showing two different names with the same view count.
i understand that in videos view count it is not so bad, but what if i want to sort other stuff by numbers that are less big? do you have a way around it?
Thanks!!
Really good explanation. Do you know if there is a way to return a list of all values of a column (not just related)? That could be really powerfull with the Filter-function.
Unfortunately no; right now you have to access these values through a Relation. One trick you can use to deal with this limitaiton is to relate every page to a single page (which I call an Aggregator), either in the same database or a different one.
Hopefully we'll get db-level querying like this in the future!
That replace() function messed me up... LOL!
I gotta rewatch that a few more times.
Thank you so much this! i do however, have one problem. What if the values in my relation property are identical for more than one item, so for example suppose that you have two videos with an identical number in the "view" property of say 500, when you apply the formula in the first half of this tutorial, it unfortunately will not be able to distinguish between the two and returns the same CZcams video name for both.
I have the same problem :/ did you find any solution yet?
Thanks for the breakdown @Thomas, but DAMN! I really hate the fact that I cant filter formula properties... I just built a massive database network for some unique production types and its just so BRUTAL that at the final move of making a "production" databases template... I realize I cant filter the "Shots" database to the "production" without a direct relation... Im so at a loss... If you have any details on the "aggregator" technique Im all ears
Hi Thomas,
Thank. you for the wonderful videos. Been learning alot. I am struggling a bit to apply the mapping funcition on number properties.
I have Database A and Database B that are linked through a relation I want to return in Database A a sum of only those records on Database B that are within a certain date range. How do you suggest I go about doing that?
Thanks for the video. Do you think that granular sharing might be next for databases or is it possible to kind of do it now with this new update?
I really hope so! It's currently not possible to do at all. I think it's the most important new feature they should be working on, so I'm hoping we'll see it soon.
Hi Thomas. I have a dashboard where, similar to you examples, I have multiple databases that have some relation in common. I have to set the filter in each view for the same variable if I want to see filtered data. This is a bit frustrating and time consuming. Is it possible to have a single place on the dashboard where i can filter and all the different views update accordingly? Like a header row with filter controls that control all the views simultaneously. Thanks
Hi!
What is the best way to filter data from an another database?
36:32: Regex mnemonic: \w stands for "word", and \d stands for "digit".
gutes Video, muss ich denn in der relation property alle Werte der referenzierten Tabelle manuell hinzufügen? geht das nicht irgendwie einfacher?
Thank you, Thomas, for sharing such valuable content. I wanted to ask if there's any chance that we can create a weekly plan for tasks, and then have daily tasks that load each day based on which day of the week it is?
Hmm - if you want the daily tasks to be created automatically, there are still a couple of roadblocks unless you chose to use the API for it.
You could build recurring templates for each day of the week, but they currently won't trigger Database Automations - which are the main way you'd automatically create tasks based on something happening, like a weekly task template page being generated.
Great video, highly commend! One question please, is it possible to achieve the following?
if word A is included in column B, output content of column B and bold word A in the output result? I believe, in other words, conditional inline formatting. Wondering if this is possible in Notion? Greatly appreciate!
Thank you, this was so helpful! Finally removed a bunch of properties trying to rollup a rollup! And thank you for the formatting for the commas. Any chance you can point me in the direction of something similar for formatting dollars when it only sometimes shows the 2 decimal places?
As in less than $1? In that case, this should help! thomasfrank.notion.site/Format-Numbers-with-Commas-Directly-in-Formulas-f7ed00f48fb6488fa58f6dc76999aa5d?pvs=4
@@ThomasFrankExplains Thank you! Wow, that Formula Examples page is a gold mine!
now THIS is the state of the art of personal productivity
What about the Property Reference Limits, is there any change with Formulas 2.0?
@ThomasFrankExplains Nice video, quick question, for the thousands separator could you have used a look-behind operator on your regex??
I tried that a while back, but Safari didn't support lookarounds so they wouldn't work in Notion's mobile apps.
There was a recent PR to add them in, so they might work now. Haven't tested yet!
is there a way to get the name of the formula property from inside the formula? for example a formula property named "MyFormula" should have a string output "MyFormula" in every cell
What's the formula for calculating the word count of a page?
I love this! But I have a question. What can you do if two of your view numbers are the same? I have a couple posts with the same number of views and I keep getting only 1 of the post titles back.
Hello thank you for the video question do you have a formula that includes numbers after decimal to the right? So let’s say 1,000,000.33. Your replace formula doesn’t include decimal
Hi Thomas. around minute 20, you changed the style of the numbers and turned the number 704883 into green . Im trying to do that but keep the numbers format so i can sum it and add the dollar sign to it. is that possible ?
Thank you for this video but I’m lost after slice 😵💫
hi, i'm trying to create a formula that goes like if a relation is empty it should show like a word and I'm very confused was wondering if you could help?
How do you import CZcams analytics into Notion in the first place? The only way I have found is to do it all manually, but I don't want to do that when I can just access my CZcams analytics that are up to date day-by-day.
You can do it with the CZcams API - I made a tutorial on it! czcams.com/video/VUeFtATlIDY/video.html
Ahha! My question too. I have no interest in setting this up manually lol
@Mezxxii
6 seconds ago
I wish I could call out to a backlink rather than needing the add more explicit relations to my database that I'm creating a formula for.
Maybe Notion will add this to the next update? ...or I'm doing something wrong.
That'd be cool! It doesn't exist yet, so you're not doing anything wrong. Currently, formulas can only work with other database properties - not on-page content or special features like backlinks.
fu Frank, its like I'm watching a paid course
what make it better than others notion formula's video so far is real study case, while others only use random data.
its proved that you make this video seriously
thanks a lot
Great insight into the new features of Notion! I have a question about the link to page ID you mentioned at 26:00 , I would like to view the linked page in Side Peek or Center Peek but can't get it to work properly. I've tried adding "?p=" as well as the full workspace URL before the link - it works, but it also reloads the entire page for some reason. Any suggestions on how to get it to just smoothly open in side peek without reload?
It doesn't work yet, but myself and several other Notion Ambassadors have flagged this same issue to the team. Hopefuly they'll add it as ⌥/Alt+Click behavior soon!
Thanks for the reply! Glad to hear it's not just me 😊
Hi! Thanks for your video! I have a client database with a list of 'purchased programs' property (that is linked to program sold table) and I added a link property to the program database (this is just a list of program with some information on it).
I would like use the list (purchased program) to get the one that match the 'program.rank' property ==1 (in the program table) in a property of my client table.
So I want to compare each program in that property with the program table and keep only those with rank property == 1.
I just cannot figure how to do it since its calling 2 different table property. Do you know how I can do it? For now I tried with this formula but not working any way I tried it :
filter(prop("PROGRAM SOLD"),current.prop("Program LINK").map(current.prop("RANK"))==1)
@ThomasFrankExplains Hey,
May I ask how did you have a relation properties that has all the videos in it? I am not sure if you did it manually or is it a thing that can be automated.
For example, I am trying to build two databases, one will keep track of for example my daily expenses, and another will keep track of the days. So, the days database will have an entry for each day and each day should be related to the expenses that happened that day.
However, I cannot see to figure how can this be done automatically. Thanks.
All those pages in my Videos database are related to that one page in my Channels database. You can learn how to do this easily in my database tutorial! czcams.com/video/mAJOpO73d8Y/video.html&si=eyr5JfZ2fKvzhIr9
Okay okay beautiful but how are you automating pulling metric data regularly on videos into the database
Hi Thomas, great use case. The optimal regular expression would be this: replaceAll("(\d{3})(?=\d{3})", "$1,")
It would be, but the last time I checked, lookarounds weren't supported in Safari/Webkit, and the Notion mobile apps use Webkit under the hood. So you'd get a blank formula when using them.
However, there was finally a PR for it late last year: bugs.webkit.org/show_bug.cgi?id=174931
So maybe they work now! I just haven't had time to go test across all devices.
@@ThomasFrankExplains Nice response, like a true programer, welcome among us.
Hi Thomas, I have a question about something related to formulas and I can't find the solution avywhere, so you're my last chance. I have a Database with a list of clothes, each one with many different properties. I also created a formula that allows me to generate automatically a description of the piece of clothing. Now it comes the problem. When I'm on my computer I don't have any problem with copying and pasting the text generated from the formula because there's the dedicated button; but the thing is different on the mobile app. There is no button to copy the text from the formula and when i click on the description cell it opens the formula. The only way to solve it is to restrict permissions to members so they can't edit the formula, but it's not ideal. Is there any way to extract the text from the formula and put it on a text property maybe so that I don't have problem copying it on the mobile app? Thanks in advance if you answer.
26:01 there is a bug on the ID part. when you click the link of that part where you link(video.ID) you can't go back to the page when you click back.
it just keep coming back to the same page. also .. if may I asked.. can we control how that page should open? I only want it to open half way. not open the whole page leaving the main page.
Hmm, if you hit ctrl/cmd+[, it doesn't take you back? I've done it a ton of times and it's worked for me.
And unfortunately Side Peek doesn't work with formula links yet, but that change has been requested!
@@ThomasFrankExplains there is no ctrl/cmd click on a tablet. for now I'm going to delete this property. until they added something like. link(id).peak, or .center. I'll return the property back.
I'm using this to my product list for "Related products" viewing names and prices.
and are mainly use by my workers on a tablet.
Great video! Thanks a lot for the thorough tutorial!
P.S. For the page id on the 📄 link, you can also use the id() function with id(video) without the need to create a property in the Videos DB.
Great call! I hadn't noticed that id() got updated to allow an argument.
The only thing that I have a hard time following with formulas is the $, /n, /d [^ ], *, etc. types of characters. Notion really doesn't show what they do or how to use them. I have not been able to find this in Notion's documentation. If anyone can point me to better documentation for this, that would be amazing. Or, if Thomas can do a video on them, that would be great!
Notion never documented their regular expression capabilities, but fortunately I did 🙂 thomasjfrank.com/formulas/regular-expressions-in-notion-formulas/
@@ThomasFrankExplains Amazing! Thank you so much! I will take a look.
Look who is back....
Bro, i really need your help, currently im working on a notion integration, im building a company around it (and other integrations for other platforms), right now im stuck, i have no way of testing the notion OAuth flow without my integration going public, but i cant do that yet, i dont have things like landing pages, terms of use and privacy policies ready, what could i do?
My team and I are working through this right now. You need to create your landing, terms, and privacy pages. They can even be simple Notion pages to start!
@@ThomasFrankExplains thanks, i guess there is no way around, gonna do what must be done
Thomas, is it possible to change the dot for a comma in currencys?
In the US, it's like this: 1,050.50
In Brazil, we use it like this: 1.050,50
I need it to be a comma in the decimal places. Any chance that’s doable?
It's possible if you turn a number into a string with format(), and then write some complicated RegEx to place the punctuation where you want. But if you need to return a number, then it might not be possible until Notion offers that type of currency formatting as a Number format.
@@ThomasFrankExplains
Thanks for the fast answer lol!
Yes, I'm using a string
I even adapted the formula to replaceAll("\B(?=(\d{3})+\b)", ".") as someone said in the comments
The only problem is that the final number is 1.050.50, with dots in the thousands and decimal places
I can’t find a way to replace the decimal dot with a comma
If I use it like this, the number will be read as 1 million instead of one thousand
But can you do this in obsidian?
I think Obsidian has some fairly simple database-like plugins now, but I don't think it has a formula language yet.
I might be wrong, but returning the top 3 videos will only work under the premise that each value in the property 'views' is unique and will never be the same.
If you have two videos with the same view count, it will show twice the same first video (in order of the entries in the 'CZcams Videos' database) that matches the view count.
You're correct! With this use case, it's a very unlikely that you'll run into this problem. But it could happen.
I originally tried to build a formula that would get around this, but got stuck on some quirks with how the formula engine handles arrays that contain both a page object and one of that object's properties. I was trying to return an array with the view count first, then the page reference second.
I've just gone back and tried another approach - this time I'm returning an array of view counts and page IDs, which I didn't think of earlier. Now, we can run find() on the page ID, not the view count. That ensures we'll get the correct page even if there's a duplicate view count.
This does add a good bit of additional complexity to the formula, though:
prop("CZcams Videos").map(
[
"0000000000".substring(0, 10 - current.prop("Views").length()) + current.prop("Views"),
current.id()
]
).sort().reverse().slice(0,3).map(
lets(
currentID,
current.last(),
video,
prop("CZcams Videos").find(current.id() == currentID),
video.prop("Name")
.replace("(.{25}\w*).*","$1... ")
.link(video.prop("URL")).style("b") +
video.prop("Views")
.replace("(\d+)(\d{3})$","$1,$2")
.replace("(\d+)(\d{3},\d{3})$","$1,$2")
.style("c", "b", "green", "green_background") +
" 📃".link(video.id())
)
).join("
").style("b")
Fantastic. Thank you for taking the time @@ThomasFrankExplains
Thanks, this was extremely helpful!
Is your old channel dead , I have been waiting for videos for ages!!😢
Working on new videos now. It's just been very hard to get back to videos while scaling a company to 11 people, building products, covering all these new Notion features, and doing tons of programming 😅
Notion using in mobile please full class professional 😊
This is basically just a strip-down version of Javascript! I am not complaining though, I actually encourage it 👍
Same! I'm pushing for a straight up JS window, though I doubt I'll get it 🤓
Still, the API is good enough for most JS things!
Yeah, I need to start delving into the API and do some experiments!
Thank you for all the content and lessons 👍
12:53 burp🤣
Ha, looks like I missed a cut!
Also made me chuckle.
I feel like a bunch of steps where missed her, can you do a version for beginners, this made my brain hurt
That was too much for getting in one view, need to return here later
Hey Thomas, my name is Ofek and I really enjoy your content. I want to ask if you can give free alternative to chat GPT thank you appreciate it
The only free alternative I know of is the one built into Bing right now! There are open-source models too, but they require really powerful GPUs to run. So you typically have to host them on paid hosting services, and they end up costing more than ChatGPT.
Thank you I appreciate the answer@@ThomasFrankExplains
first
Looks a lot like Coda formulas 🤔
Yeah, they're pretty similar now! To the point where The Coda Guy and myself can challenge each other to switch apps and fare pretty well: twitter.com/TomFrankly/status/1700700006288851194
In truth, they're both just fairly similar to JavaScript with a bunch of limitations. And each has advantages over the other - Notion has a better variable function now, while Coda has cool extra functions like Sequence() - and of course the MAJOR advantage that you can reference entire columns or tables instead of lone properties
@@ThomasFrankExplainswell, I guess I should pack my bags and move back to Notion... 😂
Senpai notice me 🙄
The Replace function is giving me a nose bleed.
You spelt Gastly wrong - unsubbed.
Not everyone is a creator, build something more useful :(
The same could be said for any example at all. Either it's too general and broad to be interesting to anyone, or it's useful to a certain use case.
But this is why I included an alternative use case in the template (linked in the description) and showed it in the first two minutes of the video!
As long as you understand what he's doing and why, it isn't too hard to adapt these functions to whatever your use case may be.
I learned about the link() function from this video, for example, which proved to be useful for one of my own pages.
I have a formula:
prop("Tracker").map(current.prop("Hour"))
Result: "2, 1"
How to add 2 with 1 and get the result: "3"?
if you add .sum() at the end, it should sum all the numbers in that resulting list!
@@ThomasFrankExplains error: ⚠Cannot call sum with target of type array. [0,20].
The "Hour" column is the "Name" text column