Help

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

Topic Labels: Formulas
1376 3
cancel
Showing results for 
Search instead for 
Did you mean: 
alexordanis
4 - Data Explorer
4 - Data Explorer

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=...

 

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 3

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.

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
6 - Interface Innovator
6 - Interface Innovator

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:

Add-Rev_num-to-Submissions.png

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)

 

Rollup-of-Max_rev.png

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

 

Max_rev-Latest_rev.png

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

Last_comment_config.png

Latest_comment.png

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.