Help

Re: Table Showing Last Record Only

Solved
Jump to Solution
1385 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Eric_Roberts1
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi,

Here’s what I’d like to do.

On my website, I’d like one form (Table 1) to have two fields.

  1. Twitter Handle
  2. Writing Prompt

On my website, a user fills in both fields then the data gets added to Table 1 (in airtable).

When Table 1 fields are filled, another table (Table 2) on the same web page will display the Twitter Handle and Writing Prompt.

However, Table 2 will only show the LAST record (Twitter Handle and Writing Prompt) input from Table 1.

I found this page that I thought would be helfpul: View last record only but I’m unable to make it work.

P.S. I want to prevent users from adding duplicate data.

I’ve added a picture to make things more clear, this is the kind of page I’d like to build. @Jacks data was input earliest. It vanishes once the next user (@elon) adds data. This would be TABLE 2.

@You and “Go ahead…” would be the form where a new user would input their data. Once the user ads the data, it gets shown in Table 2.
Capture

1 Solution

Accepted Solutions

Over in your [Last Record View] table, you should have a single record. I like to use a single character like a period (.) as the name for this record.

Screen Shot 2020-07-20 at 10.52.09 AM

Back in the [Main] table, every record needs to link to this single record in [Last Record View]. If you’ve already got a lot of records in place, the easiest way is to start is with a formula field that has a single period (or whatever you chose as the name for your lone record in the other table) as a string.

Screen Shot 2020-07-20 at 10.55.42 AM

That will auto-fill every record with that same thing.

Screen Shot 2020-07-20 at 10.56.57 AM

Now convert that formula field into a link field, pointing to your [Last Record View] table. The “.” in every record will be converted into a link to that lone record.

Screen Shot 2020-07-20 at 10.58.11 AM

Screen Shot 2020-07-20 at 10.58.29 AM

Over in the [Last Record View] table, you’ll have incoming links from all of those records in [Main], which are named by their {Autonumber} values.

Screen Shot 2020-07-20 at 10.59.15 AM

Add a rollup field, using those links to pull in the {Autonumber} field values, and using MAX(values) as the aggregation formula. That will give you the highest value from that {Autonumber} field.

Screen Shot 2020-07-20 at 11.00.42 AM

Screen Shot 2020-07-20 at 11.01.23 AM

Go back to [Main], and add a lookup field to pull this max number in.

Screen Shot 2020-07-20 at 11.02.29 AM

Now every record in [Main] has that maximum value.

Screen Shot 2020-07-20 at 11.03.23 AM

Add a formula field that compares the max value with the {Autonumber}, returning a 1 where it finds a match. While the example in that other thread wrapped the comparison inside an IF() function, you can just put the comparison by itself.

Screen Shot 2020-07-20 at 11.06.45 AM

Screen Shot 2020-07-20 at 11.07.10 AM

That indicates which record matches the highest number. Now go back to [Last Record View] and add a lookup, pulling in whatever data you want from that matched record using a condition. The example below pulls the {Twitter Handle} value; duplicate the field and tweak to pull the {Written Prompt} value.

Screen Shot 2020-07-20 at 11.11.04 AM

Screen Shot 2020-07-20 at 11.12.11 AM

To ensure that new records auto-link from the {Common Link} field to that lone record, group by that linked field. However, this only works for records added in the view itself. Records made via form won’t be linked (I just tested it). To fill in the missing links, I suggest writing a quick script, or possibly using the scripting action beta to assist.

See Solution in Thread

5 Replies 5

Welcome to the community, @Eric_Roberts1! :grinning_face_with_big_eyes:

Could you provide more detail on what’s not working with it? There’s probably a way to fix it, but not without knowing how you set it up.

Hi Justin,

I tried following these instructions.

Make sure that in the 1st table you have an Autonumber field.
Obviously the last record will have the highest number.
Link every record in the 1st table to the 2nd table, that only has 1 record.

The bold part’s wording is confusing. Anyways, here’s my 1st tables’ setup. 3 columns. In this case, let’s pretend 3 people have visited my website and input data into the two fields (Twitter Handle, Written Prompt). I’d like the latest one to show (@michelle, I made breakfast) on my website in table 2.

1

Can you please guide me through step-by-step on how to create what I’m hoping for.

Over in your [Last Record View] table, you should have a single record. I like to use a single character like a period (.) as the name for this record.

Screen Shot 2020-07-20 at 10.52.09 AM

Back in the [Main] table, every record needs to link to this single record in [Last Record View]. If you’ve already got a lot of records in place, the easiest way is to start is with a formula field that has a single period (or whatever you chose as the name for your lone record in the other table) as a string.

Screen Shot 2020-07-20 at 10.55.42 AM

That will auto-fill every record with that same thing.

Screen Shot 2020-07-20 at 10.56.57 AM

Now convert that formula field into a link field, pointing to your [Last Record View] table. The “.” in every record will be converted into a link to that lone record.

Screen Shot 2020-07-20 at 10.58.11 AM

Screen Shot 2020-07-20 at 10.58.29 AM

Over in the [Last Record View] table, you’ll have incoming links from all of those records in [Main], which are named by their {Autonumber} values.

Screen Shot 2020-07-20 at 10.59.15 AM

Add a rollup field, using those links to pull in the {Autonumber} field values, and using MAX(values) as the aggregation formula. That will give you the highest value from that {Autonumber} field.

Screen Shot 2020-07-20 at 11.00.42 AM

Screen Shot 2020-07-20 at 11.01.23 AM

Go back to [Main], and add a lookup field to pull this max number in.

Screen Shot 2020-07-20 at 11.02.29 AM

Now every record in [Main] has that maximum value.

Screen Shot 2020-07-20 at 11.03.23 AM

Add a formula field that compares the max value with the {Autonumber}, returning a 1 where it finds a match. While the example in that other thread wrapped the comparison inside an IF() function, you can just put the comparison by itself.

Screen Shot 2020-07-20 at 11.06.45 AM

Screen Shot 2020-07-20 at 11.07.10 AM

That indicates which record matches the highest number. Now go back to [Last Record View] and add a lookup, pulling in whatever data you want from that matched record using a condition. The example below pulls the {Twitter Handle} value; duplicate the field and tweak to pull the {Written Prompt} value.

Screen Shot 2020-07-20 at 11.11.04 AM

Screen Shot 2020-07-20 at 11.12.11 AM

To ensure that new records auto-link from the {Common Link} field to that lone record, group by that linked field. However, this only works for records added in the view itself. Records made via form won’t be linked (I just tested it). To fill in the missing links, I suggest writing a quick script, or possibly using the scripting action beta to assist.

Justin, this is amazing - I have yet to go through it in full but I will later tonight.

The funny thing is, I was able to make progress on my own. The issue, believe it or not, was the rollup feature. I was unable to save and move on because I didn’t even realize I had to scroll down in to choose a table field. Maybe this is something Airtable could consider (I saw that another user had a similar issue in another thread!).

Capture

As for auto-linking, I read that Zapier can solve this problem… It’ll take some further investigating. I’ll also check out your script suggestion.

Thanks.

That’s only an issue on smaller screens. I’ve bumped into that on occasion when working on my laptop, but never on my desktop with a larger screen. I’m not sure what Airtable could do to make it more clear. There may be some CSS tweaks that could be applied to make it more obvious that there’s something being truncated, but I’m no CSS guru, so that’s just a guess.