Re: Table Showing Last Record Only
Hi,
Here’s what I’d like to do.
On my website, I’d like one form (Table 1) to have two fields.
- Twitter Handle
- 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.
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.
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.
That will auto-fill every record with that same thing.
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.
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.
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.
Go back to [Main], and add a lookup field to pull this max number in.
Now every record in [Main] has that maximum value.
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.
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.
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.
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.
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.
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.
That will auto-fill every record with that same thing.
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.
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.
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.
Go back to [Main], and add a lookup field to pull this max number in.
Now every record in [Main] has that maximum value.
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.
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.
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!).
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.
