Accepted Solutions
From my understanding, the Scripting block is the only block that’s free to all users. All other blocks require that the base be in a workspace with a Pro subscription or higher.
That said, forget about Page Designer, because I finally got my head on straight and thought of a way to make this work with your initial approach of using the “Send record” feature. :slightly_smiling_face: More on that later.
What I have below are solutions to all three problems:
- Collecting info for all festivals for a given film
- Totaling the fees for those festivals
- Tracking the selection status of each film-festival combination
They’re all gonna be mixed together a bit, but I’ll try to point out each one as I go along.
You will need to first create a formula on the [Festivals] table that collects all the info you want to send to directors, and splits it on multiple lines by inserting the newline escape code ("\n") between pieces. In this example, I’ll stick with some basic info, but obviously this could be expanded to include a lot more. Here’s the formula, which I put into a field called {Key Info}:
Name & "\n"
& Website & "\n"
& "Dates: " & DATETIME_FORMAT({Festival Start}, "L")
& " - " & DATETIME_FORMAT({Festival End}, "L")
& "\nAverage Attendance: " & Attendance
And here’s how it looks (using the expanded field view to show the line breaks):
We’ll set that aside for now and bring back the junction table I made earlier, though in a slightly different arrangement than I made it the first time.
As I said before, a junction table is just a regular table that’s used to tie other tables together for a specific purpose. In this case, we’ll use it to connect a film to a festival, track that film’s selection state for the festival, and also to pull in the festival’s key info and fee information for later aggregation.
First is the primary field, which I set as a formula field. I’ll reveal the formula later, because it depends on some of the other field content that I want to explain first.
The next two fields are link fields where I can select a film and festival. Pretty standard stuff here.
Next will be a single-select field where you can track the selection state for the film at the linked festival:
After the selection state field will be two lookup fields. Using the festival links, we’ll pull in the key info and fee details for the festival.
With that in place, here’s my formula for the primary field:
IF({Selected?}, LEFT({Selected?}, 1)) & " " & Film & " @ " & Festival
That makes my junction table look like this:
Once that is built, go to your [Films] table and add a rollup field named {All Festival Key Info} with this setup:
That creates this output (again showing the expanded view of the field for clarity):
Also, notice that you have a quick at-a-glance view of the selection status of each film-festival combo thanks to the colored emojis.
Finally, add another rollup field to pull in the {Festival Fee} info from [Films at Festivals], using the SUM(values) aggregation formula to give you a summary of all fees. That makes the final [Films] table look like this:
Now you can hide the link field (and any others that you don’t want to be part of your email), and that collected summary of all festivals will drop neatly into your email:
Whew! I think that covers everything, but if I missed anything, please let me know.
Thanks for the clarification. If I read you correctly, it’s possible that some films will be at more than one festival, and you want to ensure that you can not only connect a film with all the festivals it will be show in, but also see additional information about a specific festival.
In that case, I suggest making what’s called a junction table. Instead of connecting festivals and films directly from their respective tables, you’ll do this from the junction table. Here’s an example setup. I’ve got some festivals in a [Festivals] table:
And I’ve got films in a {Films} table:
Next I make my junction table, which I’ll name {Films at Festivals}. Each record will link to a single film that appears at a given festival, and the {Name} field will combine the two using a formula for easy reference elsewhere:
Using lookup fields, I can pull in details about the linked festival for each record:
By applying a filter to the view, I can only show records for a single film, which can be shared with that film’s director so that he/she can see details about all the festivals where it was shown.
Is that what you’re trying to achieve?
A junction table isn’t a different type of table. It’s just a table that’s intended to be used to connect data from other tables. In this case, it’s a way to connect festivals and films in a very specific way for a specific purpose.
Thanks for sharing the video. Knowing that you were trying to use the “Send record” feature makes your end goal much more clear.
In theory you could use a series of lookup or rollup fields in the [Films] table to pull specific data about the linked festivals, but it would be very difficult to format, largely because the “Send record” feature gives you very little control over the formatting of the record you’re sending.
I strongly suggest using the Page Designer block instead. It gives you far more control over the layout of your data, and has the ability to do exactly what you want: pull in data related to linked records, even without having lookup or rollup fields in place. Here’s an example. Instead of connecting films to festivals using a junction table, I linked films directly to festivals as you did in your video demo.
In the Page Designer block, I selected my [Films] table in the main settings, then added that {Festivals} link field to the layout (very roughly, just to demonstrate). Because I want to add more info about the listed festivals, I expanded the item to fill the page width.
In the controls on the left, you’ll see this:
Right now there’s only a column for the {Name} field, but we can add more. If I add other columns from the [Festivals] table…
…the table in the page now looks like this:
Use the Previous Record and Next Record links a the top of Page Designer to switch to a different record.
You can export each record’s page as a PDF, which can then be emailed to your contacts.
Is that closer to what you want to do?
From my understanding, the Scripting block is the only block that’s free to all users. All other blocks require that the base be in a workspace with a Pro subscription or higher.
That said, forget about Page Designer, because I finally got my head on straight and thought of a way to make this work with your initial approach of using the “Send record” feature. :slightly_smiling_face: More on that later.
What I have below are solutions to all three problems:
- Collecting info for all festivals for a given film
- Totaling the fees for those festivals
- Tracking the selection status of each film-festival combination
They’re all gonna be mixed together a bit, but I’ll try to point out each one as I go along.
You will need to first create a formula on the [Festivals] table that collects all the info you want to send to directors, and splits it on multiple lines by inserting the newline escape code ("\n") between pieces. In this example, I’ll stick with some basic info, but obviously this could be expanded to include a lot more. Here’s the formula, which I put into a field called {Key Info}:
Name & "\n"
& Website & "\n"
& "Dates: " & DATETIME_FORMAT({Festival Start}, "L")
& " - " & DATETIME_FORMAT({Festival End}, "L")
& "\nAverage Attendance: " & Attendance
And here’s how it looks (using the expanded field view to show the line breaks):
We’ll set that aside for now and bring back the junction table I made earlier, though in a slightly different arrangement than I made it the first time.
As I said before, a junction table is just a regular table that’s used to tie other tables together for a specific purpose. In this case, we’ll use it to connect a film to a festival, track that film’s selection state for the festival, and also to pull in the festival’s key info and fee information for later aggregation.
First is the primary field, which I set as a formula field. I’ll reveal the formula later, because it depends on some of the other field content that I want to explain first.
The next two fields are link fields where I can select a film and festival. Pretty standard stuff here.
Next will be a single-select field where you can track the selection state for the film at the linked festival:
After the selection state field will be two lookup fields. Using the festival links, we’ll pull in the key info and fee details for the festival.
With that in place, here’s my formula for the primary field:
IF({Selected?}, LEFT({Selected?}, 1)) & " " & Film & " @ " & Festival
That makes my junction table look like this:
Once that is built, go to your [Films] table and add a rollup field named {All Festival Key Info} with this setup:
That creates this output (again showing the expanded view of the field for clarity):
Also, notice that you have a quick at-a-glance view of the selection status of each film-festival combo thanks to the colored emojis.
Finally, add another rollup field to pull in the {Festival Fee} info from [Films at Festivals], using the SUM(values) aggregation formula to give you a summary of all fees. That makes the final [Films] table look like this:
Now you can hide the link field (and any others that you don’t want to be part of your email), and that collected summary of all festivals will drop neatly into your email:
Whew! I think that covers everything, but if I missed anything, please let me know.
