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 bgscott
      I have an application where some of the related files will reside on removable media.  When sorting, a message box appears telling the user that the related file (which is not on the server) can not be opened (Not Found) and asks the user to locate it.  The related file may or may not be available on the removable media and is not needed for the purposes of the sort.
      Is there anyway to prevent this message from appearing?  Error capture on does not solve the problem
    • By St3v1e
      New user
      I have a "Search" script setup in one of my layouts, which works well but I'd like to refine it a little to make searching quicker and with less mouse clicks.
      Currently, I have a field into which I enter a client name, and a "Search" button which activates a search throughout my database for any records containing this name. For the search to work I have to input the name (or a portion of it) into the "Search Name" field , and then click on the "Search" button.
      What I'd like to happen is to enter the name into the field and then hit the enter key to activate the same search. Is this possible?

      Thanks in advance.
    • By ChrisOC
      Has anyone integrated Smartsheet with FileMaker using Claris Connect? I can get the basics set up and am able to create a Flow that will add a new record in FM when a row is added in Smartsheet.  However, I can't seem to figure out how to pass any column data from that row into the new FM record.  It does not give me the tags for any of the columns from my sheet, only high level parent and body tags.  What am I missing?  
    • By paulkivett
      My first try using a Server Side Script. I'm importing csv data which is on "This PC / Documents/CompPBM_checks.csv". I have tried every possible path, but still get missing file error. Options tried include
      file:ThisPC/FileMaker Server/data/documents/CompPBM_checks.csv
      $Import equals: Get ( DocumentsPath )&"CompPBM_checks.cvs"
      FM Server is hosted on AWS
    • By pixi
      Has anyone played with latest FMS Linux and LetsEncrypt-Certificates? 
      (How) does it work?
  • Create New...

Important Information

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