Jump to content

Show the latest version

Recommended Posts

Hi all,

I have created a portal in a related table and I want to apply a filter to it so that it only shows the latest versions from a found set of records and to hide the others. There is a related field within the portal called 'scan version' which displays the version number as eg 'v001' 'v002' 'v003' etc

I've been hunting around online to find a calculation that fits my needs and have played around with the 'Max' function but to no avail

If anyone can give me some pointers that would be awesome, I'm still very new to this and learning so any help is always appreciated!

If you need any more info please let me know



I have just worked this out and used the following calculation

Scans::Scan Versions = Max ( Scans::Scan Versions )

and it worked!

Link to post
Share on other sites
2 hours ago, Mike_FM said:

apply a filter to it so that it only shows the latest versions from a found set of records

I suspect you made a mistake here: a portal that shows a found set of records cannot be filtered.


Link to post
Share on other sites

Ah yes, apologies calling it a found set is a mistake and also upon further testing the calculation: Scans::Scan Versions = Max ( Scans::Scan Versions ) doesn't quite work for what I need.

Diving in a little deeper...

I have two tables in my database; 1 called Counts and 1 called Scans and they are related using a match field called VFX_ShotCode (Counts table is the parent and Scans is the child)

Within the scans table I have created a field called 'scan filename' and it contains the names of all the scans in my project; they all follow this naming convention-


episode#_scene#_shot#_scantype&#_version#) - an example would be 101_010_0010_bg01_v001

I also have a field within the Scans table called Scan Versions which I contains a calculation that cyphens off the 'v001' of the scan filename.

I have created a portal in the Counts table to show this related scan data and as it currently stands, when I am in the Counts Table and on the record with VFX_ShotCode '101_010_0010', the following related scan data is displayed 






However, I only want to show the latest version of each scan filename, which would look like




Using the Max function shows me all the v002s but not the v001 of the 'el01' scan

Any advice would be really appreciated, I am still trying to understand how to create calculations that do what I need and following your previous calculations has really helped

Thanks so much,



Link to post
Share on other sites

I think this could be easier if you had separate tables for each level of hierarchy:

Episodes -< Scenes -< Shots -< Scantypes -< Scans

As it is, each record in Scans needs to determine if it is the latest one in its own subgroup, when there is no pre-existing definition of the subgroup. One way to create such definition is to define an auxiliary self-join relationship linking all scans of a subgroup together - see the attached demo.

It should be also possible to replace the relationship with a calculation field in the parent table, but then the filtering formula would be quite complex - not something I would suggest to a beginner (or even an intermediate level) developer.



Edited by comment
Link to post
Share on other sites

This is awesome! 

So I used the Left and Right function to split out the scan filename string into new fields; cVersion and cSubGroup, 

I then duplicated the scan table and and  created a relationship between the new cSubGroup fields - I didn't realise you could duplicate table and do a self join

To further my understanding of the calculation you used in the portal filter:

In layman's terms I understand that 'Scans::cVersion = Max' means show the highest number in this field (?)

But what does ( ScansInSubgroup::cVersion ) part of the calculation mean?

Thanks again for your time and help!



Link to post
Share on other sites
2 hours ago, Mike_FM said:

I then duplicated the scan table

I hope not! You only need to add another occurrence of the same Scans table to the relationships graph.

The filtering expression needs to be read while taking into account the context of each field reference. In plain language, it checks if the version of the examined record is equal to the maximum version of the entire subgroup.  The reference Scans::cVersion uses the same occurrence as the portal, therefore it evaluates to the version of the portal record being examined; the expression Max ( ScansInSubgroup::cVersion ) uses the newly added occurrence, therefore it returns the maximum value of all the records in the same subgroup. If these values are equal, the comparison returns true and the record passes the filtering test.


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Similar Content

    • By Mike_FM
      Hi all,
      I would like to create a page count for a found set of records when I am in preview mode and when I export to PDF  ie if a found set of records has 3 pages then the first page would contain a field that reads '1 of 3'
      Can anyone help with how to achieve this?
      Any help is much appreciated
    • By Don_Macaroni
      I cannot select inside an input field at a specific character. Clicking on the field works and selects the field, but I then need to use the arrow keys to go to a specific characters. Mouse clicking at a specific characters does nothing. Anyone has experienced this?
    • By Amanco
      I moved fields (and scripts) from one FM file to another one.
      Now, I have a table to 2 fields that has same function but different names, example: Container1 and Container2
      Both have their own attached scripts and calculations.
      I would like to merge "container2" into "Container1" field so the attached scripts and calculations will also change, but I can't rename it simply to "container1"
      is there a way to without going through all the scripts and calculations?
    • By Selva
      I have tried to pass the below JSON data to the API. But it doesn't accept the JSON data due to Square brackets[] in the JSON Key. Is there any solution available to pass the below value?
      JSONSetElement ( "{}" ; "[Name | Last]" ; "Test" ; JSONString )
      We need the output like below.
      "[Name | Last]": "Test"
      Thanks in advance.
    • By Mike_FM
      Hi all,
      I'm a film editor and have started building a FM database to be able to track visual effects shots that go in and out of an editing timeline. 
      I am trying to create a calculation that will enable me to add frames to timecode and get the result back in timecode.
      At the moment I have a text field called 'Scan TC In' which has the following TC entry 18:59:48:23 (HH:MM:SS:FF) and I need to add 8 frames to it, the timeline runs at 24 frames a second. 
      Does anyone know how I can achieve this?
      I'm a newbie to FM so any help is greatly appreciated!
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.