Skip to main content

I want make an array of linked records, sort them alphabetically, then return one of the fields

  • January 26, 2024
  • 3 replies
  • 188 views
A
Forum|alt.badge.img

Here is my base I am developing to track VFX shots for a film.

Table "SHOTS" is the shot in the film. "Submissions" is the version I get from a vendor

https://airtable.com/appfa9Uq0xYTTe5oK/shrH1DETOqdRkJUjT/tbl299qf49ykQMspW/viwjFl7u86veCddkU?blocks=hide

 

Ideally I would like to be able to load all the versions in to an array then return the latest comment when I am reviewing the latest version. The array should be sorted alpha numerically. 

I have been trying with little success to use 

ARRAYSLICE(ARRAYCOMPACT(values)), -1)

 Can you do this with the formula, rollup field or do you need to script this?

3 replies

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • January 26, 2024

The only way to do natively within Airtable would be to script it. You could also do that externally with Make's array functions, which includes a sort function, but that would probably be overkill to use an external app for that.

Alexey_Gusev
Forum|alt.badge.img+25

Hi,
I thought about script function of sorting links and suddenly realized you can easily sort it by hand.
Suppose Shot code in Shots is unique primary field.
In 'Submissions' table add temporary single text field and copy 'Shot code' field there. Now select 'Shot code' field and clear it pressing del. Sort table by 'Submission Name' and copy all temporary field values into linked field 'Shot code'. Links will be added according to their order during copy-paste, and therefore will be alphabetically ordered. Afterwards, remove temporary table. 

Zach_Young
Forum|alt.badge.img+12
  • Zach_Young
  • Participating Frequently
  • February 28, 2024

If you want I think you want, then yes: any number of Submissions linked to a Shot can "know" which Submission is the latest, and flag itself as "the latest Submission", then the Shot record can do a lookup on its submissions based on that flag to display the comment from "the latest Submission".

My modifications to your VFX Testing Base 

The key is to have some kind of numerical value in the Submissions table that indicates which is the latest.  The date field you already had would work; I went ahead and added a numerical revision number, Rev_num, just to be clear:

Name is also based on Rev_num:

 

 

'v' & RIGHT('000'&Rev_num, 3)

 

Back in the Shots table, do a Rollup of that numerical-value field and get the max value:

 

MAX(values)

 

Now each Shot "knows" the max rev number, which means it (in effect) can know the latest Submission...

Back in Submissions, do a lookup on Max_rev then create a formula field, Latest_rev, which asks "is my rev the latest rev?":

 

IF(Rev_num=Max_rev, 'Y')

 

Finally (😖 yay!!) back in Shots, add a lookup of Comment and filter the lookup by Latest_rev is Y:

Does that do what you wanted?

Something about what you stated in the original question, or the way the shared base was laid out, makes me think you actually want to look at the previous comment while reviewing the latest revision: to answer the question, does this revision satisfy the needs expressed in the comment on the previous revision. If so, you can just link a revision to its previous revision, then do a lookup for the comment on the previous revision, then look that up from Shots. That's also in the shared base, now.

Terms & ConditionsAccessibility statement