Set filter to filter latest revision for a document register

#1

Hi Coda Community,

I would like to set a view of table 1 that will filter to show only the latest revision number for each document. Appreciate if any of the community could help me to set the filter formula.

#2

You could create a new column to check if document revision is current or outdated, using this formula

thisTable.Filter(CurrentValue.[Document Code]=thisRow.[Document Code]).Sort(True(),Revision).Revision.Last()=thisRow.Revision

It returns true for last revision for each document. Then filtering is trivial.

Note that it sorts alphabetically so you don’t really need the aux tables. But if you really need them then try

thisTable.Filter(CurrentValue.[Document Code]=thisRow.[Document Code]).Sort(True(),[Rev Value]).Revision.Last()=thisRow.Revision

#3

Hi Dalmo ,

It works like a charm!! Thanks a lot.

Anyway as i’m still new on Coda, do you care to explain a bit how this code works? I dont want to just copy and paste the formula without understanding the steps/logic it takes.

#4

No worries, Nik. Glad it worked.

I assume you already read a bit about the formula syntax and chaining, you should also be familiar with thisRow and CurrentValue.

Then you break down the formula to

thisTable
 .Filter(CurrentValue.[Document Code]=thisRow.[Document Code]) //cuts down the table to work only with those rows related to the same document
 .Sort(True(),Revision) // this adds consistency to guarantee results will be sorted by revision, from oldest to newest, so if you're manually sorting the table it won't affect the results
 .Revision // up to now you were dealing with full rows, now we only get the Revision column
 .Last() // gets the last item of the column, which will be the latest revision (e.g. 00, P00 etc)
=thisRow.Revision // now that you found what's the latest revision is you check if it's on this row or not

A good exercise would be to create a column for each step and observe the results.

2 Likes