Help

Re: LInk to another record

Solved
Jump to Solution
1899 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Cristina_Moreno
4 - Data Explorer
4 - Data Explorer

Hello, can I join three fields un one?

I want to mix three fields in one automatically. Is that possible? I dont know wich rule I have to follow.

In A I have different categories (A) which belong to another column (B); and I want to mix in a third field the categories which are the same. Is that possible?

A: blue B=house;
C= Carla=blue+ 13yearsold

😧 I want to mix in other names in C, the words that are the same.

thank you
Cristina

1 Solution

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):

Screen Shot 2020-05-23 at 5.06.37 PM

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.

Screen Shot 2020-05-23 at 5.50.49 PM

Next will be a single-select field where you can track the selection state for the film at the linked festival:

Screen Shot 2020-05-23 at 6.04.21 PM

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.

Screen Shot 2020-05-23 at 5.56.51 PM

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:

Screen Shot 2020-05-23 at 5.59.06 PM

Once that is built, go to your [Films] table and add a rollup field named {All Festival Key Info} with this setup:

Screen Shot 2020-05-23 at 6.07.02 PM

That creates this output (again showing the expanded view of the field for clarity):

Screen Shot 2020-05-23 at 6.08.37 PM

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:

Screen Shot 2020-05-23 at 6.13.14 PM

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:

Screen Shot 2020-05-23 at 5.11.41 PM

Whew! I think that covers everything, but if I missed anything, please let me know.

See Solution in Thread

17 Replies 17

Welcome to the community, @Cristina_Moreno! :grinning_face_with_big_eyes: I’m having some difficulty understanding the contents of your fields. Could you give another example, this time with each field’s contents on a separate line, and also include the specific output you’d like to see in the fourth field based on that example? Also, please indicate each field’s type (single line text / single select / multiple-select, etc.)

Cristina_Moreno
4 - Data Explorer
4 - Data Explorer

Hello Justin,

thank you so much for answer to my question. Before paying or introduce to my company I want to make sure I can do the things I want.

I have in the first table the festivals with different information (deadle, name, categories, web, start and end), and in the second table I have films (20)

I want to link both fields. I want to oselect the festival and choosing the films from that festival.

After that, I want to make individual report from each film, and send it to the director. But I would like that the director see what I see in the first table of festivals with all information.

thank you

Cristina

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:

Screen Shot 2020-05-22 at 3.11.45 PM

And I’ve got films in a {Films} table:

Screen Shot 2020-05-22 at 3.15.05 PM

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:

Screen Shot 2020-05-22 at 3.17.03 PM

Using lookup fields, I can pull in details about the linked festival for each record:

Screen Shot 2020-05-22 at 3.18.08 PM

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.

Screen Shot 2020-05-22 at 3.20.02 PM

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.

Screen Shot 2020-05-23 at 2.26.01 PM

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.

Screen Shot 2020-05-23 at 2.28.36 PM

In the controls on the left, you’ll see this:

Screen Shot 2020-05-23 at 2.29.20 PM

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…

Screen Shot 2020-05-23 at 2.30.58 PM

…the table in the page now looks like this:

Screen Shot 2020-05-23 at 2.31.34 PM

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?

Thank you again for your explanation.
I tried tomorrow to do with page designer block which is free i think.

Is it possible to share the view with the directors in other format? Not in pdf??

I have two other questions which I dont know how to integrate yet.

In some festivals there is a fee. Which i see in the table “Festival” but i want that the director see in its report that fee and a summatory of other fees from all festivals. Is thas possible to do that?? How?

I want to add another field but i dont know where should i add it.

I need in each film to update the status in each festival. Selected/not selected.

Do you have any idea how to do that?

Thank you in advance
Cristina

Cristina Moreno

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):

Screen Shot 2020-05-23 at 5.06.37 PM

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.

Screen Shot 2020-05-23 at 5.50.49 PM

Next will be a single-select field where you can track the selection state for the film at the linked festival:

Screen Shot 2020-05-23 at 6.04.21 PM

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.

Screen Shot 2020-05-23 at 5.56.51 PM

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:

Screen Shot 2020-05-23 at 5.59.06 PM

Once that is built, go to your [Films] table and add a rollup field named {All Festival Key Info} with this setup:

Screen Shot 2020-05-23 at 6.07.02 PM

That creates this output (again showing the expanded view of the field for clarity):

Screen Shot 2020-05-23 at 6.08.37 PM

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:

Screen Shot 2020-05-23 at 6.13.14 PM

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:

Screen Shot 2020-05-23 at 5.11.41 PM

Whew! I think that covers everything, but if I missed anything, please let me know.

Hello Justin

thank you so much for your email. That would be great, but I am quite overwhelm and desesperate

. I am not quite applied with the technology of Airtable, and I am suffering a lot because I tried thing and I dont succedd following our instructions.

I tried to following your steps again this weekend, or try to ask any friend. When I tried, can I continue asking you for doubts?

best
Cristina

That looks like you’ve got the right start. You just need to change the field names to those from your own table. For example, the website field in mine was {Website}, but in yours it’s {WEB}, so change “Website” to WEB for that part of the formula. Same for the other fields; rename them to match your own table, as well as add whatever fields you want in that emailed summary.

You won’t be able to do anything on the Formatting tab of that formula setup dialog. That only applies if your formula creates a date or a number.

I’m not sure what you’re referring to by “the final.” Are you speaking of your final email output?

The record that you’ll choose to send as an email will be from the table containing your films. That’s where all of the relevant info gets collected through the steps I outlined above.

If you have further questions, feel free to ask.

Hello
but in your screenshot, in the field “key info” you entered all that information. Dont you click “formatting” you add it everything at once and you saved?
Name & “\n” & Website & “\n” & "Dates: " & DATETIME_FORMAT({Festival Start}, “L”) & " - " & DATETIME_FORMAT({Festival End}, “L”) & "\nAverage Attendance: " & Attendance

best
Cristina

Sorry for the confusion. That screenshot I posted was the expanded view of that formula field. That shows what the formula created for a single record after I’d finished writing the formula. Like I said, the Formatting tab in the field properties won’t work when we’re outputting text. It only formats numbers and dates.

It looks like my text description of the setup is causing more confusion for you. I’ll record a demo video of the process and post that later today, which should (I hope) make things more clear.

Hello Justin

that would be lovely. Could you record me a video of the whole process? I promised I tried to follow your instructions, but some of them I get stucked.

Do not worry, if you need more time you can send me at the end of the week.

THANK YOU THANK YOU THANK YOU

Cristina

I just sent you a private message with a link to a video where I walked through the entire setup. If that helps you, please consider marking my comment above (the big one where I typed the instructions for the same setup) as the solution to your question. This helps others who may be searching with similar questions. Thanks!

Hello Justin

sorry to delete some post. I dont mind to share everhytihg, but I should use another example of the films , like Film 1 Film 2 Film 2. I through at first that if I answered it will be directed to you, instead of the whole community, sorry for misunderstood that.

I will type again in my reply your solution, but maybe not showing the titles if you dont mind?

I will watch it carefully the video this weekend. In case I have any comment can I still asking you?

have a great day
Cristina

Cristina_Moreno
4 - Data Explorer
4 - Data Explorer

Hello Justin, I hope you are okay. Please confirm if you understand what I want to achive. your video was amazing. And I am pretty close to achieve what I want.

  1. In the first table, Festivales, how can I see all the films that I submitted? I use this table to guide in my submission process.

  2. I dont know how to many the submission of several films in one festival. If I am right your video shows: Collecting info for all festivals for a given film. And my goal is to submit several films to each festival. Not al films goes to the same festival. This process I do it manually.

  3. In the case I need to incorporate some internets and update the list of festival. How can I share only that view?

best
Cristina

The link field on the [Festivales] table contains all links to all films submitted to that festival. However, looking at them in the link field itself may be tough to read. One way to make a cleaner list is to add a rollup field. It should use that link field automatically (unless you made another one), so all you need to do is choose the target field to roll up, which would be the {Film} field, and then an aggregation formula that takes all of those film names and lists them on individual lines:

ARRAYJOIN(values, "\n")

As I pointed out in our side discussion, each record on the [Films at Festivals] table will connect one film to one festival. To connect that same film to another festival, add a new record for that pair.

Here’s an overview of view sharing:


If your question is about collaboration in a shared view, I recommend starting a new thread, as this one has primarily been about linking records between bases.

On that note, if you feel that my base breakdown above addressed your core question, please mark that comment as the solution to your question. That will help others who have similar questions.

Zeena_Hassan
4 - Data Explorer
4 - Data Explorer

I am trying a similar exercise and hitting a roadblock. I created a full data tab and when i make updates to it - i would like the other tabs in my base to update. How do i go about doing this?

Welcome to the community, @Zeena_Hassan! :grinning_face_with_big_eyes: Sorry to hear that you’re having issues, but would you mind starting a new thread for your specific question, including more details about the setup of your base? It’s easier to track than having your issue as part of someone else’s thread.

@Cristina_Moreno It looks like you marked the wrong comment as the solution. I was referring to my earlier comment where I gave the detailed breakdown of the system. Please switch the “solution” marker to that one when you have a moment. Thanks!